VBA Macro - ok debug ko runtime-CodePudding

I've written a macro which essentially sums up numbers, grouping by year and by month based on our projection model. It runs fine when stepping through the code (with F8 from start to end), but it stops immediately in standard execution (the "play" button F5). The first trouble is, according to VBA, in the following line:

issmIndex = Range("A1:Z1").Find("ck.IssMon").Column  'issmIndex an integer

...which throws a Runtime error 91.

Originally I tried Application.WorksheetFunction.Match(...) but had the same problem: runs OK in debug, but KO in execute (Error 1004 instead). I considered it could have been an Excel version issue (the Match function has a different name in the Italian version) so I decided to switch to a more neutral Find, but still no luck.

Anyone has any idea please? How is it possible that debugging does not show bugs if there are any? Thanks!

CodePudding user response：

Welcome to SO. Sometimes Excel reads code faster than executing, so when reading a command there is a previous one not finished. IT's weird but it happens a lot if your code does a lot of stuff and calculus.

Besides, when debugging, every command line is executed before reading next one, so you cannot detect this just debugging.

So if your code runs perfect when debugged but errors if executed as normal, try to add the command DoEvents right before the problematic line. Something like this:

' your previous code
'
'
'
Doevents
issmIndex = Range("A1:Z1").Find("ck.IssMon").Column 'issmIndex an integer
'
'rest of your code

This commands forces Excel to make sure everything has been executed before reading. It's kind of like a checkpoint, something like make sure you've done everything before going to next line.

DoEvents function

CodePudding user response：

When you have an error with a line that is a combination of several commands, try breaking it down into the individual steps.

For example, this works:

Sub findDemo()
  Const toFind = "blah"
  Dim rg As Range, f As Range
  Set rg = Range("A2:C5")
  Set f = rg.Find(toFind)
  If f Is Nothing Then
    Stop 'not found
  Else
    Debug.Print "found in column #" & f.Column
  End If
End Sub

Also see the example in the documentation for Range.Find().

CodePudding user response：

first of all, big thanks to everybody for the swift answers. Based on what Nathan first suggested, I added the "sheets..." part and vba now gave an error 9 (not found), which made me suspect the issue was elsewhere. The code in fact started with a "ThisWorkbook.Activate", so I commented it and now it works! I am aware it is really simplistic as no error handling is coded, but I was misled by debugging. Will work better on the initial setting. Have a nice day!

---edit: "thisWorkbook" returns a Workbook object that represents the workbook where the current macro code is running. I saved the Macro in Personal.xlsb but data was elsewhere, so it is pretty obvious why it didn't work (though I expected another error code than 91)...what is not obvious now is why debugging didn't raise any objection!