Using the same variables for multiple macros

[OldManDemosthenes]

I have a macro (macro1) which sets up several variables. Is it possible to have macro2 call and use the variables setup in macro1?

So the Code for macro1 would look something like this

Sub Macro1()
Dim ALocation As Range, ARow As Long
[INDENT]Set ALocation = Worksheets("Test").Range("A:B").Find("String")[/INDENT]
[INDENT][B]ARow[/B] = ALocation.Row[/INDENT]
End Sub

and macro2

Sub Macro2()

Call Macro1
Worksheets("Test").Range("C1") = Worksheets("Test").Cells([B]ARow[/B], 10).value

End Sub

The problem I am having is that Macro2 is not recognizing the dim variables (ARow and others) from Macro1

Thanks!

 

[texasalynn]

you need to setup those macros as public

Public ALocation As Range, ARow As Long
and this would go before the Sub line

 

[AlphaFrog]

You want to declaire the shared variables at the Module-level.

Dim ARow As Long

Sub Macro1()
    Dim ALocation As Range
    Set ALocation = Worksheets("Test").Range("A:B").Find("String")
    ARow = ALocation.Row
End Sub

Sub Macro2()
    Call Macro1
    Worksheets("Test").Range("C1") = Worksheets("Test").Cells(ARow, 10).value
End Sub

Scope of variables in Visual Basic for Applications

 

[OldManDemosthenes]

Great that solves the problem

Thank you both!

 

[OldManDemosthenes]

So I've hit a snag....

The advice both of you provided worked great when going from macro to macro.

I now need to make it go from macro to userform. I tried searching but couldn't find anything on this.

I also thought about making the code running the userform reference a macro and run everything through macros - didn't work with the userform I have

I've tried adding the code from the original macros directly into the userfom, but the file becomes too large.

Any help?

 

[Norie]

There is another way you could do this, pass variables between the subs.

Option Explicit
Sub Macro1(MyRow As Long)
Dim ALocation As Range, ARow As Long
    Set ALocation = Worksheets("Test").Range("A:B").Find("String")
    If Not ALocation Is Nothing Then
        ARow = ALocation.Row
    End If
    MyRow = ARow
End Sub
 
Sub Macro2()
Dim MyRow As Long
 
       Call Macro1(MyRow)
 
    Worksheets("Test").Range("C1") = Worksheets("Test").Cells(MyRow, 10).Value
 
End Sub

Mind you this would probably be more suited to using a function.

Option Explicit
 
Function GetRow() As Long
Dim ALocation As Range, ARow As Long
    Set ALocation = Worksheets("Test").Range("A:B").Find("String")
    If Not ALocation Is Nothing Then
        ARow = ALocation.Row
    End If
    GetRow = ARow
End Function
 
Sub Macro2()
Dim MyRow As Long
    MyRow = GetRow
    Worksheets("Test").Range("C1") = Worksheets("Test").Cells(MyRow, 10).Value
End Sub

 

[AlphaFrog]

ARow is available to UserForm1 in the example below...

Public ARow As Long

Sub Macro1()
    ARow = 10
    UserForm1.Show
End Sub

 

[OldManDemosthenes]

Thanks for the very quick responses once again!

Those options both look great for individual variables (which is what I specified in my original post). Do they still work with many variables?

To be more specific with my problem, the user enters in a row number for roughly 60 variables.

So, for example, A1 is 'Test1', B1 the user enters 15 (for row 15). The userform then uses various vlookup and offset functions for row 15 in included database worksheets to draw data relating to 'Test1' and the userform selections. It repeats this process for each of the roughly 60 variables in relation to the userform selections

 

[Norie]

Are you sure you need 60 odd variables for that?

Are you going to use all of them in one search or whatever it is you are doing?

If the user is entering values via a userform you might not even need variables.

That might not seen to make sense but it's pretty easy to refer to controls on a userform and get their values.

 

[OldManDemosthenes]

Unfortunately I can't think of a better way of solving my problem than the 60 odd variables of row numbers.

The problem is that my model includes several database tables downloaded from the internet which must be updated once a year. While the database tables give the same general information, every year they list everything in a different order and change the titles for each category. So one year they could have "Clothes" and its related data in row 5 and the next they'll call it "Apparel" and it will be in row 40. Very frustrating to say the least

So my solution is to have a page with all of the categories listed in column A. The user then goes through the database tables and writes the appropriate row in in column B (this happens only once a year when updating model). This way I know that "Clothes/Apparel or whatever other name they come up with" will always be located in A1 of this worksheet. The userform can then use the row number listed in B1 to find which row the category is in for each of the database tables.