VBA problem in Excel 97

VBA problem in Excel 97

Didn't find your answer?

Can anybody tell me why this code will not run in XL97? It falls over at the myarray declaration. (Run-time error 438. Object does not support this property or method). The code runs happily in XL2003.

It appears not to like the use of Sheet code names. With some tweaking I can get it to run with the Sheet names but, since I can not guarantee these will not change, I would prefer to use Sheet code names. I have not been able to find anything that indicates that Excel 97 handles Sheet code names differently to Excel 2003.

Unfortunately the client is using various versions of Excel otherwise I would stop the file opening in Excel 97.

Public Sub RemoveFilter()
Dim myarray As Variant
Dim n As Variant
Dim sh As Worksheet

myarray = Array(Sheet2, Sheet5, Sheet6, Sheet7, Sheet8, Sheet12)
For Each n In myarray
Set sh = n
sh.Unprotect
If sh.FilterMode Then
    sh.ShowAllData
  End If

Next n

End Sub

Any help gratefully received.
 

Replies (4)

Please login or register to join the discussion.

avatar
By ACDWebb
17th Sep 2009 10:18

Will this work?

Public Sub RemoveFilter()
Dim myarray As String
Dim n As Variant
Dim sh As Worksheet

myarray = Array(Sheet2.Name, Sheet5.Name, Sheet6.Name, Sheet7.Name, Sheet8.Name, Sheet12.Name)
For Each n In myarray
Set sh = Worksheets(n)
sh.Unprotect
If sh.FilterMode Then
    sh.ShowAllData
  End If

Next n

End Sub

Thanks (0)
avatar
By paulwakefield1
17th Sep 2009 10:38

Thank you

That was quick! Many thanks - works fine. One of those "Simple when you are showed how" moments.

Thanks (0)
avatar
By RichardSchollar
17th Sep 2009 12:16

Somewhat surprised...
That the suggested code worked since it defined myarray as a string and yet assigned a variant array (of strings) to the myarray variable (which would thus result in a type mismatch). I presume that you kept your declaration statement of

Dim myarray As Variant

myarray = Array(Sheet2.Name, Sheet3.Name, Sheet4.Name) 'etc

Richard

Thanks (0)
avatar
By paulwakefield1
17th Sep 2009 12:31

Quite correct

The flash answer is I spotted the mismatch and retained the original declaration.

 

The true answer is I hadn't spoted that you had changed the declaration and left the original unchanged. :-)

Thanks (0)