Hi,
I'm trying to access a variable in sub 2 which was declared in sub 1, both within the same module. How do I do this i.e. I want to declare a variable in sub 1 such that its accessable in sub 1 and sub 2. I've tried declaring the variable as global above both subs (in the declarations section), but it doesn't seem to be working
Thank you!
example, enter this at the top of a general module (eg Module1).
Then refer to it in your worksheet event code as you would any other variable. For example.
Public lTest As Long
hope this helpsPrivate Sub Worksheet_SelectionChange(ByVal Target As Range) lTest = lTest + 1 MsgBox "You've moved " & lTest & " times." End Sub
In (General)declarations declare variable like this and try.
Dim k as long
You can't if the variable was declared in sub1. You would need to pass it into sub2 as an argument.
Or move the declaration outside of sub1 and have it available to all routines in the module.
If you declare the variable in sub1 AND outside of all routines the variables used in sub1 and sub2 will be different variables with different scope but with the same name.
you can declare a module level variable that is used by both bits of code (but do NOT declare the same variable again in either code) or you can pass the variable from one routine to the other which is usually better
Thanks for the replies.
I'm not sure what I'm doing wrong, but I tried those suggestions and I'm not getting it to work. Here is a sample copy of my code:
And its throwing an error when I run sub temp, saying that object is required at the "consolidatedFile.activate" lineDim desiredDate As String Dim ConsolidatedFile as String Public Sub getConsolidatedFile() ConsolidatedFile = activeworkbook.name end sub public sub Temp () Application.Workbooks(ConsolidatedFile).Activate end sub
Any help would be awesome! Thanks!
Last edited by arlu1201; 04-19-2012 at 05:59 AM. Reason: Use code tags in future as per forum rules.
that line ain't in the code you posted. if it's in your real code then the problem is that you're trying to use a string as a workbook.Originally Posted by MikeFranz123
Ok, no problem, I sorted it out. Thanks so much to everyone for their help, much appreciated!
I assume you got it to work by running the other sub first.Dim desiredDate As String Dim ConsolidatedFile as String Public Sub getConsolidatedFile() ConsolidatedFile = activeworkbook.name end sub public sub Temp () getConsolidatedFile Application.Workbooks(ConsolidatedFile).Activate end sub
Also I assume your example is contrived as I don't see the point of activating the activeworkbook.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks