CODEWITHSUNDEEP
vbaexcelvariables
Ricky
Ricky

Reputation: 1173

VBA How to use a variable between subs?

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!

Upvotes: 9

Views: 98880

Answers (2)

Preston
Preston

Reputation: 8187

You have a couple of options.

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

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

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

Upvotes: 17

mojo3340
mojo3340

Reputation: 549

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

Upvotes: 1

Related Questions