Const and VBA values
 

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

Const and VBA values
 

Hi

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

Regards,
Per

"sgl" skrev i meddelelsen
...
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

Const and VBA values
 

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

"sgl" wrote:

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

Const and VBA values
 

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

lOFFSET_COLS = Evaluate(ActiveSheet.Names("conlOFFSET_COLS").Valu e)
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" wrote in message
...
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

Const and VBA values
 

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

"Rob Bovey" wrote:

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

lOFFSET_COLS = Evaluate(ActiveSheet.Names("conlOFFSET_COLS").Valu e)
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" wrote in message
...
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