Results 1 to 13 of 13

Thread: [RESOLVED] "Reset" a With/End With structure?

  1. #1

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    Resolved [RESOLVED] "Reset" a With/End With structure?

    I don't think it's possible to do, but I have code that has this structure.

    Code:
    Set ws = Activesheet
    With ws
        For lngRow = 2 to 1000
            If lngRow = 100
                'Code to create a new worksheet which by default activates the new sheet
                'blah
                'blah
               Set ws = Activesheet ' The new sheet
               ' Is it possible here to have the With refer to the new sheet because as is the With
               ' still refers to the original sheet
            End If
            .Cells(lngRow, "A") = .Cells(lngRow, "A")
            End If
        Next
    End With

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: "Reset" a With/End With structure?

    That's interesting. I always thought that With was purely a design-time construct and that the actual code that was executed would be the same as using the variable specified in the With statement everywhere it was omitted within the block. I don't use VB6 or VBA but I just tested VB.NET and I see the same behaviour you describe. I ran this code:
    vb.net Code:
    1. Module Module1
    2.  
    3.     Sub Main()
    4.         Dim t1 As New Thing
    5.         Dim t2 As New Thing
    6.  
    7.         t1.Name = "First Thing"
    8.         t2.Name = "Second Thing"
    9.  
    10.         Console.WriteLine(t1.Name)
    11.         Console.WriteLine(t2.Name)
    12.  
    13.         Dim tX As Thing
    14.  
    15.         tX = t1
    16.  
    17.         With tX
    18.             Console.WriteLine(.Name)
    19.  
    20.             tX = t2
    21.  
    22.             Console.WriteLine(.Name)
    23.  
    24.             tX = t1
    25.  
    26.             Console.WriteLine(.Name)
    27.         End With
    28.  
    29.         Console.ReadLine()
    30.     End Sub
    31.  
    32. End Module
    33.  
    34. Class Thing
    35.     Public Property Name As String
    36. End Class
    and I got this output:
    First Thing
    Second Thing
    First Thing
    First Thing
    First Thing
    Had my assumption (and your desire) been true then that would have been:
    First Thing
    Second Thing
    First Thing
    Second Thing
    First Thing
    It appears that a With statement actually generates a new variable altogether and, presumably, one that is inaccessible to user code. This could probably be confirmed by examining the IL generated by compiling the VB.NET above, but I don't think that that conformation is even needed. VB.NET is a different language but many features of the VB syntax have been implemented to work in the same way as in other flavours of VB so they presumably explicitly chose to maintain this behaviour of With. The upshot is that, while I can't say with 100% certainty, I am 99% sure that you're correct that it's not possible.

    Actually, I just rested after commenting out the first line that sets the value of tX and I got this message:
    System.NullReferenceException: 'Object reference not set to an instance of an object.'

    $W0 was Nothing.
    That $W0 is presumably the variable implicitly created by the With statement. Again, VB6 and VBA may work slightly differently under the hood but VB.NET would have been implemented to produce exactly the same behaviour for the sake of faithfully upgrading existing VB6 code, so they likely do similar, if not exactly the same.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: "Reset" a With/End With structure?

    OK, I'm more confused now because I changed Thing in my code to be a structure rather than a class and I now see the behaviour that you want and I originally expected. As VB6 and VBA don't have the same concept of classes and structures as VB.NET, I don't know what that means for those other languages.

  4. #4

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    Re: "Reset" a With/End With structure?

    You can see similar behavior in loops. For example the output of this code
    Code:
    Sub test()
    Dim lngLastRow As Long
    Dim lngRow As Long
    Dim ctr As Integer
    
    lngLastRow = 10
    
    For lngRow = 1 To lngLastRow
        ctr = ctr + 1
        If lngRow = 5 Then
            lngLastRow = 25
        End If
        Debug.Print ctr
    Next
    End Sub
    is

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: "Reset" a With/End With structure?

    For lngRow = 1 To lngLastRow
    the for line is only executed once on entering the loop, so changing lnglastrow will have no effect during the loop, whereas changing lngrow wold affect the output, if it is changed to a value > lnglastrow the loop would exit, else if the new value is reduced extra outputs would occur
    Code:
    lnglastrow = 10
    For lngrow = 1 To lnglastrow
        ctr = ctr + 1
        If lngrow = 5 Then
            lngrow = 3
        End If
        Debug.Print ctr
    Next
    give an endless loop

    I always thought that With was purely a design-time construct and that the actual code that was executed would be the same as using the variable specified in the With statement everywhere it was omitted within the block.
    as far as i am aware the with object is retained in memory as a pointer for the duration of the with block where as using the specified object variable would have to be reread for each line, making the with block more efficient, someone may well correct me on this
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

  7. #7
    Sinecure devotee
    Join Date
    Aug 2013
    Location
    Southern Tier NY
    Posts
    6,582

    Re: [RESOLVED] "Reset" a With/End With structure?

    Quote Originally Posted by MartinLiss View Post
    I found a workaround.
    Please, don't tell us.
    "Anyone can do any amount of work, provided it isn't the work he is supposed to be doing at that moment" Robert Benchley, 1930

  8. #8

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    Re: [RESOLVED] "Reset" a With/End With structure?

    I guess I deserve that

    I assume (and hope) you can tell that the code that I originally posted is not my actual code but just a simplified example. In any case when the equivalent of lngRow reaching 100 happens I called a new, modified, version of the code that has its own With/End With structure and exited the original code.

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: [RESOLVED] "Reset" a With/End With structure?

    If your workaround does the job then fine, but there's also the option of not using With at all. If you were using any language other than a flavour of VB then that's what you'd have to do anyway.

  10. #10

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    Re: [RESOLVED] "Reset" a With/End With structure?

    Understood and that's the first thing I thought of but there's a lot of code in my actual sub that refers to the same worksheet and I believe that using the ws object is a lot more efficient.

  11. #11
    Sinecure devotee
    Join Date
    Aug 2013
    Location
    Southern Tier NY
    Posts
    6,582

    Re: [RESOLVED] "Reset" a With/End With structure?

    Quote Originally Posted by MartinLiss View Post
    Understood and that's the first thing I thought of but there's a lot of code in my actual sub that refers to the same worksheet and I believe that using the ws object is a lot more efficient.
    I think we agree about using "ws" instead of ActiveSheet as ws is much shorter to type, and all the references in the code would thus be shorter, i.e. ws.cell, etc.
    Since the With clause is unique to VB (as far as I know), not using it and using a short reference, i.e. a "pointer" or really an alias for the name of a longer reference to an object in a block of code is not a rare thing to do. It would also be more efficient than calling a method, and passing the object to it, and having the aliasing done with the parameter name, just so you could use a With clause in the method.
    "Anyone can do any amount of work, provided it isn't the work he is supposed to be doing at that moment" Robert Benchley, 1930

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [RESOLVED] "Reset" a With/End With structure?

    you could also use a second with block within the first

    Code:
    Set ws = ActiveSheet
    With ws
        ' stuff
        Set newsht = Sheets.Add
        With newsht
            .Name = "my new sheet"
            ' other stuff 
        End With
        ' more stuff or not
    End With
    alternatively if there is no further code to run on the previous active sheet you could end the first with and start a second
    Code:
    Set ws = ActiveSheet
    With ws
        ' stuff
        Set ws = Sheets.Add
        Debug.Print .Name
        ' other stuff as required
    End With
    With ws
        .Name = "xx3"
        Debug.Print .Name
        ' more stuff
    End With
    Sheet5
    xx3
    Last edited by westconn1; Aug 8th, 2020 at 08:21 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    Re: [RESOLVED] "Reset" a With/End With structure?

    Thanks but I don't think I can do either of those things because of my For lngRow/Next loop. As I said I have a workaround that I don't want to change, but I'll actually test your suggestions when I have time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width