Assigning named workbook ranges to VBA variable (Excel 2007)

[mwmentor]

Hi:


I am having some trouble with assigning named workbook ranges to variables in VBA.


I have done the following:


1. Declared the VBA variables as Range variables

2. Used the Set function to load the variable content


Example:


Declare variable type

Dim rng_Lastdate, rng_HistMax, rng_HistMed, rng_HistMin As Range


Code to load variable content:

[pre]Set rng_Lastdate = ActiveWorkbook.Names("rng_US_LastDate")
Set rng_HistMin = ActiveWorkbook.Names("rng_US_HistMin")
Set rng_HistMed = ActiveWorkbook.Names("rng_US_HistMed")
Set rng_HistMax = ActiveWorkbook.Names("rng_US_HistMax")

[/pre]
The error that I receive is a Type Mismatch (run-time error 13)


I have tried various things including changing the variable type to string, but that simply moves the problem rather than solve it. So all the variables load except rng_HistMin.


If someone provide guidelines, that would be great, thank you.


Regards,

Michael

 

[vasim]

Try


Activeworkbook.names.range("your range")

 

[mwmentor]

Hi there:


Thank you for your advise. It seems that that option is not supported in my version of Excel - 2007. I am also contemplating another way to do this as well.


Regards,

Michael

 

[mwmentor]

Hi:


I have changed the variables to string and that seems to work fine - strange because all the variables worked fine except for one. Anyway, I am not sure if this is really a fix, but it works.


Regards,

Michael.

 

[SirJB7]

Hi, mwmentor!


Tried this?

Set rng_Lastdate = Worksheets("XXX").Range("rng_US_LastDate")


Regards!


PS: Give a look to any of my uploaded files as example. Check "constants" and "declarations" at top and then "start" code sections.

 

[mwmentor]

Hi SirJB7:


Yep, that will work for assigning values from the worksheet. And then it is a short step to obtaining the address. So that's cool too.


Thank you.


Regards,

Michael.

 

[Hui]

Also the line

Dim rng_Lastdate, rng_HistMax, rng_HistMed, rng_HistMin As Range


Dimensions rng_Lastdate, rng_HistMax, rng_HistMed as variants

only rng_HistMin is Dimed as a Range


In VB as opposed to VBA they would all be a Range type

 

[mwmentor]

Thanks all - I appreciate your assistance


Regards,

Michael

 

[SirJB7]

Hi, mwmentor!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!