With/End With statement without the dot

mark91345

Board Regular
Joined
Feb 11, 2011
Messages
113
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
A
B
6
CompanyManager
7
JellyfishCamila Wilson
8
DasringGreg Earnest
9
FightrrWalter Jackson
10
KryptisMargaret Rivera
11
PerinoEddie Chen
12
TwistrrRachel Crosby
13
HackrrAnthony Welch
14
PesNorma Ashley

<tbody>
</tbody>
Sheet: Activity

<tbody>
</tbody>
 
Last edited:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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

Code:
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
 
Last edited:
Upvote 0
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
Code:
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
 
Last edited:
Upvote 0
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).



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
A
B
6
CompanyManager
7
JellyfishCamila Wilson
8
DasringGreg Earnest
9
FightrrWalter Jackson
10
KryptisMargaret Rivera
11
PerinoEddie Chen
12
TwistrrRachel Crosby
13
HackrrAnthony Welch
14
PesNorma Ashley
Sheet: Activity
 
Upvote 0
In what way doesn't your code work? It works for me.
 
Upvote 0
You could use the ShNew variable like this
Code:
    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.
 
Upvote 0
You could use the ShNew variable like this
Code:
    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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top