Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of Variable
If in a standard module I have an interger variable and I wish to be able to
use this variable later in a sheet_change event, what exactly would I do? Above the Standard module I put: Global vcol1 as Interger But I'm not sure what else I am to do in order to have it later to use in my Sheet_Change event code. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of Variable
Use:
Public vcol1 as Integer "Jim May" wrote in message ... If in a standard module I have an interger variable and I wish to be able to use this variable later in a sheet_change event, what exactly would I do? Above the Standard module I put: Global vcol1 as Interger But I'm not sure what else I am to do in order to have it later to use in my Sheet_Change event code. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of Variable
I replaced Global vcol1 as Integer
with Public vcol1 as Integer but as soon as I run my macro in my Standard Module and I go to the immediate Window and enter ? vcol1 and <return I get 0, versus say 9 "Otto Moehrbach" wrote: Use: Public vcol1 as Integer "Jim May" wrote in message ... If in a standard module I have an interger variable and I wish to be able to use this variable later in a sheet_change event, what exactly would I do? Above the Standard module I put: Global vcol1 as Interger But I'm not sure what else I am to do in order to have it later to use in my Sheet_Change event code. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of Variable
You need to make vcol1 equal to something.
If it is to be a column index number then something like: vcol1 = 3 ' this would be Colummn C and you could use it like: Cells(2, vcol1).Copy Cells(5, 15) 'copy from Range("C2") to Range("O5") If you want it to equal a date value then: vcol1 = Jan ' to use it you would have to be sure that the data type is compatible with the place it is applied. The Dim statement only declares the data type. It does not assign the value to the variable. You must use the equal sign and in cases where you are defining an object variable, also use The Set command to assign a value to the variable. Those variables that do not have the Set command can have their values changed later in the code by simply making them equal to something else. But don't try changing them within the same loop, that won't work. "Jim May" wrote: If in a standard module I have an interger variable and I wish to be able to use this variable later in a sheet_change event, what exactly would I do? Above the Standard module I put: Global vcol1 as Interger But I'm not sure what else I am to do in order to have it later to use in my Sheet_Change event code. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of Variable
You can't create a purely Public variant in an object module, such as
ThisWorkbook, a Class, a Sheet, or a UserForm code module. Truly Public variables must reside in a standard module. Public XYZ As Long If you declared it in an object module, you'd have to reference it using the module or instance name. E.g., in a UserForm, Public ABC As Long Now in a standard code module: ABC = 1234 ' doesn't work Userform1.ABC = 1234 ' works fine -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Jim May" wrote in message ... I replaced Global vcol1 as Integer with Public vcol1 as Integer but as soon as I run my macro in my Standard Module and I go to the immediate Window and enter ? vcol1 and <return I get 0, versus say 9 "Otto Moehrbach" wrote: Use: Public vcol1 as Integer "Jim May" wrote in message ... If in a standard module I have an interger variable and I wish to be able to use this variable later in a sheet_change event, what exactly would I do? Above the Standard module I put: Global vcol1 as Interger But I'm not sure what else I am to do in order to have it later to use in my Sheet_Change event code. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of Variable
vcol1 is created and assigned the integer 9 in my standard module -- I place
in the declaration section of the same standard module Public vcol1 as Integer Surely I can use this in a sheet_change event 5 seconds later... "Chip Pearson" wrote: You can't create a purely Public variant in an object module, such as ThisWorkbook, a Class, a Sheet, or a UserForm code module. Truly Public variables must reside in a standard module. Public XYZ As Long If you declared it in an object module, you'd have to reference it using the module or instance name. E.g., in a UserForm, Public ABC As Long Now in a standard code module: ABC = 1234 ' doesn't work Userform1.ABC = 1234 ' works fine -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Jim May" wrote in message ... I replaced Global vcol1 as Integer with Public vcol1 as Integer but as soon as I run my macro in my Standard Module and I go to the immediate Window and enter ? vcol1 and <return I get 0, versus say 9 "Otto Moehrbach" wrote: Use: Public vcol1 as Integer "Jim May" wrote in message ... If in a standard module I have an interger variable and I wish to be able to use this variable later in a sheet_change event, what exactly would I do? Above the Standard module I put: Global vcol1 as Interger But I'm not sure what else I am to do in order to have it later to use in my Sheet_Change event code. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of Variable
Yes you can, so it looks like the initialising code is failing.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim May" wrote in message ... vcol1 is created and assigned the integer 9 in my standard module -- I place in the declaration section of the same standard module Public vcol1 as Integer Surely I can use this in a sheet_change event 5 seconds later... "Chip Pearson" wrote: You can't create a purely Public variant in an object module, such as ThisWorkbook, a Class, a Sheet, or a UserForm code module. Truly Public variables must reside in a standard module. Public XYZ As Long If you declared it in an object module, you'd have to reference it using the module or instance name. E.g., in a UserForm, Public ABC As Long Now in a standard code module: ABC = 1234 ' doesn't work Userform1.ABC = 1234 ' works fine -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Jim May" wrote in message ... I replaced Global vcol1 as Integer with Public vcol1 as Integer but as soon as I run my macro in my Standard Module and I go to the immediate Window and enter ? vcol1 and <return I get 0, versus say 9 "Otto Moehrbach" wrote: Use: Public vcol1 as Integer "Jim May" wrote in message ... If in a standard module I have an interger variable and I wish to be able to use this variable later in a sheet_change event, what exactly would I do? Above the Standard module I put: Global vcol1 as Interger But I'm not sure what else I am to do in order to have it later to use in my Sheet_Change event code. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-Time error '91': Object variable of With block variable not set | Excel Programming | |||
Sum cells based on a row variable and seperate column variable | Excel Worksheet Functions | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming |