×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel VBA to Pass Var between file macros after var changes

Excel VBA to Pass Var between file macros after var changes

Excel VBA to Pass Var between file macros after var changes

(OP)
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:

CODE

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

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

RE: Excel VBA to Pass Var between file macros after var changes

Is this what you are looking for?

CODE

Option Explicit

Sub calling_macro()
Dim bob As String

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

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

RE: Excel VBA to Pass Var between file macros after var changes

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

RE: Excel VBA to Pass Var between file macros after var changes

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

RE: Excel VBA to Pass Var between file macros after var changes

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

RE: Excel VBA to Pass Var between file macros after var changes

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

RE: Excel VBA to Pass Var between file macros after var changes

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

RE: Excel VBA to Pass Var between file macros after var changes

I'd go with combo's solution

RE: Excel VBA to Pass Var between file macros after var changes

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

RE: Excel VBA to Pass Var between file macros after var changes

Why not just use a function that returns a value?

RE: Excel VBA to Pass Var between file macros after var changes

(OP)
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!

RE: Excel VBA to Pass Var between file macros after var changes

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

RE: Excel VBA to Pass Var between file macros after var changes

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

RE: Excel VBA to Pass Var between file macros after var changes

Quote (remeng)


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

CODE

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:

CODE

Sub calling_macro()
    Dim bob As String

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

    ...
    ...

    bob = getBob()
    MsgBox bob

End Sub 

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close