Doing my Best to teach myself VBA and came accross this peice of code online that unhides all of the hidden worksheets.
Code:
Sub UnhideAllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub
My question is about the Dim statement.
If i understand what i have learned so far,
Dim ws As Worksheet just declares "ws" as a worksheet could'nt I do away with that line/statement and just type out Worksheet? or is there a benifite to makeing that declaration.
Code:
Sub UnhideAllSheets()
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Visible = xlSheetVisible
Next Worksheet
End Sub
The blue Worksheet and the green Worksheet are completely different things. The blue one is a data type and makes VB create ws as an object variable. At the time of creation, the ws varable is Nothing... it is awaiting an action that will make it "point" to an actual worksheet in your workbook. One way to do that is by a direct assignment of that reference. For example...
Set ws = Sheets("Sheet1")
Set is used with objects because you do not assigning values to objects, you give the a reference to an existing object of some kind (in this case, the "kind" is a worksheet). So after the above assignment, ws will reference Sheet1... anything you do to a property, or with a method, of ws will be done to/with Sheet1... ws and Sheet1 are one in the same so long as ws is not given a reference to another worksheet. Another way to set a reference to an object is implicitly though a For..Each statement. What For..Each does is automatically set reference for ws, one-at-a-time for each iterated loop, from a collection of like objects... Worksheets (note the "s" on the end) is such a collection which contains all of the worksheets in the workbook.
The green Worksheet, on the other hand, is a Variant variable (the automatic type declaration for a variable that has not been Dim'med). Variants can be equal to anything... numbers, strings, objects, etc. Because a Variant is not an object (it can hold an object, but it is not an object in and of itself), you would not need the Set keyword when assigning an object to it. So, your green Worksheet variable can be used in a For..Each loop just like ws was used in your first example (but as Andrew said, it is not a good idea to name your variables the same as something predefined/existing within VBA itself). It may seem like using Variants is "simpler" and/or "easier" than Dim'ming variables and, to some extent, it is BUT Variants are highly inefficient (both memory-wise and execution speed-wise) and can subject your code to hard-to-find errors (you accidentally assign a different data type to it than what was intended... you will get no error warnings at run time because Variants can be assigned anything, so you variable may not have the value you think it should later on it your code). As Andrew said, Variants should be avoided when a properly declared variable can be implemented in its place.
as I hurried it up so I could respond to a request from my wife for help with something (you know, "I'll be right there dear, just give me one second more..."
