"Subscript Out of Range" Errors For Ranges

[Guest]

I'm in the process of altering someone else's code. He's got a code module
that with code that refers to ranges by name. For instance,

Range("aggregate_loan_monthly_results").Clear

This code was originally called via a macro button on a worksheet. I've
added a User Form that's calling this code instead, and when it gets to that
line, it errors out with "Subscript out of range". It still works fine if I
use the button.

What's the deal?

 

[witek]

I'm in the process of altering someone else's code. He's got a code module
that with code that refers to ranges by name. For instance,

Range("aggregate_loan_monthly_results").Clear

This code was originally called via a macro button on a worksheet. I've
added a User Form that's calling this code instead, and when it gets to that
line, it errors out with "Subscript out of range". It still works fine if I
use the button.

What's the deal?

Range can be defined on a sheet, not global, so check if you have the
same sheets active when you run macro from button and from user form.

Go to Insert/Name/Define , find your range and check if you see sheet
name close to name or not. If there is a sheet name close to range name
it means that name is defined as local and is not visible when other
sheet is active.

 

[Tim Zych]

It could also be the workbook with the range is not active. That will
produce an error.

What is different about the environment when you click the button versus
when the userform is active and that line of code is executed?