Running "External Macros" in Excel
Running "External Macros" in Excel
(OP)
I may be stepping out of my league with what I am trying to do, but I am hoping I can get at least some direction as to if this is possible and where to go to better understand and utilize it. My terminology may be off, but I will do my best to make sense of what I am describing.
In college for my computer programing class I took Java. In Java there are sub-sets of code called methods (analogous to macros). The point of a method was you could recall the same block of text as many times as you want without having to re-program it. From my understanding this is no different than a macro, but I am just more familiar with the term method.
What I want to be able to do is have one master sheet of calculations that can run through other workbooks (like a macro or a method) and send back results. I want to then reference the same sheet for another repetitive calculation and repeat repeat repeat. What I don't want is to have to copy 100 of the same workbook so that my results sheet can display results for each scenario.
For example, say I was designing beam A-J. I want to have one sheet where I can input all my relevant information, such as span, bracing conditions, loads etc.. Then have that information run through a single workbook that I have written for general beam design, and return the answer to column A. Then I want to reference the same workbook that I used in beam A and get different results for beam B.
Another way of putting it is I want to use a workbook more like a set of instructions, even without opening it, than us it like an excel sheet that I will print from. I don't know if the answer is I just need to learn VBA if I want to do this, or if this can be somehow done with Macros, or if this just can't be done with Excel. Any advice is appreciated.
Thank you,
Josh
In college for my computer programing class I took Java. In Java there are sub-sets of code called methods (analogous to macros). The point of a method was you could recall the same block of text as many times as you want without having to re-program it. From my understanding this is no different than a macro, but I am just more familiar with the term method.
What I want to be able to do is have one master sheet of calculations that can run through other workbooks (like a macro or a method) and send back results. I want to then reference the same sheet for another repetitive calculation and repeat repeat repeat. What I don't want is to have to copy 100 of the same workbook so that my results sheet can display results for each scenario.
For example, say I was designing beam A-J. I want to have one sheet where I can input all my relevant information, such as span, bracing conditions, loads etc.. Then have that information run through a single workbook that I have written for general beam design, and return the answer to column A. Then I want to reference the same workbook that I used in beam A and get different results for beam B.
Another way of putting it is I want to use a workbook more like a set of instructions, even without opening it, than us it like an excel sheet that I will print from. I don't know if the answer is I just need to learn VBA if I want to do this, or if this can be somehow done with Macros, or if this just can't be done with Excel. Any advice is appreciated.
Thank you,
Josh





RE: Running "External Macros" in Excel
I've done something similar many times. Think of each your your external workbooks as a database that you could query with specific parameter(s) to return on piece of data. I wrote functions that did that. So in column A is a PartNumber, for instance. So I had a function named PartNom. It returned the Nomenclature, for the PartNumber supplied to the function, to the column in my table that was to contain Nomenclatures. I might have another column that I need stock location, so I designed another function that returned that data given a PartNumber.
Is this what you're looking for?
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Running "External Macros" in Excel
Josh
RE: Running "External Macros" in Excel
"...return a result..."
What kind of a result? A value, a table, a ???
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Running "External Macros" in Excel
For example:
My input would be for a series of beams, we will say beams A, B and C. My input in the sheet I'm working on would look something like this:
Beam A: Span = 10'-0", Dead Load = 10 PLF, Live Load = 50 PLF, unbraced, #2 DF 6x8
Beam B: Span = 4'-0", Dead Load = 120 PLF, Live Load = 300 PLF, fully braced, #2 DF 4x6
Beam C: Span = 6'-0", Dead Load = 50 PLF, Live Load = 80 PLF, unbraced, #2 DF 4x6
The external sheet would be set up to do a check on an individual beam, using the load, span, bracing condition, wood grade, and lumber size listed above. The return output would look something like this:
Beam A: Bending Stress Ratio = 0.83, Shear Stress Ratio = 0.51, Deflection Ratio = L/381, Design OK
Beam B: Bending Stress Ratio = 1.08, Shear Stress Ratio = 0.83, Deflection Ratio = L/241, Design NG
Beam C: Bending Stress Ratio = 0.95, Shear Stress Ratio = 0.63, Deflection Ratio = L/360, Design OK
It doesn't have to be separated by commas like that, all the information can be in individual cells. I just want the external sheet to work as a set of instructions for each beam to run though to do calculations in the background and find results, without having to have a separate external worksheet for each beam. I have all the programing skills to do each beam individually in it's own sheet, but I want to take things to the next level by being able to input more information in one sheet while having other sheets perform background processes. I can do this with tabs, but it could be so much cleaner if I can reference different external workbooks of per-programed information.
Thanks again for your help.
RE: Running "External Macros" in Excel
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Running "External Macros" in Excel
RE: Running "External Macros" in Excel
RE: Running "External Macros" in Excel
The main drawback of this AddIn approach is that you cannot use any WORKSHEET capabilities in your "external" workbook. All the data manipulation has to be performed inside the AddIn's user defined functions. If you have already spent a lot of time developing worksheet-based calculations and formulae you might, quite understandably, be loathe to abandon them. (However the LOGIC you have developed and tested in the worksheet will still apply even though you will have to re-code it in VBA.)
RE: Running "External Macros" in Excel
Its entirely possible to develop repeatable VBA code in order to allow for reuse, but this may confuse the context of also using methods on VBA objects (e.g. worksheet object)
The advantage of using an Add-in is that it separates the code from the data, such that the code can be updated for various reasons in an easier manner. Conversely, storing the code in the same worksheets as the data means that each spreadsheet that contains the code needs to be updated if an error or other issue is found. The same concept goes for access of external data (such as either in separate sheets of the same workbook, through external workbooks, or possibly through another means such as DB queries), separating the code from the data means that data updates can be done easier on changed conditions.
It sounds like there are a number of different goals for your calculation sheets, and there are a number of ways to achieve them, depending on time available and development capability. All of them are likely possible with VBA, actual implementation may depend on a few other factors. Hard coded external workbooks get painful pretty quickly unless steps are taken to manage the issues (hard coded filenames or paths that shift, managing bug fixes on code when you find bugs, working out how many different versions of the spreadsheet tools might be in existence). A big part of your scope is whether the external sheets will carry out their own calculations, or whether they're just data providers for a central calculation sheet. An even bigger part of your scope is whether you ever intend for anyone else to use your tools.
However, its all possible. I'd probably start with attempting to break down the different functions (calculation, data extraction, verification) then start making changes to the central calculation sheet first (e.g. static data on one worksheet, work out the layout of the sheet, then look to transfer it to external file).
RE: Running "External Macros" in Excel
One other pre-requisite: In the VBA editor, in Tools > References, check a reference to Microsoft ActiveX Data Objects Library. I have 2.8 checked.
CODE
Here's what the source table looks like in the external workbook on sheet Part Master...
Here's how I'd use the function. On my sheet...
You could store functions like this in your PERSONAL.XLSB workbook or in an add-in. At one time I had about 100 such functions that I constructed to use from time to time as needed. I accessed Oracle tables, DB2 tables, Access tables, tables within my workbook and external workbooks.
Let us know how you'd like to proceed.
Skip,
Just traded in my OLD subtlety...
for a NUance!