Excel: Defining a worksheet as a variable for use throughout the module
I'm very new to VBA and this one problem is driving me mad. I've read several forums and articles on the scope of variables but clearly I'm missing something. I'm trying to define a worksheet as a variable so that I may reference it in multiple subroutines throughout the module but I can't figure out where to Set it. It only seems to work if I set the variable within each subroutine (which defeats the purpose). Where have I gone wrong?
Dim wsFV As Worksheet
Dim wsOS As Worksheet
Sub SetVariable()
Set wsFV = Worksheets("FullView")
Set wsOS = Worksheets("Off-Site")
End Sub
Sub ClearForm()
wsFV.Range("E5:F55,H5:R55,X5:X55,E3").ClearContents
wsFV.Range("E5:R55,W5:X55").Interior.Color = RGB(255, 255, 255)
End Sub
Posted Aug 17, 2015, 7:26 AM UTC · edited
311
Welcome to public vs private variables!
At the top of your module, before the words 'sub sub name', write 'Public wsKv as worksheet
You can then use this variable anywhere, because it is public - and excel remembers the variable between procedures.
The main thing to watch out for, is if your code breaks (end/debug), or you hit 'stop' whilst not running code, and it gets cleared what it saved as the object.
You would typically still test before using it if there're gaps between the subs you want it to refer to. You'd do this with something like:
If wsKv is nothing then set wsKv = sheets("Sheetname")
The other way is to declare it as a Constant, but AFK, I can't remember how you do a worksheet with that. Google for 'Const as Worksheet' and see what you find.
Changing Dim to Public has no effect. I still get the following error: "Run-time error '91': Object variable or With block variable not set" I search for "Const as Worksheet" and generally found three kinds of responses: People saying you can't set a worksheet as a constant, people offering an elaborate workaround, and people who have reiterated what you and other forums have said (namely, use Dim above the first sub or use Public.
I've now toyed with With blocks and still no luck. I can see no other solution than to use
in every suroutine.
You still need to 'set' the variable at an early stage in one of your subs (the one that's going to fire before any others, ideally). Declaring it public outside of the sub just means it is available across all subs.
That's what's generating your error. Do the 'set wkVs = sheets("Sheetname")' as one of the first lines of your first sub. The rest of your subs should 'know' what worksheet that means from then on (so you only have to set it once)
Thank you, but that's just as I've been doing. In the code from my original post I set the variable in the first sub, yet when I get to the next sub 'Sub ClearForm()' the variable isn't set. The code fails.
Are you running ClearForm separately from SetVariable? You need to run SetVariable within the same execution session as any sub dependent on wsFV or wsOS. Changing ClearForm to the following would work:
If you have a bunch of subs for a particular procedure you could write something like this to keep the worksheet objects initialized:
The important part here is all public variables are destroyed when execution ends. So running SetVariable as is will create the worksheet objects, then end execution and destroy the objects.
you need to set the variable in each subroutine, or pass it as a parameter to any embedded routine.
otherwise declare a constant atop all your routines.
and that should be available to all your subs.
And you've done that after writing 'Public wkVs as Worksheet' outside the sub?