VBA Code Location Question - Module vs. Worksheet Level Code

Matanuska · Feb 24th, 2002, 07:15 AM

Can anyone offer any general guidelines regarding code placement (in modules vs. sheets vs. workbooks. vs. class modules)?

Is vba code stored in a sheet object limited to handling events, buttons, dropdowns, etc?

If I define a custom function in a sheet object I can see it back in Excel in the Insert Function list but it won't work, even when I include the full path.

Works fine when stored in a regular VBA module.

(FWIW - I wanted to put the function in the worksheet object so the code would go along with the worksheet when it was pulled out of a workbook. I've since learned in this forum how to export code from a module & then re-import it into the new workbook, but the other way sure would have been easier.)

Anne Troy · Feb 24th, 2002, 11:11 AM

Check out Mr. Pierson's information at:

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

Hope it helps!

Matanuska · Feb 24th, 2002, 07:48 PM

Just what I needed!

Thanks

Anne Troy · Feb 24th, 2002, 07:54 PM

Great!

Export and Import?
Cut and paste works well when you're in the same workbook. (Delete any empty modules.) If you're *sharing* your code with someone else--that's when I export. Then send them the *.bas file.

Matanuska · Feb 24th, 2002, 08:01 PM

Export & import because I'm cloning a set of worksheets programatically from hidden template sheets to create a new workbook when a button is clicked & some code modules need to go along with the sheets.

Feb 24th, 2002, 11:11 AM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	Check out Mr. Pierson's information at: http://www.cpearson.com/excel/codemods.htm Hope it helps! __________________ ~Anne Troy

Feb 24th, 2002, 07:54 PM	#4
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	Great! Export and Import? Cut and paste works well when you're in the same workbook. (Delete any empty modules.) If you're sharing your code with someone else--that's when I export. Then send them the *.bas file. __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 24th, 2002, 07:15 AM	#1
Matanuska New Member Join Date: Feb 2002 Location: Houston, Texas Posts: 28	Can anyone offer any general guidelines regarding code placement (in modules vs. sheets vs. workbooks. vs. class modules)? Is vba code stored in a sheet object limited to handling events, buttons, dropdowns, etc? If I define a custom function in a sheet object I can see it back in Excel in the Insert Function list but it won't work, even when I include the full path. Works fine when stored in a regular VBA module. (FWIW - I wanted to put the function in the worksheet object so the code would go along with the worksheet when it was pulled out of a workbook. I've since learned in this forum how to export code from a module & then re-import it into the new workbook, but the other way sure would have been easier.)

Feb 24th, 2002, 07:48 PM	#3
Matanuska New Member Join Date: Feb 2002 Location: Houston, Texas Posts: 28	Just what I needed! Thanks

Feb 24th, 2002, 08:01 PM	#5
Matanuska New Member Join Date: Feb 2002 Location: Houston, Texas Posts: 28	Export & import because I'm cloning a set of worksheets programatically from hidden template sheets to create a new workbook when a button is clicked & some code modules need to go along with the sheets.