CODEWITHSUNDEEP
excelvba
Drager
Drager

Reputation: 47

Some doubts about Dim and Set Worksheet

Normally, Dim should be done first and then Set should be done in vba code, like the below code. 

Dim xWs1 As Worksheet
Dim xWs2 As Worksheet
.
.
.
Dim xWsN As Worksheet
Set xWs1 = Worksheets("A")
Set xWs2 = Worksheets("B")
.
.
.
Set xWsN = Worksheets("NNN")

If I need to Dim and Set 5 worksheets, 10 lines of code will be needed. It seems that the code will be too long when I need to Dim and Set more than 5 worksheets.

I found that if I just use Set, the vba code can also run properly. I would like to ask this will cause any problems if I didn't use Dim?

Set xWs1 = Worksheets("A")
Set xWs2 = Worksheets("B")
.
.
.
Set xWsN = Worksheets("NNN")

Thanks!

Upvotes: 4

Views: 1406

Answers (3)

ashleedawg
ashleedawg

Reputation: 21619

How to Declare and Set 92 Objects using 92 Characters:

If "taking up space" is your concern, and you hypothetically want to explicitly set 5 procedure-level variables (w1..w5) to Set to 5 worksheets (Sht1..Sht5), you could use:

Example #1:

DefObj W
Sub wSet1()
  Dim w1, w2, w3, w4, w5
  Set w1=[Sht1]:Set w2=[Sht2]:Set w3=[Sht3]:Set w4=[Sht4]:Set w5=[Sht5]
End Sub

...or, even more space-efficient, if for example, you had 92 worksheets to Set in 92 declared variables? Then:

Example #2:

DefObj W
Sub wSet2():Dim wks(1To 92),x:For x=1To 92:Set wks(x)=Sheets("Sht"&x):Next x:End Sub

(That's 92 characters... not counting the line feed!)

Explanation:

Between these two ways of shortening declaration, we're using six shortcuts. Below is a summary of each, and see the links under "More Information" for full documentation on each.

Disclaimer: There are a number of reasons we shouldn't use shortcuts in programming. The obvious one is that, the more you compress code, the harder it is to read and understand (especially by others), and therefore can be harder to troubleshoot or expand upon.

If you don't know what the "standard methods" are, do not learn the shortcuts first! Learn how to do things "THE RIGHT WAY" before learning the shortcuts, no matter how appealing it may seem. There was a time that I argued that neatness like indentation and commenting, and full, proper techniques, didn't matter. I was wrong; had to learn that the hard way. If you're reading this, you'll probably have to learn the hard way too, but at least:

Don't use shortcuts when posting example code in your Stack Overflow questions. (This is not a method of [MCVE]!) You will probably get yelled at! ...and possibly have you questions down-voted or ignored... You were warned!

✓ DefObj (Default Data Types)

[Deftype statements][1] are a forgotten method of declaring default data types.  Normally, the default data type is [`Variant`][2], so this:

Dim myVariable as Variant

...is identical to:

Dim myVariable

...however the DefObj W statement (used at module-level) says:

All variables declared in this module, that start with the letter 'W' default to type Object (unless otherwise specified). Note that Deftypes statements must be used at module-level (before your first Sub).

The entire list: (More Info)

DefBool DefByte DefCur DefDate DefDbl DefDec DefInt DefLng DefSng DefStr DefObj DefVar

✓ , (Commas in 'Dim' Statements)

When declaring variables with Dim, multiple variables can be listed on the same line, separated with a comma. Therefore this:

Sub mySub()
    Dim myVariable1 as Currency  
    Dim myVariable2 as Currency  
    …

...is identical to this: (combining examples with Deftypes)

DefCur m
Sub mySub()
    Dim myVariable1, myVariable1 
    …

✓ Sheets ('Sheets' collection)

  • The WorkSheets Object refers to the collection of all the Worksheet objects in the specified or active workbook.

  • The Charts Object` refers to the collection of **all the Chart objects in the specified or active workbook.

    • But the **Sheets Objectrefers to ***both*** theWorksheets*and*Charts` collections.

So, if a workbook has 3 worksheets and 2 chart sheet, in VBA:

  • Sheets.Count will return 5

  • Worksheets.Count will return 3

Warning: Using Sheets could cause a conflict if you have a Chart and a Worksheet with the same name (and should also be avoided when referring to worksheets in other files). But for a simple single-file, worksheet-only workbook, save yourself some Work and stick with just Sheets.

✓ [ ] (Square-Bracket Reference Shortcuts)

[Square brackets] can be used as a shortcut when referring to Worksheets, Cell Ranges and individual Cells. You can use either the A1 Reference Style or a named range within brackets as a shortcut for the Range property. You do not have to type the word "Range" or use quotation marks.

Worksheets("Sheet1").[A1:B5].ClearContents 

[MyRange].Value = 30

This is barely documented, and even less documented is the fact that, if used in the logical order, square brackets can be used to refer to worksheets.

Combining examples, all of these statements will have identical result:

Worksheets("Sheet1").Range("A1") = Now()
Sheets("Sheet1").Range("A1") = Now()
Worksheets("Sheet1").[A1] = Now()
Sheets("Sheet1").[A1] = Now()
[Sheet1].[A1] = Now()

✓ wks() (Variable Arrays)

If you have a large number of similar objects to declare, it's often easier (and more organized) to group them together in an array. An array can be declared as any type including, for example, Object, Worksheet. (...or even the rarely-used and bizarre types like LongLong and IConverterApplicationPreferences. (Apparently whoever thought up that one doesn't care for shortcuts.)

✓ For..Set..Next (Loop to Set Variable Arrays)

When using an array of objects (any any variable sets), the next logical step is to reduce code with any tasks that need to be performed on the entire group of objects.

Other Notes:

Example #1 could have been compressed to one line but I wanted it to be easy to read in the answer. If our sheet names were S1..S5 instead of the oh-so-lengthy Sht1..Sht5, and we use the :, we could accomplish the same thing in 105 characters:

Example #1b:

DefObj W
Sub wSet():Dim w1,w2,w3,w4,w5:Set w1=[S1]:Set w2=[S2]:Set w3=[S3]:Set w4=[S4]:Set w5=[S5]:End Sub

Data Type Shortcut Symbols

Another rarely used set of dates back to 1974: data type shortcuts chosen by Gary Kildall for the CP/M Operating System

Symbol  Data Type  Constant                                                            

%       Integer    vbInteger = 2
$       String     vbString = 8
&       Long       vbLong = 3
@       Decimal    vbDecimal = 6
!       Single     vbSingle = 4
#       Double     vbDouble = 5

Still supported today in many coding languages, you could for example use these interchangeably:

Dim myVariable as String
Dim myVariable$

More Information:

Upvotes: 2

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

Take the following example of why using implicit variable declaration is usually a bad idea:

Sub Test()

    myVariable = 10

    myOutcome = myVaraible + 5

End Test

myOutcome = 5. Can you see why?

I misspelled myVariable in the second line, so I just essentially created a brand new variable myVaraible (which had a default value of 0).

This is why you should always use Option Explicit at the beginning of every module; and why you should always explicitly declare all variables.

While it still works, you are just setting yourself up for needless debugging headaches.

If your issue is that you want to condense your code to use less lines, you can do something like this:

Option Explicit

Sub Test()

    Dim myVariable As Long:    myVariable = 10
    Dim myOutput As Long
    myOutput = myVariable + 5

End Sub

You can also declare multiple variables on the same line:

Option Explicit

Sub Test()

    Dim myVariable As Long, myOutput As Long
    myVariable = 10
    myOutput = myVariable + 5

End Sub

Not necessarily recommending this (as it can degrade readability), but it's yet another method of declaring variables. This does require the same data type, but you can add your worksheets in an array (from your example):

Option Explicit

Sub Test()

    Dim xWs(1 To 5) As Worksheet

    Set xWs(1) = Worksheets("A")
    Set xWs(2) = Worksheets("B")
    Set xWs(3) = Worksheets("C")
    Set xWs(4) = Worksheets("D")
    Set xWs(5) = Worksheets("E")

End Sub

Upvotes: 1

Imran Malek
Imran Malek

Reputation: 1719

If you don't use dim statement , variable is automatically created as a Variant type.

The Variant type can be an integer, a string, a workbook, or any of the other type of variable and it can change as the variable changes, one moment it can be a string, then it can be changed to a workbook.

Using Dim

enter image description here

Without using Dim

enter image description here

There are mainly two problems with not using Dim,

  1. Variant types uses more computer memory as a result it will make your code slow especially when you use loops.
  2. Difficult to find errors ( in your case you can assign anything to variable XWs1 such as numbers , names etc. which can be avoided if you use dim)

Upvotes: 2

Related Questions