VBA variables defined one sub and called in another in multiple subs

Question

I want to call a sub that I've written in another sub, and add a couple new things to it, but use the variables that I've defined in the first sub don't show up in the second, even though the first macro is called within the sub.

For example, I've defined the variables as Public outside of the subs, but the values I define in the first sub, and then call in second sub gets lost. In the code below, running the macro "test" works, but "test1" gives me a "Run-time error '1004'".

Public row1 As Integer
Public col1 As Integer

Sub test()

row1 = 2
col1 = 2
ActiveSheet.Cells(row1, col1).Select

End Sub

Sub test1()

Call test

ActiveSheet.Cells(row1, col1).Resize(6, 5).Select

End Sub

Any guidance on how to get row1 and col1 to work in the test1 sub would be great. Is there a better way to achieve what I'm trying to do?

code works for me as is. I would recommend qualify the sheet you want to work to a variable Dim ws as Worksheet | Set ws = Sheets("Sheet1") and working with that ws.Cells(row1,col1).Resize(6,5).Select`, but since this is probably just test code for the question it's probably not that big of a deal. — Scott Holtzman
– Scott Holtzman, Commented Mar 10, 2016 at 17:24
Why do the subs have to be split? If you are doing some complex thing in another sub, you can pass variables through the call and then back with a result. — Chrismas007
– Chrismas007, Commented Mar 10, 2016 at 17:25
Thanks, Scott Holtzman, but I don't understand -- doesn't the "ActiveSheet." take care of that? Also, would you put the "Dim ws" statement in the first sub, or both? — Eric
– Eric, Commented Mar 10, 2016 at 23:38
Chrismas007: the first sub/macro is updating/refreshing data that I want to leave as dynamic while in a session, and the second sub is going to freeze those values so they can be used offline. I want to call the updating macro before freezing the data to be sure everything is current, but the second sub will use many of the variables in the first sub. Does that make sense? I can re-assign the variables in the second sub, but it seems like the variables should already be a defined since it's calling the macro. But may I'm misunderstanding how this works in VB. Thanks. — Eric
– Eric, Commented Mar 10, 2016 at 23:45

Chrismas007 · Accepted Answer · 2016-03-10 17:35:10Z

1

An alternative option is to pass variables from sub to use a Public Function like this:

Sub MainSubHere()
    'Some random code
    Dim AddMe1 As Long, AddMe2 As Long, MySum As Long
    AddMe1 = 2
    AddMe2 = 2
    MySum = AddMeSub(AddMe1, AddMe2)
    Msgbox MySum
End Sub

Public Function AddMeSub(AddMe1 As Long, AddMe2 As Long) As Long
    AddMeSub = AddMe1 + AddMe2
End Sub

edited Mar 10, 2016 at 17:35

answered Mar 10, 2016 at 17:28

Chrismas007

6,1154 gold badges27 silver badges47 bronze badges

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

Comments

Mark S. · Accepted Answer · 2020-02-20 21:27:21Z

0

You can try, but there's other stuff to improve here, such as using Select rathering then just going to the action statement. I left out that sub out since it served no purpose.

Dim row1 As Integer, col1 As Integer

Private Sub DefineVars()
Set row1 = 2
Set col1 = 2

End Sub

Sub test()

Call DefineVars

ActiveSheet.Cells(row1, col1).Resize(6, 5).Select

End Sub

answered Feb 20, 2020 at 21:27

Mark S.

2,8661 gold badge10 silver badges30 bronze badges

Collectives™ on Stack Overflow

VBA variables defined one sub and called in another in multiple subs

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related