Const and VBA values

[sgl]

Hi All,

I have the following DefinedNames conlOFFSET_COLS = 4 and conlINPUT_COLS =
6. I want to assign these two constants to my VBA code so that I can use
their values in my procedure. I have the following code ...

Dim lOFFSET_COLS As Long
Dim lINPUT_COLS As Long

lOFFSET_COLS = ActiveSheet.Names("conlOFFSET_COLS").Value
lINPUT_COLS = ActiveSheet.Names("conlINPUT_COLS").Value

When the code is executed I get a TypeMismatch error. I have tried various
ways of extracting the values but can't seem to get it right.

Does anyone have any ideas

Many thanks/sgl

 

[Per Jessen]

Hi

lOFFSET_COLS = ActiveSheet.Range("conlOFFSET_COLS").Value
lINPUT_COLS = ActiveSheet.Range("conlINPUT_COLS").Value

Regards,
Per

 

[Jason]

The Names property is a string. You are receiving an error because you are
trying to assign a string to a Long variable type. You can probably correct
the error by multiplying your "ActiveSheet.Names("conlOFFSET_COLS").Value" by
the number 1 giving you:

lOFFSET_COLS = ActiveSheet.Names("conlOFFSET_COLS").Value*1
lINPUT_COLS = ActiveSheet.Names("conlINPUT_COLS").Value*1

 

[Rob Bovey]

One way to coerce defined name constants into values for your VBA code is
the following:

lOFFSET_COLS = Evaluate(ActiveSheet.Names("conlOFFSET_COLS").Value)
lINPUT_COLS = Evaluate(ActiveSheet.Names("conlINPUT_COLS").Value)

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[sgl]

Thank you all for your prompt replies.
Robs suggestion worked perfectly and have used this in my application.
Rob Thank you very much for your assistance.
Many thanks/sgl