CODEWITHSUNDEEP
vbaexcel
cRIMSOn ffs
cRIMSOn ffs

Reputation: 77

Why is sheet being included in VBA when i've asked for it not to be

Does anyone know why not all sheets are being excluded as defined in my code below? It seems that sheet 'Asset' is still being included. Many thanks

Sub Run_Me_To_Fix_Columns()
   Dim ws As Worksheet

'------------------------------------------------------------------
'List the names of the worksheets to exclude from Sub resizingColumns
'------------------------------------------------------------------

    Const excludeSheets As String = "Control,DIVA_Report,Asset"

'------------------------------------------------------------------

    For Each ws In ActiveWorkbook.Worksheets
        If IsError(Application.Match(ws.Name, Split(excludeSheets, ","))) Then
        Call resizingColumns(ws)
        End If
    Next
End Sub

Sub resizingColumns(ws As Worksheet)
       With ws
        ws.Range("A:AZ").ColumnWidth = 10
    End With
    For i = 1 To 24
        Numbers = WorksheetFunction.Count(ws.Columns(i))
        Text = WorksheetFunction.CountA(ws.Columns(i)) - Numbers
        If Numbers < Text Then
            ws.Columns(i).EntireColumn.AutoFit
        End If
    Next i
End Sub

Upvotes: 1

Views: 65

Answers (1)

paxdiablo
paxdiablo

Reputation: 882446

It's always a good idea to have some basic debugging skills in your toolchest. For example, you could insert code like the following before your test:

For Each s In Split(excludeSheets, ",")
    MsgBox "EX: [" & s & "]: " & CStr(len(s))
Next

For Each ws In ActiveWorkbook.Worksheets
    MsgBox "WS: [" & ws.Name & "]: " & CStr(len(ws.Name))
Next

For Each ws In ActiveWorkbook.Worksheets
    MsgBox "MATCH: " & ws.Name & ": " & CStr(Application.Match(ws.Name, Split(excludeSheets, ",")))
    MsgBox "ISERR: " & ws.Name & ": " & CStr(IsError(Application.Match(ws.Name, Split(excludeSheets, ","))))
Next

This will show you the values you're checking (with lengths to ensure they don't have unexpected white-space before or after, something that's bitten me before with sheet names).

Further on that, I'll actually walk you through a decent debugging session so you can gain some skills in that area. First, create a new workbook with the sheets "Sheet1" through "Sheet5", and enter the following code:

Sub test()
    Dim ws As Worksheet

    Const excludeSheets As String = "Sheet2,Sheet3,Sheet5"
    
    For Each s In Split(excludeSheets, ",")
        MsgBox "EX: [" & s & "]: " & CStr(Len(s))
    Next

    For Each ws In ActiveWorkbook.Worksheets
        MsgBox "WS: [" & ws.Name & "]: " & CStr(Len(ws.Name))
    Next

    For Each ws In ActiveWorkbook.Worksheets
        MsgBox "MATCH: [" & ws.Name & "]: " & CStr(Application.Match(ws.Name, Split(excludeSheets, ",")))
        MsgBox "ISERR: [" & ws.Name & "]: " & CStr(IsError(Application.Match(ws.Name, Split(excludeSheets, ","))))
    Next

    For Each ws In ActiveWorkbook.Worksheets
        If IsError(Application.Match(ws.Name, Split(excludeSheets, ","))) Then
            MsgBox "GOT: [" & ws.Name & "]"
        End If
    Next
End Sub

When you run that, you'll see the following message boxes:

EX: [Sheet2]: 6
EX: [Sheet3]: 6
EX: [Sheet5]: 6

WS: [Sheet1]: 6
WS: [Sheet2]: 6
WS: [Sheet3]: 6
WS: [Sheet4]: 6
WS: [Sheet5]: 6

MATCH: [Sheet1]: Error 2042
ISERR: [Sheet1]: True

MATCH: [Sheet2]: 1
ISERR: [Sheet2]: False

MATCH: [Sheet3]: 2
ISERR: [Sheet3]: False

MATCH: [Sheet4]: 2
ISERR: [Sheet4]: False

MATCH: [Sheet5]: 3
ISERR: [Sheet5]: False

GOT: [Sheet1]

You can see from that output that the only sheet output was Sheet1 despite the fact that Sheet4 is not in the exclude list. And, from the MATCH lines, both Sheet3 and Sheet4 appear to have been found in position 2 of the exclude list.

So that immediately tells you where the problem lies, Excel appears to be doing something wrong. But, in fact, it's doing exactly what you told it to do. Going to the documentation for the Match function, we see this little snippet:

expression.Match (Lookup_value, Lookup_array, Match_type)

  • If Match_type is 1, Match finds the largest value that is less than or equal to Lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

  • If Match_type is 0, Match finds the first value that is exactly equal to Lookup_value. Lookup_array can be in any order.

  • If Match_type is -1, Match finds the smallest value that is greater than or equal to Lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

  • If Match_type is omitted, it is assumed to be 1.

Note that last clause. Because you're choosing the default match type of "largest less than or equal to", Sheet1 causes an error because there is no entry less than or equal to it in the exclude list.

However, for Sheet4, there is a match. Sheet2 and Sheet3 are both less than or equal to Sheet4, and Sheet3 is the largest of them, hence why it's "found" in position 2.

You may well find even stranger results in your situation since your worksheets my not be sorted alphabetically.

The solution then is to specify an exact match rather than using the default (the 0 parameter added to the Match call):

If IsError(Application.Match(ws.Name, Split(excludeSheets, ","), 0)) Then

Upvotes: 2

Related Questions