| Implementing coderget="_blank" href="vbe.htm">VBE
(Visual Basic Editor). This is an environment, very similar to the
programming interface in Visual basic and .NET. The VBE can be opened by
the key combination Alt+F11 or Tools>Macros>Visual basic editor.
Excel stores code in three places
-
Standard Modules
-
Object Class Modules
-
Class Modules
Standard Modules
are the area for 'standard' code. This is the most popular type of code
and is exactly what is generated if you record code using the macro
recorder. (Tools>Macros>Record New Macro...) In the case of the macro
recorder, it will automatically add modules to the
project, with
sequential number, e.g. module1, module2, etc. If you are in doubt where
your code should go, you should try here first. Anything placed in
here, unless it is marked in the code as 'private' will appear in the
macro dialog box (Tools>Macro>Macros...).
You can add standard modules to
your project by either (in the VBE):
- Right-clicking on any object underneath the
project you
are working on and selecting Insert>Module. (NOT Class
Module).
- Selecting your
project or
any object below it and select Insert>Module (NOT Class
Module).
These modules start by being sequentially numbered, e.g. Module1,
Module2, but can be renamed by changing the name under the 'Name'
property in the property explorer
while the relevant module is selected.
Any code you have is placed in the main window of the VBE once you
have created or opened a module by double-clicking on it.
There is no limit to the amount of procedures/functions a module can
hold, but, like file structure, it helps to add modules in large
projects to segment the functions that the code performs.
Making code available at all times
(personal.xls)
To enable standard code to operate it has to be in a module in an
open workbook. The best waMaking code available at all times
(personal.xls)
To enable standard code to operate it has to be in a module in an
open workbook. The best way to achieve this is by using your
personal.xls workbook. This is a hidden workbook, which as the name
suggests is set up 'per user', that opens each time Excel does. To
ascertain if you already have one set up, go to Window>Unhide... in
Excel. If this is 'dimmed' then you have not got one. To
overcome this open an ordinary workbook and save it as personal.xls in a
special folder that was created at set-up called
XLStart. This will normally reside in the same folder that Excel installed to although
in some network installations and Windows XP versions it will be in the
user's 'applications' folder. (e.g. C:\Documents and Settings\Nick
Hodge\Application Data\Microsoft\Excel\XLSTART). Once you have done
this, go to Window>Hide and this workbook can now become a repository
for any code you want available at all times.
Code specific to a particular workbook
In this instance, code can be saved in a module(s) within the
workbook itself. Whilst this code can access any open workbook or
automation object (Other applications such as Word, etc), it cannot be
accessed from another piece of code when this workbook is closed. During
code building you may find yourself referring often to the
'ActiveWorkbook' property of the Workbook object. In the case of
code that refers often to the workbook containing the code, as in this
section, you can instead use the 'ThisWorkbook' property to refer always
to the workbook containing the code. This is very useful if your
code switches between workbooks often as you never need to know which is
the currently active workbook to refer to this 'base' workbook. The
'ThisWorkbook' property is also very useful when building 'add-ins' for
Excel.
Object Class Modules (Event
Code) are special code modules, (seen as built-in
class modules), that are placed behind many
objects in Excel to provide the ability to react to 'events' triggered
when something happen to that object. For example, every worksheet is
monitoring every change to every cell on it! When a change is made to
any cell, the Worksheet_Change() event is fired allowing you to run
code, such as validation each time this happens. Many objects have this
ability including, (not limited to), Workbooks, Worksheets, UserForms,
ActiveX controls (list boxes, command buttons), Charts, etc. Currently
the Excel application itself does not have events, although these can be
established by 'rolling your own' in Class
Modules and selecting 'View Code' or select Insert>Code (F7)
from the menu.
Or from within the Excel user-interface you can:
- Right-click on a sheet tab and select 'View code' to
access that worksheet's code module.
- Right click on the small Excel workbook icon in the top
left of the workbook window, (NOT the application window,
which is the 'overall' window), and select 'View Code' to access the
Workbook code module.
Once you have
opened a code window for a particular object, you can select the
object from the 'object' drop-down in the main code window. (left
drop-down just above main code window) and then view the available
'events' for that object in the 'procedures' drop-down, (just to the
right of the 'object' drop-down).
Within object class modules you can refer to the object itself by
using the 'Me' keyword, so for example, in Sheet1 code module the
following are the same:
Worksheets("Sheet1").Range("A1").Value="Nick"
Me.Range("A1").Value="Nick"
Again this way you can always ensure you are referring to the
correct object no matter what else has gone on since in the GUI (Graphic
User Interface).
Class Modules are
provided to create your own objects in Excel and along with that your
own events, properties, etc. for those objects. Once you have created
the new object type, say an 'Application' type, this will appear in the
'object' drop-down of the VBE and any events you have created will
appear in the 'procedures' drop-down. In this way for example, you
can 'manufacture' events for the application object which are not
currently supplied as 'standard'.
Class modules are added in exactly the same way as 'Standard
Modules' except you will select 'Class Module'. (NOT
'Module'). |
