-
Aug 5th, 2020, 02:41 PM
#1
[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
-
Aug 5th, 2020, 10:27 PM
#2
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:
Module Module1
Sub Main()
Dim t1 As New Thing
Dim t2 As New Thing
t1.Name = "First Thing"
t2.Name = "Second Thing"
Console.WriteLine(t1.Name)
Console.WriteLine(t2.Name)
Dim tX As Thing
tX = t1
With tX
Console.WriteLine(.Name)
tX = t2
Console.WriteLine(.Name)
tX = t1
Console.WriteLine(.Name)
End With
Console.ReadLine()
End Sub
End Module
Class Thing
Public Property Name As String
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.
-
Aug 5th, 2020, 10:33 PM
#3
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.
-
Aug 5th, 2020, 11:05 PM
#4
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
-
Aug 6th, 2020, 06:11 AM
#5
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
-
Aug 6th, 2020, 03:15 PM
#6
Re: [RESOLVED] "Reset" a With/End With structure?
-
Aug 6th, 2020, 03:30 PM
#7
Re: [RESOLVED] "Reset" a With/End With structure?
Originally Posted by MartinLiss
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
-
Aug 6th, 2020, 04:08 PM
#8
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.
-
Aug 6th, 2020, 08:02 PM
#9
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.
-
Aug 6th, 2020, 08:13 PM
#10
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.
-
Aug 7th, 2020, 02:18 AM
#11
Re: [RESOLVED] "Reset" a With/End With structure?
Originally Posted by MartinLiss
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
-
Aug 8th, 2020, 08:14 AM
#12
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
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
-
Aug 8th, 2020, 11:08 AM
#13
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|