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

does know why not sheets being excluded defined in code below? seems sheet 'asset' still being included. many thanks

sub run_me_to_fix_columns()    dim ws worksheet  '------------------------------------------------------------------ 'list names of worksheets exclude sub resizingcolumns '------------------------------------------------------------------      const excludesheets string = "control,diva_report,asset"  '------------------------------------------------------------------      each ws in activeworkbook.worksheets         if iserror(application.match(ws.name, split(excludesheets, ",")))         call resizingcolumns(ws)         end if     next end sub  sub resizingcolumns(ws worksheet)        ws         ws.range("a:az").columnwidth = 10     end     = 1 24         numbers = worksheetfunction.count(ws.columns(i))         text = worksheetfunction.counta(ws.columns(i)) - numbers         if numbers < text             ws.columns(i).entirecolumn.autofit         end if     next end sub 

it's idea have basic debugging skills in toolchest. example, insert code following before test:

for each s in split(excludesheets, ",")     msgbox "ex: [" & s & "]: " & cstr(len(s)) next  each ws in activeworkbook.worksheets     msgbox "ws: [" & ws.name & "]: " & cstr(len(ws.name)) next  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 show values you're checking (with lengths ensure don't have unexpected white-space before or after, that's bitten me before sheet names).

---

further on that, i'll walk through decent debugging session can gain skills in area. first, create new workbook sheets "sheet1" through "sheet5", , enter following code:

sub test()     dim ws worksheet      const excludesheets string = "sheet2,sheet3,sheet5"      each s in split(excludesheets, ",")         msgbox "ex: [" & s & "]: " & cstr(len(s))     next      each ws in activeworkbook.worksheets         msgbox "ws: [" & ws.name & "]: " & cstr(len(ws.name))     next      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      each ws in activeworkbook.worksheets         if iserror(application.match(ws.name, split(excludesheets, ",")))             msgbox "got: [" & ws.name & "]"         end if     next end sub 

when run that, you'll see 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 output sheet output sheet1 despite fact sheet4 not in exclude list. and, match lines, both sheet3 , sheet4 appear have been found in position 2 of exclude list.

so tells problem lies, excel appears doing wrong. but, in fact, it's doing told do. going documentation match function, see little snippet:

expression.match (lookup_value, lookup_array, match_type)

• if match_type 1, match finds largest value less or equal lookup_value. lookup_array must placed in ascending order: ...-2, -1, 0, 1, 2, ..., a-z, false, true.

• if match_type 0, match finds first value equal lookup_value. lookup_array can in order.

• if match_type -1, match finds smallest value greater or equal lookup_value. lookup_array must placed in descending order: true, false, z-a, ...2, 1, 0, -1, -2, ..., , on.

• if match_type omitted, assumed 1.

note last clause. because you're choosing default match type of "largest less or equal to", sheet1 causes error because there no entry less or equal in exclude list.

however, sheet4, there is match. sheet2 , sheet3 both less or equal sheet4, , sheet3 largest of them, hence why it's "found" in position 2.

you may find stranger results in situation since worksheets not sorted alphabetically.

the solution specify exact match rather using default (the 0 parameter added match call):

if iserror(application.match(ws.name, split(excludesheets, ","), 0))