 Back to jkp-ads.com |
Ron de Bruin
|
 |
|
Back to jkp-ads.com |
Ron de Bruin
|
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
When you want to try VBA code that you see in a Forum, Newsgroup or
WebSite and you read:
Paste the code in a General, Regular,
Normal or Standard module
Paste the code in a Sheet
module
Paste the code in the ThisWorkbook module
And you have no idea where to paste the code then I hope the screenshots
below will help you. If you are on a Mac see also the links on the bottom of
this page. There are four types of modules and there are
screenshots for the first two on this page.
ThisWorkbook and
worksheet code modules
You find them in the Microsoft Excel
Objects folder and you use them to store event macros
General, Regular, Normal or Standard code modules
Here you store
normal macros, User Defined Functions(UDFs) and global variables.
You can
have more then one module in your project
UserForm modules
Here you store the event code for the Userform
Class modules
See this page for more information
http://cpearson.com/excel/AppEvent.aspx
Open the VBA editor with the shortcut Alt+F11.
You see that you can use Ctrl+r to open the Project
Explorer if you not see it.
The Project Explorer is like Windows Explorer
and you can see all your open workbooks
and add-ins there.
Normal the active project (workbook) is expand so you can see all the
modules in it.
But if you see this press the * on the numeric keyboard with the project
selected to expand your project.
You can also click on the + or double
click the project name.
Tip: Use Option Explicit in every module
If used,
the Option Explicit statement must appear before any other code.
When
you use Option Explicit you must explicitly declare all variables.
If
you attempt to use an undeclared variable name, an error occurs at compile
time.
It avoid incorrectly typing the name of an existing variable
and avoid confusion
in code where the scope of the variable is not
clear.
If you not declare your variable it will be a Variant, Variants
are slow, they take up a lot of memory,
and using them when not
absolutely necessary can create hard-to-find bugs in your code
To add
Option Explicit automatic in every new module or every module in a new file
use this
Tools>Options in the menu bar and check "Require
Variable Declaration".
You must insert a module first in your project before you can use it for
code.
Click on Insert>Module with your project active in
the Menu bar to do this.
Select the module in the Project Explorer and press F7
or double click on the module.
You can see above the menu bar if you have
the code window of the correct module active.
The module is directly
active after you insert it in your project for the first time.
Paste
the code in the code window on the right hand side below Option
Explicit.
After you paste the code in the code window use
Alt+q to go back to Excel.
In Excel you can use
Alt+F8 then to select the macro and run it.
Select the sheet in
the Project Explorer and press F7 or double click on the
sheet module.
You can see above the menu bar if you have the code window
of the correct module active.
Another way to go to a sheet module is
to right click on a sheet tab and choose "View Code"
You not have to open
the VBA editor first when you use this.
Paste the code in the code
window on the right hand side below Option Explicit.
After you paste the code in the code window use Alt+q to go
back to Excel.
Select
ThisWorkbook in the Project Explorer and press F7 or double
click on the ThisWorkbook module.
You can see above the menu bar if you
have the code window of the correct module active.
Paste the code in
the code window on the right hand side below Option Explicit.
After you paste the code in the code window use Alt+q to go
back to Excel.
Note: if you are on a Mac you will find out that the VBE editor is not as good as the one in the Windows versions of Office. Below are two links with more information.
How to Code with the VBE in Office 2011 for Mac: http://www.dummies.com/how-to/content/how-to-code-with-the-vbe-in-office-2011-for-mac.html
Display and Navigate the VBE Interface in Office 2011 for Mac: http://www.dummies.com/how-to/content/display-and-navigate-the-vbe-interface-in-office-2.html