VB Errors When Running Macro in Excel

[u/Cudaprine]

Hello,

I hope this is the right place for this. I'm supporting a user who relies on a macro-enabled Excel spreadsheet with multiple worksheets, but the key ones are:

• Cost Items
• Variables
• Results Summary

The workflow:

1. Enter data into the Cost Items sheet (e.g., Name: Test1, Cost: 1000; Name: test2, Cost: 5000).
2. Add data to the Variables sheet under "Brisk Parameters" – P10: -5%, P90: 15%.
3. Go to the Results Summary sheet.

At that point, I get a pop up message that states the data has changed.

I click OK, it churns for a moment, and then I get:

Microsoft Visual Basic - "Run-time error '9': Subscript out of range"

If I click Debug, it highlights this line in yellow:

"If VarExists(Count) And VarProb(I) = 1 Then"

What I've tried so far:

• Searched online and found suggestions it might be an overflow error of some kind.
• Tested on multiple systems (managed company device, unmanaged device, different PCs).
• Confirmed Trust Center settings allow macros.
• Installed and registered the Brisk server (was told this was required – still no change).

The kicker:
This exact file was working perfectly fine just a few weeks ago. No known changes were made to the file or the environment.

I've been banging my head against this for a couple of days now and am starting to lose my sanity.

Does anyone have any idea what direction I should go in to troubleshoot why this spreadsheet suddenly stopped functioning?

Any help would be greatly appreciated.

TIA!

[u/euben_hadd]

I would take this line >> "If VarExists(Count) And VarProb(I) = 1 Then" and separate the "if" so you can tell which variable is causing the problem.

"If VarExists(Count) = 1 Then

If VarProb(I) = 1 Then"

'stuff goes here...

End If

End If"

At least isolate which one is causing the problem. Also check that the variables are properly assigned. Maybe use "> 0" instead of "= 1"? Try using "true" or "false"? Try "!= 0"?

Just guessing without seeing the code. But something is getting placed in one of those fields that is not an integer, or is beyonf the integer limit. Check the input data.

[u/jd31068]

If VarExists and/or VarProb is an array then you should use UBound to ensure that count or I doesn't exceed the number of items in the array(s) as doing so will give you this error.

as an example

Dim VarProb(3) as string
Dim I as Integer

VarProb(0) = "A"  (arrays in VBA are zero based so the first index is 0)
VarProb(1) = "B"
VarProb(2) = "C"
VarProb(3) = "D"

I = 4
Debug.Print VarProb(I)   <--- will cause the error subscript (the number in the paranthesis) out of range

' check the array for the max index and print if I is a valid index
If Ubound(VarProb) >= I then Debug.Print VarProb(I) else MsgBox "No such VarProb at array index " & Cstr(I) 

put a breakpoint on the If statement and use a watch to view the value of count and I to see which is the culprit or use debug.print to write count and I values to the immediate window, and look at them when the code stops of the If statement.

He is a short guide on debugging VBA in Excel https://www.geeksforgeeks.org/excel/debugging-vba-code-in-excel/

[u/Western_End_2223]

Are "VarExists" and "VarProb" functions or arrays?  If arrays, the values in "Count" or "I" may be exceeding the declared array sizes.  You said that the spreadsheet was working before.  Perhaps enough data has been added that the arrays need higher upper limits.

[u/3583-bytes-free]

If you haven't fixed it I'll have a look for you - put the file on WeTransfer and send me the link in chat.

Assuming it doesn't contain any sensitive info of course (and isn't breaking any sub rules)