View Code question

[Doug Waters 03/03/08]

Please excuse an inexperienced but motivated newbie's question:

In Excel, when you right-click the tab for a given worksheet in a workbook,
and then select View Code from the resultant popup menu, what is the name of
the type of code that goes/resides in the Code window that then appears in
Visual Basic Editor (since it apparently is not a macro)? I need to know
what this type of code is called so I can research it when and how to use it.

Thanks for any help that anyone can provide.

Doug

 

[Tim879]

typically you would put event procedures in the worksheet level and
"macros" in the modules.

the difference is the code placed in these sheets is usually for
worksheet level events (i.e. when a cell is changed, selected, etc.)
vs. code placed in modules which could be for anything.

For example, if you wanted to trigger a macro when a user entered a
value in a given cell, you would put the code to trigger the macro in
the worksheet.

this site has a great explanation of event procedures with some
examples.
http://www.cpearson.com/excel/Events.aspx

 

[StumpedAgain]

Visual Basic for Applications (VBA)

Check out this book for getting started using VBA:
http://www.dummies.com/WileyCDA/Dum...ramming-For-Dummies.productCd-0764574124.html

 

[Doug Waters 03/03/08]

So code in the View Code window IS a macro but just doesn't reside in a
module? And macros in worksheets and modules both reside in the workbook
file?

 

[StumpedAgain]

So code in the View Code window IS a macro but just doesn't reside in a
module?

Yes, the code that you see in the View Code window is a macro that is
usually specific to that worksheet.

And macros in worksheets and modules both reside in the workbook file?

Everything is stored in the same file. In the "Project Explorer"
(View->Project Explorer) you can browse code by workbook or by module.

 

[Chip Pearson]

So code in the View Code window IS a macro but just doesn't reside in a

module? And macros in worksheets and modules both reside in the workbook
file?

The term 'macro' is both overly generic and obsolete. It is better to refer
to the code as 'procedures' and differentiate between the types of
procedures. VBA procedures come in 4 flavors: Subs, Functions, Properties,
and Events. Subs and Functions are for general usage, differing only in the
facet that a Function can return a value to its caller while a Sub cannot.
Typically, Subs and Functions reside in regular Modules. Properties can
exist in regular modules or object modules (an object module is a Class
module, the code module behind a UserForm, one of the Sheet modules, or the
ThisWorkbook module). Properties come in three types: Get, Let, and Set.
Events are procedures that are called automatically by Excel when the user
(or some code) takes an action. For example, the Change event occurs when
the value of a cell is changed. There are many built-in events for
worksheets, the workbook and the application, and you can create your own
events (only in Object modules) with the Event and RaiseEvent statements.

All functional VBA code must be within procedures, and procedures must be
within one or more modules, and modules must be within a workbook (xls,
xlsm, xla, xlam). A module may contain any (reasonable) number of procedures
and a workbook may contain any (reasonable) number of modules. Typically,
the Sheet modules and the ThisWorkbook module contain only code for the
Events that are raised by those objects, although strictly speaking they may
contain any code.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Gord Dibben]

The code could be considered as a macro.

A macro can be run in a number of ways.

Sub Foo()
code to do something
End Sub

Can be run from a button or shortcut key and is generally stored in a standard
module.

Private Sub Worksheet_Change(ByVal Target As Range)
set the triggering event
make the change
code to do something or Call Foo which does its thing
End Sub

Is run automatically when a change is made.....thus it is called "event code"

All macros live in workbooks or add-ins(which are workbooks)

Browse through Chip's site for much more.


Gord Dibben MS Excel MVP