Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Basics - Where does the code live?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Basics - Where does the code live?
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 On Wed, 6 Jan 2010 15:57:02 -0800, Robin wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Basics - Where does the code live?
Excellent! Just what I was looking for.
Thank you. "Gord Dibben" wrote: 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 On Wed, 6 Jan 2010 15:57:02 -0800, Robin wrote: 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Basics - Where does the code live?
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 On Wed, 6 Jan 2010 17:02:01 -0800, Robin wrote: Excellent! Just what I was looking for. Thank you. "Gord Dibben" wrote: 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 On Wed, 6 Jan 2010 15:57:02 -0800, Robin wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PDF basics | Excel Programming | |||
Add Ins basics | Excel Programming | |||
basics? | New Users to Excel | |||
MsgBox basics | Excel Programming | |||
scrollbar control of a chart, live or not live | Excel Programming |