With/End With statement without the dot

[mark91345]

Using Excel 365, my VBA works, but I am curious about the With/End with section. I always assumed that I needed a "." to precede my entries within a With/End With statement. Why doesn't my code have the same need for a "dot" to precede the code? (I had to look online for some help with this and found that the code that worked did not have a dot). But why?

Lastly, is there a way to post code that includes the proper tabbing? This site removed my formatting when I pasted it in.



Sub With_No_Dot()
Dim CompInfo(1 To 8, 1 To 2)
Dim r As Byte, c As Byte
Const StartRow As Long = 6

Dim NewBook As Workbook
Dim ShNew As Worksheet

For r = 1 To 8
For c = 1 To 2
CompInfo(r, c) = Cells(StartRow + r, c).Value
Next c
Next r

Set NewBook = Workbooks.Add
For r = 1 To 8
With NewBook
Set ShNew = .Sheets.Add
ShNew.Name = CompInfo(r, 1)
Range("A1").Value = CompInfo(r, 1)
Range("A2").Value = CompInfo(r, 2)
End With
Next r
End Sub



Excel 2016 (Windows) 64 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

6​

[/TD]
[TD="bgcolor: #548235"]Company[/TD]
[TD="bgcolor: #548235"]Manager[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

7​

[/TD]
[TD]Jellyfish[/TD]
[TD]Camila Wilson[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

8​

[/TD]
[TD]Dasring[/TD]
[TD]Greg Earnest[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

9​

[/TD]
[TD]Fightrr[/TD]
[TD]Walter Jackson[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

10​

[/TD]
[TD]Kryptis[/TD]
[TD]Margaret Rivera[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

11​

[/TD]
[TD]Perino[/TD]
[TD]Eddie Chen[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

12​

[/TD]
[TD]Twistrr[/TD]
[TD]Rachel Crosby[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

13​

[/TD]
[TD]Hackrr[/TD]
[TD]Anthony Welch[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

14​

[/TD]
[TD]Pes[/TD]
[TD]Norma Ashley[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Activity[/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

With out the dot, the line Range("A1") assumes the default, i.e. the ActiveSheet.
Your code adds a new sheet to NewBook.
That makes the new sheet the ActiveSheet.
So they are equivelent.

Were you to add a dot to the Range("A1") line, it would error, since Range is a property of a worksheet object, but not a workbook object.
Full qualification would look like

Set NewBook = Workbooks.Add
For r = 1 To 8
    With NewBook
        With .Sheets.Add
            .Name = CompInfo(r, 1)
            .Range("A1").Value = CompInfo(r, 1)
            .Range("A2").Value = CompInfo(r, 2)
        End With
    End With
Next r

 

[Yongle]

Using Excel 365, my VBA works, but I am curious about the With/End with section. I always assumed that I needed a "." to precede my entries within a With/End With statement. Why doesn't my code have the same need for a "dot" to precede the code? (I had to look online for some help with this and found that the code that worked did not have a dot). But why?

Yes you must ALWAYS use the DOT to be sure you get the correct result
The code did what you wanted because (in this case) the ACTIVE workbook happened to be the one you wanted to refer to
But that would not always be the case

The location of the code also matters
- if the above code was in ThisWorkbook module (rather than a standard module) Sheets.Add would have inserted the sheet in the wrong workbook (even though the new workbook was active at the time)
- Range("A5") refers to the active sheet in the active workbook when code is in a standard module
- Range("A5") refers to A5 in Sheet1 when code is in Sheet1's SHEET module
- using With Sheets("Sheet2") means that .Range("A5") refers to A5 in Sheet2

is there a way to post code that includes the proper tabbing? This site removed my formatting when I pasted it in.

Click on # icon above post window
These things appear (code tags)
[ CODE][ /CODE]
[ CODE] paste code here between the tags [ /CODE]

and this happens

Sub With_No_Dot()
    Dim CompInfo(1 To 8, 1 To 2)
    Dim r As Byte, c As Byte
    Const StartRow As Long = 6
    
    Dim NewBook As Workbook
    Dim ShNew As Worksheet
    
    For r = 1 To 8
        For c = 1 To 2
            CompInfo(r, c) = Cells(StartRow + r, c).Value
        Next c
    Next r
    
    Set NewBook = Workbooks.Add
    For r = 1 To 8
        With NewBook
            Set ShNew = .Sheets.Add
            ShNew.Name = CompInfo(r, 1)
            Range("A1").Value = CompInfo(r, 1)
            Range("A2").Value = CompInfo(r, 2)
        End With
    Next r
End Sub

OR paste code into the post window first, select the code and then click on # icon above post window which places the code tags around the selected text

 

[mark91345]

Ah, so I have a nested With statement. I get it.

I am curious, I Dim'd a variable, "ShNew as Worksheet", that I would like to use within the With statement to add sheets, but I cannot figure out HOW to employ it.
For example, in the With statement,
With NewBook
With ShNew (then add sheets code here)

I have tried multiple permutations, but nothing works so far. Frankly, your code is better (and simpler), but I need to understand how to work with variables in the With statements (in addition to the raw code).

Dim CompInfo(1 To 8, 1 To 2)Dim r As Byte, c As Byte
Const StartRow As Long = 6


Dim NewBook As Workbook
Dim ShNew As Worksheet


    For r = 1 To 8
        For c = 1 To 2
            CompInfo(r, c) = Cells(StartRow + r, c).Value
          Next c
    Next r
    
Set NewBook = Workbooks.Add
    For r = 1 To 8
             With NewBook
                With .Sheets.Add
                    .Name = CompInfo(r, 1)
                    .Range("A1").Value = CompInfo(r, 1)
                    .Range("A2").Value = CompInfo(r, 2)
                End With
            End With
    Next r
End Sub

Excel 2016 (Windows) 64 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td=bgcolor:#548235]Company[/td][td=bgcolor:#548235]Manager[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]Jellyfish[/td][td]Camila Wilson[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]Dasring[/td][td]Greg Earnest[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]Fightrr[/td][td]Walter Jackson[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]Kryptis[/td][td]Margaret Rivera[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]Perino[/td][td]Eddie Chen[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]Twistrr[/td][td]Rachel Crosby[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]Hackrr[/td][td]Anthony Welch[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]Pes[/td][td]Norma Ashley[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Activity[/td][/tr][/table]

 

[Fluff]

In what way doesn't your code work? It works for me.

 

[mikerickson]

You could use the ShNew variable like this

    Set NewBook = Workbooks.Add
    For r = 1 To 8
             With NewBook
                Set ShNew = .Sheets.Add
                With ShNew
                    .Name = CompInfo(r, 1)
                    .Range("A1").Value = CompInfo(r, 1)
                    .Range("A2").Value = CompInfo(r, 2)
                End With
            End With
    Next r
End Sub

But it isn't needed for this task.

 

[mark91345]

You could use the ShNew variable like this

    Set NewBook = Workbooks.Add
    For r = 1 To 8
             With NewBook
                Set ShNew = .Sheets.Add
                With ShNew
                    .Name = CompInfo(r, 1)
                    .Range("A1").Value = CompInfo(r, 1)
                    .Range("A2").Value = CompInfo(r, 2)
                End With
            End With
    Next r
End Sub

But it isn't needed for this task.

Yes, I now see that it's redundant, but at least I know HOW to add it it within the WITH statement.

Thank you for your help.