Running "External Macros" in Excel

[JTMergens]

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

 

[SkipVought]

Hi,

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JTMergens]

Yes, this is definitely along the lines of what I am looking for. I am hoping that the external work book can also crunch numbers and then return a result based on the information in the main workbook I am using in addition to just looking up information, but this points me in the right direction. I have never looked into making custom functions in excel so I suspect I have a bit of reading ahead of me to make it work, but again, thank you for pointing me in the right direction.

Josh

 

[SkipVought]

I am hoping that the external work book can also crunch numbers and then return a result based on the information in the main workbook I am using in addition to just looking up information

"...return a result..."

What kind of a result? A value, a table, a ???





Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JTMergens]

It would return a value, but it would have to compute that value based on the input, not just look up a value.

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.

 

[SkipVought]

You essentially have a bunch of lookup tables. Your external sheet is designed to display intermediate values that would be used in another lookup table, et cetera. May need to look at a specific external workbook example.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Denial]

Wouldn't developing an Add-In be the most straightforward way to achieve this sort of thing?

 

[JTMergens]

Are you proposing an Add-In that would perform all of the task I am trying to do to design the beam? Or an Add-In that would link the workbooks in the way I proposed? I am new to the concept of Functions or Add-Ins in excel so I would prefer to keep as much of this in workbooks as possible to better track what is happening, but if that's not possible I will just have to start learning more about VBA etc.

 

[Denial]

A VBA AddIn can be thought of as a collection of user defined functions, available to any workbook running in an Excel environment where the AddIn has been loaded and activated.[ ] The "functionality" (pun noted but not intended) within the AddIn is restricted to what can be achieved by Functions rather than Subs.[ ] This means that the only raw data available to a function within the AddIn are the contents of cells used as arguments to the function, and the only results that can be returned from the function will appear as values in the cell or cells from which the function has been called.[ ] This latter restriction is not as severe as in might initially appear because the function can be created as an "array function", thereby allowing multiple results to be returned in the one fell swoop.

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.)

 

[FreddyNurk]

Macros aren't, strictly speaking, analogous to methods applied to objects, in this context macro is the Office term for guided development of VBA code (i.e. the macro recorder allows you to manually carry out a process, and it 'records' the steps for you, which is then accessible either as macro shortcuts, or through VBA), although the goal of code repeatability is a good one.
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).

 

[SkipVought]

For instance, here's a function that accesses a table in an external workbook, performs a query to return a value. The function can be used on your sheet like any other spreadsheet function. I store my functions in a MODULE in my PERSONAL.XLSB workbook, which automatically opens when I open 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.

Function Nomen([highlight #FCE94F]PN As String[/highlight]) As String
'SkipVought Eng-Tips Sep 20, 2016
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
    Dim cnn As ADODB.Connection, rst As ADODB.Recordset
'[b]change this path and workbook name to your source workbook[/b]
    sPath = "C:\Users\Skip\Documents"    '[highlight #729FCF][b]<<<your path here[/b][/highlight]
    sDB = "TT_DB.xlsm"                   '[highlight #729FCF][b]<<<your workbook name here[/b][/highlight]
'[b]this ODBC drive may work for you-other drivers at this URL[/b]
'[b][URL unfurl="true"]https://www.connectionstrings.com/excel-2013/[/URL][/b]
    sConn = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
    
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    
    cnn.Open sConn
'[highlight #729FCF][b]this is where you would need to code the SQL for your particular table/field[/b][/highlight]    
    sSQL = "Select Distinct "
    sSQL = sSQL & " [Nomenclature] "
    sSQL = sSQL & "From [Part Master$] "
    sSQL = sSQL & "Where [Part Number] = '" & [highlight #FCE94F]PN[/highlight] & "'"   '[highlight #729FCF][b]<<<Here's where the parameter gets plugged in[/b][/highlight]
    
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
    
    On Error Resume Next
    
    rst.MoveFirst
    
    If Err.Number = 0 Then
        Nomen = rst(0).Value
    Else        '[b]no parameter in table[/b]
        Nomen = ""
        Err.Clear
    End If
    
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

Here's what the source table looks like in the external workbook on sheet Part Master...
[Pre]
Part Number Nomenclature
ABC D GOLDFISH
LMN O GOLDFISH
OS AR
[/pre]

Here's how I'd use the function. On my sheet...
[pre]
PN List Nom
LMN =PERSONAL.XLSB!nomen(A2)
ABC =PERSONAL.XLSB!nomen(A3)
[/pre]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]