Some doubts about Dim and Set Worksheet

Question

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

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 "Remove" and "Install 5 sheets," it will take 10 lines of code. It seems that the code will be too long when I need Dim and install more than 5 worksheets.

I found that if I just use Set, the vba code may also work correctly. I would like to ask that this will cause problems if I did not use Dim?

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

Thank!

Answer 1

Let's take the following example of why using an declaration of implicit variables is usually bad:

Sub Test()

    myVariable = 10

    myOutcome = myVaraible + 5

End Test

myOutcome= 5. Can you understand why?

myVariable , myVaraible ( 0).

Option Explicit ; .

, .

---

, , , - :

Option Explicit

Sub Test()

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

End Sub

---

:

Option Explicit

Sub Test()

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

End Sub

---

( ), . , ( ):

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

Answer 2

dim, Variant.

Variant , , , , .

Dim

Dim

Dim,

• , , .
• ( - XWs1, , .., , dim)

Answer 3

92 92 :

" " , 5 (w1..w5) Set 5 (Sht1..Sht5), :

# 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

---

... , , , , 92 Set 92 ? :

# 2:

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

_{( 92 ... !)}

---

:

. . " " .

:. , . , , ( ), .

_{, " ", ! , " ", , . , , , , , . ; . , , , , , :}

_{. ( [MCVE]!) , !... , , , ... !}

---

✓ DefObj _{^{( )}}

[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

... :

Dim myVariable 

... DefObj W ( ) :

, , , "W" , Object ( ). , Deftypes ( Sub).

: ( )

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

---

✓ , _{^{( Dim)}}

Dim , . :

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

... : ( Deftypes)

DefCur m
Sub mySub()
    Dim myVariable1, myVariable1 
    …

---

✓ Sheets _{^{( "" )}}

• WorkSheets Worksheet .

• Charts Object` ** .

  • ** Sheets refers to ***both*** the *and* .

, 3 2 , VBA:

• Sheets.Count 5

• Worksheets.Count 3

: Sheets , ( ). , , Work Sheets.

---

✓ [ ] _{^{( )}}

[ ] , . > . A1 Reference Style, Range. "Range" .

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

[MyRange].Value = 30 

, , , , . .

, , :

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

---

✓ wks() _{^{( )}}

, ( ) . , , , Object, Worksheet. _{(... , LongLong IConverterApplicationPreferences. (-, , .)}

---

✓ For..Set..Next _{^{(Loop )}}

( ) , .

---

:

# 1 , , . S1..S5 oh-so-lengthy Sht1..Sht5, :, 105 :

# 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

---

1974 : , Gary Kildall CP/M

Symbol  Data Type  Constant                                                            

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

, , , :

Dim myVariable as String
Dim myVariable$

---

:

• Microsoft.com: How to Break and Combine Expressions in Code (VB / VBA)

• MSDN: Access Cells Using Label Labeling

• Excel Hero: Excel VBA Shortcut List Links

• MSDN: Effective Use of Data Types

• MSDN: Dim Instruction (VBA)

• ExcelHowTo: vs. WorkSheetsSheets

• Stack Overflow: Difference Between and Objects WorkSheetsWorksheet

• MSDN: Application Set

• MSDN: Array Declaration