Using variables in multiple macros

Question

Can someone explain what am I doing wrong here. Excel doesn't seem to set the variable value if I run the second macro before the first one. I would like to set the values just once and use them in multiple macros. I have 6 macros that will be using the same values. The code included below is a short code I use to test.

Option Explicit

Dim Test As String

Sub sub1()

Test = "Test"
MsgBox Test

End Sub

Sub sub2()

MsgBox Test

End Sub

This is the popup i get if the second macro is executed before the first one.

You can use Public variables; you "dim" them over your first code and they'll be open for every macro to be used. Ex : Public Constant gravity as double = 9.8 — Patates Pilées
– Patates Pilées, Commented Aug 13, 2020 at 19:13
If you run the sub2 first, Test has not been assigned a value and so will be an empty string. You can call subs from each other. — SJR
– SJR, Commented Aug 13, 2020 at 19:13
@SJR So i created another macro and name it "setvalue" and call it in every macros I have. That worked but are there cleaner ways to do this? — Aaron
– Aaron, Commented Aug 13, 2020 at 19:23
If you know the value in advance you could use a constant learn.microsoft.com/en-us/office/vba/language/concepts/… You can't then change its value though. Or have one master macro which calls the others, depends on the details of what you're trying to do. — SJR
– SJR, Commented Aug 13, 2020 at 19:24
@PatatesPilées i tried Public Test as String = "Some Text" and got a compile error. Am i missing something? — Aaron
– Aaron, Commented Aug 13, 2020 at 19:28

SJR · Accepted Answer · 2020-08-13 19:52:56Z

1

Just call the second macro from the first. Otherwise Test will not be assigned a value.

You can read about scope here.

Dim Test As String

Sub sub1()

Test = "Test"
MsgBox Test
sub2 'or call sub2

End Sub

Or you can use a Constant, e.g.

Public Const Test As String = "Test"

edited Aug 13, 2020 at 19:52

answered Aug 13, 2020 at 19:15

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aaron Over a year ago

adding Const Test As String = "Test" in Declaration worked! Please post your answer and I will accept. I can accept this one too if you prefer.

SJR Over a year ago

OK, have updated my answer. Note the stuff about scope in the link above.

Lewis Morris · Accepted Answer · 2020-08-13 19:32:05Z

1

(see my edit below - the original answer doesn't answer the question you asked properly)

You can set it as a public variable

Option Explicit

Public Test As String

Sub sub1()

    Test = "Test"
    MsgBox Test

End Sub

Sub sub2()

    MsgBox Test

End Sub

Or call sub2 from sub1 with a parameter

Sub sub1()

    Test = "Test"
    MsgBox Test
    'call the second sub with the Test variable here
    sub2 Test

End Sub

Sub sub2(test as string)

    MsgBox test

End Sub

or have Sub1 be a function and return the value

Function sub1()

    Test = "Test"
    MsgBox Test
    'call the second sub with the Test variable here
    sub1 = Test

End Function

Sub sub2()
    test = sub1()
    MsgBox test

End Sub

EDIT :

Or you could wrap it all in another sub so you are able to define the public variable

Public Test as string 

Sub sub1()

    MsgBox Test

End Sub

Sub sub2()

    MsgBox Test

End Sub

Sub run()
    Test = "Test"
    sub1()
    sub2()

End Sub

edited Aug 13, 2020 at 19:32

answered Aug 13, 2020 at 19:17

Lewis Morris

2,2144 gold badges35 silver badges46 bronze badges

2 Comments

SJR Over a year ago

But if you call the second sub first OP will have the same issue as above.

Lewis Morris Over a year ago

To be fair i didn't read his question properly until you wrote this!! My bad

Super Symmetry · Accepted Answer · 2020-08-13 19:18:32Z

0

If you want a variable to keep its value for the whole excel session (i.e. a static variable) use the Global keyword

Global Test As String

Remember, one of your procedures must assign a value to it. Otherwise it will have a value of ""

answered Aug 13, 2020 at 19:18

Super Symmetry

2,8851 gold badge9 silver badges17 bronze badges

Collectives™ on Stack Overflow

Using variables in multiple macros

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related