VBA globals passed as variable

Question

I have a VBA project that was working. I think the only change was to move a variable from local to global. The String is as a parameter to a sub. I am now getting a 'ny idea why? "Database" is one of the sheets in the workbook...

It worked before, and I just changed from local to global (Public Const) variable declarations. Hovering over the variables shows sh="Database" and rng="C9:AK47" as expected. Also that sheet and range is found in the active workbook.

--- error: The 'Set' line below errors out as 'subscript not in range' --- question: Why? Sorry I'm not more clear, but it was working, and I just changed one thing...

''' globals
Option Explicit
Public Const gshDb As String = "Database"
Public Const grgDb As String = "C9:AK47"
''' call
breakFormulas True, gshDb, grgDb
''' sub
Sub breakFormulas(switch As Boolean, sh As String, rng As String)
Dim shRng As Range
Set shRng = Sheets(sh).Range(rng) ' yellow highlighted error - subscript out of range

Do you have multiple workbooks open? If so, qualify your sheet object with a workbook. This works for me in a Module — urdearboy
If they are global, why on earth would you need to pass them? — braX
urd - I don't. I did add 'ActiveWorkbook' but it didn't help. Same error. braX - I pass different globals depending on what I want. so sometimes it's breakformulas true gshDb,grgDb and sometimes it's breakformulas true,greDb,grgRng — Jason Torpy
ThisWorkbook.Sheets is more reliable if the code is housed in the book you are trying to update. If you just have one book open it's prob not your issue anyway. I am not able to reproduce the issue here - works fine for me — urdearboy
thanks for thisworkbook.sheets . I'll remember that. Check my answer below for the very stupid nonsense error I had. not sure if I should delete this altogether or leave it here for reference... — Jason Torpy

Jason Torpy Jason Torpy · Accepted Answer · 2020-04-09T21:57:07

This all works fine. I had a brain fart and the sheet name is "Dashboard" not "Database". The listed error told me 'check the sheet name' pretty clearly. And I did like 10 times, but I had to look 11 times to see it. I'm just dumb sometimes.

I'm leaving this posted here, but maybe it's best to delete. Code works just fine though, so maybe that will be helpful for someone, especially considering the troubleshooting listed in the OP.

VBA globals passed as variable

1 Answers