VBA Basics - Where does the code live?

[Robin]

Hello,

I'm designing a workbook containing about 10 worksheets. Some of the
worksheets have Controls with VBA coding associated with them. I decided to
move some of the controls to different worksheets in the workbook and the
Code did not follow. For example, original code on the VBA window:WKBK
NAME.xls design]-[Sheet1 (Code)]. After I moved the code and hit 'View
Code', an new BLANK VBA sheet WKBK NAME.xls design]-[Sheet2 (Code)] opened.

While I assume I can just cut and paste from 1 to 2, this made me realize
I'm light on some basics. Because the code manipulates data and worksheets
THROUGHOUT the workbook, can I not jsut save the code with the entire
workbook rather than on different different sheets?

How does that work? Again, this is demonstrative of my lack of knowledge of
the basics in Excel VBA. (I've been using Access VBA for years and am ok
with that.)

Thank you for any suggestions on where/how to store coding,
Robin

 

[Gord Dibben]

See Ron de Bruin's site for this.

http://www.rondebruin.nl/code.htm

All depends upon the type of code.

NOTE: assume you have several sheets with the same event code like
Worksheet_Change event.

You can place that code once only in Thisworkbook module as

Workbook_SheetChange event.

The same code will then be valid for any sheet.


Gord Dibben MS Excel MVP

 

[Robin]

Excellent! Just what I was looking for.
Thank you.

See Ron de Bruin's site for this.

http://www.rondebruin.nl/code.htm

All depends upon the type of code.

NOTE: assume you have several sheets with the same event code like
Worksheet_Change event.

You can place that code once only in Thisworkbook module as

Workbook_SheetChange event.

The same code will then be valid for any sheet.


Gord Dibben MS Excel MVP

Hello,

I'm designing a workbook containing about 10 worksheets. Some of the
worksheets have Controls with VBA coding associated with them. I decided to
move some of the controls to different worksheets in the workbook and the
Code did not follow. For example, original code on the VBA window:WKBK
NAME.xls design]-[Sheet1 (Code)]. After I moved the code and hit 'View
Code', an new BLANK VBA sheet WKBK NAME.xls design]-[Sheet2 (Code)] opened.

While I assume I can just cut and paste from 1 to 2, this made me realize
I'm light on some basics. Because the code manipulates data and worksheets
THROUGHOUT the workbook, can I not jsut save the code with the entire
workbook rather than on different different sheets?

How does that work? Again, this is demonstrative of my lack of knowledge of
the basics in Excel VBA. (I've been using Access VBA for years and am ok
with that.)

Thank you for any suggestions on where/how to store coding,
Robin

.

 

[tbone]

One thing I've found is that if the code in the worksheet tries to
access a range on another worksheet, it may not work (at least not
with Excel 2003). I'm not sure of the exact circumstances, but I found
that moving the code to a *module* allows the code to work.

Right-click on the Project window, and select Insert, then Module.
Move your code to that module, and call it from your worksheet code.
Declare your routines Public, e.g.

Public Function GetDataFromSheet(Where as String) as String

I've forgotten this twice (I know, shame on me), and it led me on a
wild goose chase because no errors pop up - it just doesn't work.

Now I try to practice good organization - if the code is more than a
small bit, or it references any other sheets, I move it to a module.
The other good thing about this is that now, my usual support stuff,
like saving and restoring screen updating and sheet protection,
automatic workbook backup, etc, is in a support module that I can just
copy to a new workbook and quickly get all those features enabled.

tbone

Excellent! Just what I was looking for.
Thank you.

See Ron de Bruin's site for this.

http://www.rondebruin.nl/code.htm

All depends upon the type of code.

NOTE: assume you have several sheets with the same event code like
Worksheet_Change event.

You can place that code once only in Thisworkbook module as

Workbook_SheetChange event.

The same code will then be valid for any sheet.


Gord Dibben MS Excel MVP

Hello,

I'm designing a workbook containing about 10 worksheets. Some of the
worksheets have Controls with VBA coding associated with them. I decided to
move some of the controls to different worksheets in the workbook and the
Code did not follow. For example, original code on the VBA window:WKBK
NAME.xls design]-[Sheet1 (Code)]. After I moved the code and hit 'View
Code', an new BLANK VBA sheet WKBK NAME.xls design]-[Sheet2 (Code)] opened.

While I assume I can just cut and paste from 1 to 2, this made me realize
I'm light on some basics. Because the code manipulates data and worksheets
THROUGHOUT the workbook, can I not jsut save the code with the entire
workbook rather than on different different sheets?

How does that work? Again, this is demonstrative of my lack of knowledge of
the basics in Excel VBA. (I've been using Access VBA for years and am ok
with that.)

Thank you for any suggestions on where/how to store coding,
Robin

.