RT Error '9': Subscript out of range when using Workbooks.activate and Sheets.activate

[faemike]

Hello,

I'm running into some behavior that I can't explain. I've created an xlsm file which I use to sort a separate workbook (workbook A) with, at first, only a single sheet (sheet A).

I've written the code so that it takes a text box input from a form in the xlsm file (a person's last name) and on a button_click event, it captures the value and stores it as a variable. It then calls Sub M with the captured value as a parameter. This sub then activates workbook A and then immediately activates sheet A. The first time that this sub is called, I have no issues. However, on every subsequent call of this sub, I get an RT Error '9'. Interestingly, if I click "debug" and then "continue"(F5), the script completes as expected so it's not a syntax-related issue though I wonder if I'm using these calls properly...

Workbooks("Workbook.xls").Activate
Sheets("Sheet").Activate

I was originally activating the workbook in the button_click sub of the macro file and then reactivating it in Sub M but I've ruled that out as an issue by simply commenting out this workbook.activate.


Any ideas or recommendations on what to look at?

 

[igold]

Hi faemike,

Welcome to the MrExcel Forum.

Could it be a typo..

Sheets("Sheet").Activate

Sheets("Sheet1").Activate

 

[faemike]

Hello igold,

No, it's not a typo. Every time this error occurs, I click debug immediately followed by F5 (continue) and it finishes without an issue.

 

[MARK858]

So you actually have a sheet just called "Sheet"?

 

[faemike]

I'll ask the question like this.

I have a .xlsm file that contains only VBA modules that I execute on another workbook. Is there anything that I should be aware of with respect to activating separate workbooks and sheets and keeping those workbooks and sheets active throughout the execution of the module?

 

[igold]

I would make sure that you are using Option Explicit at the top of your code, it might help you chase down your error.

 

[faemike]

I ask because the bold line below threw a divide by 0 error when the workbook sheet has 1799 used rows.

Workbooks("workbook.xls").Sheets("sheet").Activate

RowCount = (Cells(Rows.Count, 1).End(xlUp).Row) - 1


Application.ScreenUpdating = False
    ProgressBar.Show
    
    With ProgressBar
            .ProgressJob.Caption = "Formatting"
            .FrameProgress.Caption = Format(CurrentRow / RowCount, "0%")
            .LabelProgress.Width = (CurrentRow / RowCount) * (.FrameProgress.Width)
    End With
    DoEvents

and yes, I've changed the name of the sheet and workbook. I don't have a workbook named "workbook.xls" or a sheet in said workbook named "sheet"

 

[faemike]

I would make sure that the sheets you are calling by name, actually exist.

it would stand to reason that if I can debug the VBA where it broke and immediately press F5 to successfully continue execution without a further error, that I'm neither misspelling or mis-referencing workbook or sheet names, wouldn't it?

 

[igold]

It would stand to reason that if you are throwing a subscript error that you are trying call something that does not exist. Are you using Option Explicit.

 

[faemike]

no I am not. however, its not a matter of the VBA running and producing errant results on every execution. The maddening aspect to this problem is the inability to reproduce this error, as well as some other errant behaviors, reliably to debug it. Just as an example, I can run this VBA module several times in a row and not have any issues followed by 3 or 4 executions that produce errant results such as the aforementioned divide by 0 and subscript errors.

I'm going to try and see if I can produce a sanitized version of the data spreadsheet im working with and i'll post it up along with the VBA module. maybe a second set of eyes will catch some rookie mistake im making.