VBA How to use a variable between subs?

Question

I'm pretty new to VBA and I don't know how to use a variable between subs. The code should be pretty self explanatory:

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strWeapon As String
If Target.Address = "$I$8" Then
    MsgBox "You pick up a sword"
    strWeapon = "Sword"
ElseIf Target.Address = "$I$9" Then
    MsgBox "You pick up a magic staff"
    strWeapon = "Magic"
ElseIf Target.Address = "$I$10" Then
    MsgBox "You pick up a bow and arrows"
    strWeapon = "Bow"
End If
End Sub

Public Sub CommandButton1_Click()
If strWeapon = "Magic" Then
    UserForm1.Show
ElseIf strWeapon = "Sword" Then
    UserForm2.Show
ElseIf strWeapon = "Bow" Then
    UseForm2.Show
End If
End Sub

Is there a way I can use strWeapon in both subs? I get an error for not defining the variable strWeapon in the second sub. Thank you!

do you mean as a String variable that is updated and can be dispalyed in both Subs ? you can use the Public strWeapon as String , put this code line (for conviniance) above both Subs , under the Option Explicit — Shai Rado
– Shai Rado, Commented Dec 1, 2016 at 6:46
declare strWeapon outside the sub Worksheet_SelectionChange globally — Jahangir Alam
– Jahangir Alam, Commented Dec 1, 2016 at 6:47
I added it like you guys said to do and removed Dim strweapon as string at the top and now nothing happens when I click the commandButton — Ricky
– Ricky, Commented Dec 1, 2016 at 6:53
where is your CommandButton1 ? on which User_Form ? what exactly is your code suppose to do? — Shai Rado
– Shai Rado, Commented Dec 1, 2016 at 7:05
On second thoughts, your question is actually, "How do I keep a variable assigned after the sub routine has finished", is it not? — Preston
– Preston, Commented Dec 1, 2016 at 9:53

Preston · Accepted Answer · 2018-04-30 08:23:52Z

17

You have a couple of options.

You can use a public variable as shai rado has mentioned- this is probably easiest, however you should limit the use of public variables where possible.

You can pass the variable from one sub to another like this:

sub Main()
    dim strWeapon as string
    strWeapon = "Fork"
    WillIWin strWeapon
end sub

sub WillIWin(strWeapon as string)
    select case strWeapon
        case "Gun"
            msgbox "Probably"
        case "Sword"
            msgbox "unlikely"
        case "Fork"
            Msgbox "Goodluck"
    end select
End Sub

Alternatively if you have multiple variables to pass, you might be better with a class, this option takes more work than I'm willing to put in here, but CPearson has a good introductory course for them.

edited Apr 30, 2018 at 8:23

answered Dec 1, 2016 at 9:46

Preston

8,26511 gold badges64 silver badges96 bronze badges

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

Comments

mojo3340 · Accepted Answer · 2016-12-01 10:23:11Z

2

yes!

Public strWeapon as String

At the top of any module, do the following:

tpye Option Explicit then immediately below, put the Public declaration. this can then be used in any other subroutine OR module within your excel project

answered Dec 1, 2016 at 10:23

mojo3340

5691 gold badge6 silver badges27 bronze badges

Collectives™ on Stack Overflow

VBA How to use a variable between subs?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related