really basic problems

[themangostays@gmail.com]

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

themangostays@gmail.com

[Bob Phillips]

<themangostays@gmail.com> wrote in message
news:1139907541.128741.154330@g47g2000cwa.googlegroups.com...
> 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

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

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.

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

[Norman Jones]

Hi TheMangoStays,

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

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.


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


---
Regards,
Norman


<themangostays@gmail.com> wrote in message
news:1139907541.128741.154330@g47g2000cwa.googlegroups.com...
> 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
>
> themangostays@gmail.com
>