Excel Macro Runs With Breakpoint but Fails Without It

[Domer82]

I have an Excel macro that opens a workbook and activates the first worksheet, then searches for a specific string in the first row. If I run the macro without any breakpoints, it fails to find the string but if I set a breakpoint just before the search, it works perfectly. It's as if the worksheet activation isn't completely finished before the search is run, but setting a breakpoint gives it time to finish. I've tried the kludgy approach of adding a 3-second wait after activating the worksheet, but that didn't help.
The workbook being opened contains a pivot table, which may be related. I've read that they are sometimes slow to open. If I open a different worksheet without a pivot table, it works fine.
Here is an excerpt of the code that is failing. If I set a breakpoint at the line where the search is done (ColNum = ColWith...), it works. Without the breakpoint there, it fails. What can I do to resolve this?

Dim ColNum       As Integer
Dim FileType     As Integer
Dim ImportWB     As Workbook
Dim ImportWS     As Worksheet

Set ImportWB = Workbooks.Open(FullFileName)
Set ImportWS = ImportWB.Sheets(1)
ImportWS.Activate
' <<< Tried waiting 3 seconds here but it did not work

ColNum = ColWith("Commissions", "1:1", xlPart) ' Function searches specified range for specified text
If ColNum > 0 Then
    FileType = 0
Else
    ColNum = ColWith("Residual", "1:1", xlPart) ' <<< This text should be found in the file!
    If ColNum > 0 Then
        FileType = 1
    Else
        MsgBox "Could not find either 'Residual' or 'Commissions' in row 1 of the imported file - Exiting"
    End If
End If

 

[Jeffrey Mahoney]

Add DoEvents before the search and see what happens

 

[Domer82]

Add DoEvents before the search and see what happens

I forgot to mention that I had already tried that... it did not help.

 

[Domer82]

It turns out that the problem was due to the fact that the Excel file containing the pivot table has some sort of hidden worksheet as worksheet #1. It had a strange name and could not be unhidden, as far as I can tell. I don't know if it is related to the pivot table or because the originators had saved this as an "xlsb" file originally.

In any case, I was looking for the text in "ImportWB.Sheets(1)" thinking it was the first visible worksheet, but was in fact this hidden worksheet.

So it had nothing to do with the search function itself. I worked around this by looking for the worksheet name instead of the number.