Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Public Const & RowSource | Excel Programming | |||
Concatenate a Const Name in Sub Routine | Excel Programming | |||
Const Array | Excel Programming | |||
Not recognizing a Const | Excel Programming | |||
crash changing const to public const | Excel Programming |