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!

Answer 1

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.

Answer 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