Why does a With Statement not work?

[JeffGrant]

Hi All,

can somebody please tell me why this code does not work unless the sheet is selected or activated. Does that not negate the best practice to minimse Select and actvate Statement in my code?

If the sheet is not selected or activated, VBA throws Error 91 code.

Sub Timer2Transpose
   With Sheet62
        .Range("AA4").Select
        Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
   End With
End Sub

thanks in advance

 

[Cubist]

Try without the Selection. (Not tested).

Sub Timer2Transpose()
   With Sheet62
        .Range("AA4").ListObject.QueryTable.Refresh BackgroundQuery:=False
   End With
End Sub

 

[Micron]

IMO, best to declare and set the object, then it should not matter:

Dim sht As Worksheet
Set sht = Sheet62
   With sht
        .Range("AA4").ListObject.QueryTable.Refresh BackgroundQuery:=False
   End With

Is that code just for demo purposes? I ask because a With block having one reference doesn't make sense to me.

 

[Micron]

If the sheet is not selected or activated, VBA throws Error 91 code

But on what line? Happens for me because I don't have a sheet with that table.

 

[JeffGrant]

Hi Micron,

yes this code was for reference purposes. However, I have never used the Dim and Set statements in the subs where this happens. I will change that and see if it solves the issue.

 

[Micron]

Based on years of coding, but still being a rank amateur, IMO it's the best approach, the idea being to reduce ambiguity. Letting the app decide what you mean and applying a default of some kind does not make sense. I also agree with what I have read elsewhere: If you don't use Option Explicit in all your code modules, you deserve whatever grief that gives you. I don't understand why it is not the default setting as it seems to me that the times you'd ever not want that would be rare, if ever.

If you still have issues, id the line that raises the error.

 

[JeffGrant]

Hi Micron,

I don't understand why the aggression with "you deserve whatever grief that gives you"

At no point did I say that I don't use Option Explicit. In fact it is turned on and has always been turned on as the default.

What is did say is "I have never used the Dim and Set statements in the subs where this happens" because all my variables are declared at the top of the module.

Perhaps I should have been a little clearer and just said that I have not used, for eg, the Dim & Set statement as you have advised.

ie
Dim sht As Worksheet at the top of the module
Set sht = Sheet62 in the routine

 

[jolivanes]

I think that it is safe to say that if you want to select, whether it is needed or not, the sheet needs to be the active sheet. This also is true for items like shapes, pictures etc on that sheet.

 

[Micron]

I was making general comments about coding and quoting someone else in the process. It hand nothing to do with anyone in particular, so no aggression intended.