|Office Tutorials & Software Solutions|
|Specializing in Advanced Spreadsheet & Office Macro Solutions.
|Create YOUR OWN Microsoft® Office Macros|
|Application||Microsoft® Office® General||Version||1.00|
|Tested on versions:||2003 (11.0) - should work on any Windows version from 2000|
Macros are software applications that run inside other applications, like Office packages. In fact, many commercial software's now include some form of end-user programming to allow for customization and automations of common tasks. Microsoft's VBA (Visual Basic for Applications) is the macro language bundled into Office and currently licensed to many other software makers.
They are very useful to extend the usefulness and productivity of the Office applications. As an example, in this website you can find, among others, Macros that build calendar plans using Excel and no extra software.
Macros exist for all current Office applications, but only the most recent versions of Outlook (from 2000) support them (previous version have a different programming model). This article will use EXCEL for illustrations purposes, but in general its concepts will apply to all Microsoft® Office Applications.
Setting Macro Security
Macros are potentially dangerous. Since they are software and run by the PC user, they can do almost anything on the computer they're running, including erasing the hard drive and other forms of damage. And since they are imbedded in office applications, they can be mailed as regular office files. In fact, one of the first virus to use email to spread was the Melissa Virus, a Word Macro! Of course, back in 1999, email virus were just starting and nobody expected them, so email was considered safe and no defenses were in place.
IMPORTANT NOTE: If you have trouble running macros when following this article, please refer to the article above and adjust your security settings before proceeding.
Automation and Coded Macros
There are basically two types of macros, even if the system itself won’t make a difference between them. The difference is, really, in the usefulness and extent of the macro:
For an example of a Coded macro, that is too complex to be generated by recording an example, click here to download the demo of our Calendar Plan Generator macro.
This article is about generating Automation macros, the ones that
ANY user can create, even if they don’t write a single line of
Generating your own automation Macros
To generate a Macro, first you need an operation to be performed. As an example, lets generate a macro that:
To record a macro that performs this operation, you only need to do it yourself while having the Macro Recorder activated (to record your operations):
You now have a macro that will apply the border to ANY cell selection.
Running the Macro
In case you haven’t performed the steps above, just use our ready to use demo:
To run the macro, you have several options:
Editing the Macro Code
If you know VBA (or Visual Basic, VBScript or any other related language) you may want to manually edit the macro. Or you may just be curious about what was exactly recorded by the macro. To open the macro editor, press ALT+F11 on your keyboard. You will enter the Macro Editor:
PIC 6: Macro (VBA) Editor (Click to Enlarge).
Recorder Macros usually reside on Module1, on the Modules section. One useful manual addition to your code (that cannot be done with the recorder) is adding a message box to indicate the macro has finished. Add a line to the end of your macro code (before the “End Sub”) with
Where nnnnn is the name of your macro. The general format for this command is:
Check the DEMO for an example. Click here to download the DEMO
You may experiment with changing the code. To avoid destroying it, you may save the original macro, create a copy and edit the copy. If you end up with an unusable macro, you may just delete the copy and make a new one from the original macro.
Saving the Macros for future use
Macros are usually saved with the file where they were recorded. This mean they will also be available on that file, regardless of the computer used to open it. So, if you email the file to a friend, the macro will be there.
To save the macro with the file, just save the file with the Save command. It will be saved with your data and all other file contents (Charts, formats, etc).
This option may not be available for all Microsoft Office applications, but is available in Excel. You may save your macro as a AddIn that its loaded with Excel every time it starts. This way, if the macro is generic and can be useful in many files, it will always be available regardless of the file currently being edited.
The drawback is that in this format, the macro behaved as an Excel extension on that computer and, therefore, will not be available on other computers (unless specifically installed on them) and will not travel with the data files (if you email a file to a friend, the macro will not be there for him because it is installed on your PC, not the data file).
To save it as an AddIn, do this:
From now on, your addin will load with Excel and the macro will be available every time you run Excel, but only on the PC where it has been saved.
To install it on other computers, just mail them the original Excel macro file and repeat the steps above on each one.
Try the FREE Demo
In case you haven’t performed the steps above, you may still experiment the usefulness of macros. Just use our ready to use demo:
Can I know more about this subject ?
Check our FREE PC Tips. They will be regularly published. Better still, subscribe our Email Newsletter. You will be informed when contents updates are made and automatically granted participation on any future promotion.
(c) 2004 - 2021 OfficeHelp.Biz