Excel VBA to Pass Var between file macros after var changes

[remeng]

Hi All;

Is there a way to pass a var from one excel doc macro to another and then change the var and return the new var into the original macro?

Here is what I have so far. The calling_macro correctly opens the SANDBOX - Macro from Macro - Source.xlsm file, passes bob="hi" to the msg_box macro.

The msg_box macro updates var bob to equal "ten".

When the macro returns to the calling_macro, bob does not update to equal "ten" but stays as "hi" instead of returning "ten".

Code for calling_macro in document 1:

Sub calling_macro()

Dim bob As String

bob = "hi"


Workbooks.Open ("C:\DUMMY FOLDER\SANDBOX - Macro from Macro - Source.xlsm"), ReadOnly:=True

Application.Run "'SANDBOX - Macro from Macro - Source.xlsm'!thisworkbook.msg_box", bob

MsgBox bob



End Sub

SANDBOX - Macro from Macro - Source.xlsm (document 2) and macro msg_box code

Sub msg_box(bob)

    MsgBox "Hello world"

    bob = "ten"
    

End Sub

I have looked into ByRef, but it doesn't return the updated value back to the original document. It will pass it to another document / macro though. I'd like to avoid chaining macros together instead of returning the updated var to the original macro.


Here is the reference article for ByRef Link

Thanks for the assistance.

Mike

 

[Andrzejek]

Is this what you are looking for?

Option Explicit

Sub calling_macro()
Dim bob As String

bob = "hi"
MsgBox bob
Call msg_box(bob)
MsgBox bob  [green]'The value of bob is 'ten' here[/green]

End Sub

Sub msg_box(ByRef strbob As String)

MsgBox strbob
strbob = "ten"
    
End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[remeng]

Hi Andy,

That only works if you are keeping all of the macros in a single file. Because this is two different files, the var needs to pass from the second file back to the first. That is where I need to find a solution.

 

[combo]

Are the two excel files fixed, i.e. 'SANDBOX - Macro from Macro - Source.xlsm' has always the same name, macro and folder, and you call the macro from specific workbook only? If so, you can rename VBA project in 'SANDBOX...' workbook, next create reference in VBProject of calling workbook to renamed project in 'SANDBOX...'. It will give you access to public procedures and functions in referenced VBA project.

combo

 

[remeng]

Hi Combo

The second workbook will always have the same name (source). The first workbook won't due to rev changes.

What is VBProject? I've never seen that before.

 

[Andrzejek]

Another way would be to move all your Macros into a Personal.xlsb, a hidden workbook that opens every time you open Excel. This way all macros, functionality, (whatever) are available to you no matter what other workbook you need to work with.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

VBA project is what you can see for each workbook in VBE window, VBprojects subwindow. It is a collection of modules, class modules, userforms and excel document modules (workbook, worksheets and chartsheets). The default name VBproject can be changed.
Having two workbooks open, still in VBE, you can add reference to another workbook, like to other library, this will allow access to subs and functions here.

combo

 

[strongm]

I'd go with combo's solution

 

[combo]

To change VBA project name:

Working with VBA library in external excel file:

The drawback is that excel automatically opens referenced workbook, that has to be closed manually. If you do not work with data in referenced workbook, it is possible to hide its window. In this case however I would consider creating excel add-in, as an extension of excel functionality.

combo

 

[mintjulep]

Why not just use a function that returns a value?

 

[remeng]

Hi Andrzejek - I can't use Personal since these files will be used by multiple users. I'd like to avoid setting up Personals on all of the potential user's computers.

Hi Mint Julep - I've tried the function approach but it won't return the value to the original file's macro. The function in the first file just doesn't update. What can work is that the second document calls a second macro in the first document, but this just makes the file more cluttered.

Hi Combo - Thanks for the info. Do you have some articles I can reference to understand this in more detail?

Since multiple users are going to be accessing the Excel workbooks, does this VBAProject stay with the workbook or does it stay at the creator's computer?

Can I have multiple VBAProjects in the same workbook?

Thanks again everyone!

 

[combo]

Each macro enabled workbook has one VBA project. It is embedded in the workbook. When you add modules and write code, you work with the project - you had to see it in the VBE window. It the same in Word, Powerpoint and Access.

A quick search on this topic:

https://www.automateexcel.com/vba/project/

combo

 

[mintjulep]

Why don't you explain what you need to do, not how you think it should be done.

 

[mikrom]

Is there a way to pass a var from one excel doc macro to another and then change the var and return the new var into the original macro?

You can use a simple text file, or XML file, JSON file or a database file (like Access or SQLite)

IMO, the simplest is the using of the text file - like this:

Add to your macros these 2 routines

Function getBob()
    myfile = "c:\tmp\bob.txt"
    Open myfile For Input As #1
    Line Input #1, value
    Close #1
    getBob = value
End Function

Sub setBob(value)
    myfile = "c:\tmp\bob.txt"
    Open myfile For Output As #1
    Print #1, value
    Close #1
End Sub

(if you don't have c:\tmp on your PC create it.)

Then modify your subroutines like this:

Sub calling_macro()
    Dim bob As String

    bob = "hi"
    Call setBob(bob)
    bob = getBob()
    MsgBox bob

    ...
    ...

    bob = getBob()
    MsgBox bob

End Sub

Sub msg_box()
    MsgBox "Hello world"
    
    bob = "ten"
    Call setBob(bob)
    
End Sub