Use of Variable

[Guest]

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.

 

[Otto Moehrbach]

Use:
Public vcol1 as Integer

 

[Guest]

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

 

[Guest]

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.

 

[Chip Pearson]

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)

 

[Guest]

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...

 

[Bob Phillips]

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)