Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 31st, 2009, 03:42 PM
Registered User
 
Join Date: Mar 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Module vs Object

Good Day:

I am new to Excel. Hope someone may answer me, what is the different to have my Excel VBA codes in "Module" vs "The Workbook" which both can be found in the Project Window located on the top left corner by default.

Please reply with the easiest code, exp: Msgbox "Hi"

Thanks!

For me to start of VB in Excel, I use a very simple code:

Quote:
Sub x()
Msgbox "Hi
End Sub
The question is WHERE TO PLACE THIS CODE?

In the Project Window (on the top left corner by default or you can use the Ctrl+R or View-->Project Explorer to see it), it comes with:
- Microsoft Excel Object which further divided in to Sheet 1 and This Workbook
- Modules

I tried to place the mentioned on all the three Sheet 1, This Workbook and Modules, one by one; There is no different to the result. Technically I believe there must be some differences. I had searched from the web but don't see any related. Perhaps the question is too simple, everyone know?!

Last edited by [email protected]; April 1st, 2009 at 10:16 AM.. Reason: hoping the question is easier to understand
 
Old April 1st, 2009, 09:03 AM
Authorized User
 
Join Date: Nov 2007
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts
Default

[email protected],

Welcome to the PtoP board.

Quote:
what is the different to have my Excel VBA codes in "Module" vs "The Workbook"what is the different to have my Excel VBA codes in "Module" vs "The Workbook"
If you are learing (we all started somewhere), your standard macros for manipulating data in a worksheet would go in a "Module". Say, bold all the cells in column A, sort your data, use Data Filter AutoFilter to search for and copy inforamtion to another worksheet, massage imported data from an external source into a report for your manager, etc., etc.

You would use code in the "worksheet/workbook" "Microsoft Excel Objects" for Events such as:
Worksheet_Activate (when you click on a sheet tab)
Worksheet_Change (when you change a cell on a sheet)
Worksheet_SelectionChange (when you select a cell in the sheet)

Workbook_Open (do something when the workbook opens)
Workbook_BeforeClose (say, hide a sheet)


I hope the following will give you some insight to the world of Events, and macros:

Events And Event Procedures In VBAEvents And Event Procedures In VBA
http://www.cpearson.com/excel/Events.aspx


VBA for Excel Lesson 9: Starting a Macro in Excel (events)
http://www.excel-vba.com/vba-prog-1-...cel-events.htm

Event Macros, Worksheet Events and Workbook Events
http://www.mvps.org/dmcritchie/excel/event.htm

Excel VBA - Excel Macros
http://www.ozgrid.com/VBA/


And, try these sites (good examples of code):
http://www.mrexcel.com/
http://www.excelforum.com/index.php
http://www.ozgrid.com/
http://www.vbaexpress.com/portal.php


To really learn, as your skills increase, try answering posts at the above sites.


Have a great day,
Stan
__________________
stanleydgromjr

Windows 8.1, Excel 2007.

Last edited by stanleydgromjr; April 1st, 2009 at 09:07 AM..
 
Old April 1st, 2009, 10:15 AM
Registered User
 
Join Date: Mar 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Many thanks Stan for replying the question, it is truly appreciated but anyhow, let me rephrase my question.

For me to start of VB in Excel, I use a very simple code:

Quote:
Sub x()
Msgbox "Hi
End Sub
The question is WHERE TO PLACE THIS CODE?

In the Project Window (on the top left corner by default or you can use the Ctrl+R or View-->Project Explorer to see it), it comes with:
- Microsoft Excel Object which further divided in to Sheet 1 and This Workbook
- Modules

I tried to place the mentioned on all the three Sheet 1, This Workbook and Modules, one by one; There is no different to the result. Technically I believe there must be some differences. I had searched from the web but don't see any related. Perhaps the question is too simple, everyone know?!

Appreciate you or anyone may help. Thanks
 
Old April 1st, 2009, 03:27 PM
Authorized User
 
Join Date: Nov 2007
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts
Default

[email protected],

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).[/b]

Code:
 
Sub x()
Msgbox "Hi
End Sub

To run the macro from within the workbook, any worksheet, click on "Tools", "Macro", "Macros..."

When the "Macro" box opens,
The box to the right of "Macros in:", from the drop down box, pick "This Workbook".
Then click on the macro name in the box above this area. And, then click on the "Run" button.


Have a great day,
Stan
__________________
stanleydgromjr

Windows 8.1, Excel 2007.
 
Old April 1st, 2009, 11:38 PM
Registered User
 
Join Date: Mar 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes Stan, many thanks for the reply. All those you mentioned I have got no problem.

You mentioned: Copy the below code, and paste it into the Module (on the right pane). Let's take a minute to pause here, on top of this Module, it comes with The Workbook, Worksheets where I too can place my codes there.

The question is what is the different placing the codes in the Module vs The Workbook vs Worksheet? Not how to run the codes or how to write codes.

Thanks.





Similar Threads
Thread Thread Starter Forum Replies Last Post
module umeshtheone Beginning VB 6 2 May 17th, 2007 05:48 AM
module umeshtheone VB Databases Basics 1 May 14th, 2007 01:41 PM
new module FT BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 2 March 15th, 2007 06:06 AM
module? flora8 Access VBA 2 September 7th, 2006 11:10 AM
Module sohrabus ASP.NET 2.0 Professional 1 September 1st, 2006 12:26 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.