When I declare an object variable in a subroutine using the dim statement, can I use that object variable throughout the module or only in that subroutine. What about if I use the public statement?
Excel Facts
Which lookup functions find a value equal or greater than the lookup value?
declaring on top of the module
variable a will be recognized in the enire module
run aa
Code:
Dim a as string
Sub aa()
a = "string"
bb
End Sub
Sub bb
MsgBox a
End Sub
public ==> variable lives as long as workbook is open
Code:
Public a As String
Sub aa()
a = "ert"
End Sub
Sub bb() 'can be in other module
MsgBox a
End Sub
some experts will tell you to avoid public variables
you can use instead "calling procedures with arguments"
like this
Code:
Sub runthis()
Call macro(1, 3)
Call macro(2, 5)
Call macro(99, 101)
End Sub
Sub macro(var1 As Integer, var2 As Integer)
MsgBox var1 & " + " & var2 & " = " & var1 + var2
End Sub
'or just use
'macro 1, 3
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.