really basic problems

T

themangostays

hi,

Call me dumb, but I've written a simple excel VBA macro that pulls the
values of various cells from various sheets on a simple invoicing
package i'm working on. One of the sheets is a reference sheet
containing client and job specific data. This sheet is then referenced
against the financial data sheet.
Why oh why can't I declair public variables that can be transfered
between forms/sheets.
I've tried everything. Can one of you clever folk tell me:

1. where should I be storing these procedures. In individual modules,
behind sheets or behind forms

2. how do I get the variables to be passed between the above

3. Where should i be declairing these variables. The're (yes you
guessed it) simple. Just cash values and strings.

Please help

Cheers

[email protected]
 
B

Bob Phillips

hi,

Call me dumb, but I've written a simple excel VBA macro that pulls the
values of various cells from various sheets on a simple invoicing
package i'm working on. One of the sheets is a reference sheet
containing client and job specific data. This sheet is then referenced
against the financial data sheet.
Why oh why can't I declair public variables that can be transfered
between forms/sheets.
I've tried everything. Can one of you clever folk tell me:

1. where should I be storing these procedures. In individual modules,
behind sheets or behind forms
Click to expand...

If being used in multiple modules, store them in a standard code module.
2. how do I get the variables to be passed between the above
Click to expand...

Just reference them b y variable name, by being public in a standard code
module, they will be accessible.
3. Where should i be declairing these variables. The're (yes you
guessed it) simple. Just cash values and strings.
Click to expand...

At the start of the module, outside of any procedure.
 
N

Norman Jones

Hi TheMangoStays,
1. where should I be storing these procedures. In individual modules,
behind sheets or behind forms
Click to expand...

Worksheet event code should be placed in the worksheet's sheet module;
Userform code should bbe placed in the userformn's code module. Otherwise
procedures would normally resise in a standard workbook module.

For a comprehensive treatment of this issue, see Chip Pearson at:

http://www.cpearson.com/excel/codemods.htm

2. how do I get the variables to be passed between the above
3. Where should i be declairing these variables. The're (yes you
guessed it) simple. Just cash values and strings.
Click to expand...


Try declaring these variables as Public (global) variables at the top of a
standard module. Set the variable value in one sub and read the value in
another sub. Note that, having declared these variables at module level, the
should not be declared in the sub procedure code.

You may additionally find Chip Pearson's comments on variable declaration
instructive:

http://www.cpearson.com/excel/variables.htm
 
Top