Excelling with Excel #3 – Macros

Macros. To many, this term is shrouded in mystery. Between the 'hard problem' of consciousness and talking cats, does this world really need another unsolved mystery? I don't think so. For this reason, I, Greg the orange cat, have chosen macros as my third Excel tip.

Tip#3: Macros

According to Wikipedia, Macros are 'mapped out processes that instantiate a specified output sequence'. Interesting, Mr. Wikipedia, but your definition of "macro" is not specific enough for me. I will instead discuss "macros" in the context of Microsoft Excel.

Here's an overview of what is covered in this posting:

Note that in addition to covering macro creation and use, I also cover rudimentary VBA. This topic is either simple (recording, using macros) or surprisingly involved (using VBA to write macros). It all depends upon on how deep you wish to go.

 

 

Create/Use a Macro

 

Record a Macro

First, start recording the macro. When you're done recording your macro, click the blue square. Everything that you have done in between the start and end of the recording has been recorded as a macro. This recorded macro can now be run.

 

Run a Macro

First, view all macros that have been created.

Then, select the desired macro and click "Run".

Whatever has been recorded (or written) into the selected macro will be done.

 

Write a Macro

Writing macros involves the use of VBA. I discuss VBA in greater detail further below, but, before I do, let me quickly show you how to open the Visual Basic Editor:

Note: There is (at least) one other way to open the Visual Basic Editor in Excel 2007 beside pressing Alt+F11, but this process is outside the scope of this posting.

Once you have the Visual Basic Editor open, insert a new code module.

 

VBA: Automating Spreadsheets Since 1997

VBA (Visual Basic Applications) is a programming language. In a nutshell, VBA comes down to writing subroutines (equivalent to macros) and functions. Subroutines (macros) and functions are each unique in their own way:

• Subroutines (macros) are able to do stuff, but cannot return values
• Functions are able to return values, but cannot do stuff

 

VBA Subroutine and Function General Formats

The subroutine (macro) or function begins with the Declaration , and the end is marked by the End Declaration . In between, we have the Body in which calculations / manipulations are performed (usually involving any indicated Input Parameters).

 

VBA Subroutines (macros)

Here is an example subroutine (macro):

Note here that this subroutine (macro) does something as opposed to returning a value; cell B1 is assigned the value of 15.

VBA Functions

Here is an example function:

Note here that this function returns a value as opposed to doing something; a value of 15 is returned to cell B1.

Writing VBA Code (A primer on programming in general)

As I stated before: VBA comes down to simply writing subroutines (macros) and functions. Knowing how to write them is the tricky part. Covering VBA in one blog entry would be intense (not possible), but I want to give you a good global foundation of VBA so you can learn it on your own. Main Ideas:

• Use of variables
• General flow of code
• Flow-of-code modifiers
• Specific snippets of code that are useful

 

Use of Variables

Quite a few types of variables exist, here are some of them:

• Integer - Full Numbers - 4, 0, and -12
• Double - Decimal Numbers - 3.1416, 0, and -2.1
• Boolean - Binary - TRUE or FALSE
• Strings - Word-like - "meow", "", " ", or "43453"

Below is an example of how variables are created and then assigned values:

k

General Flow of Code

Remember, subroutines (macros) and functions begin with a Declaration and end with an End Declaration. In between those two points, we have the Body in which all of the calculations/actions are performed. Below is a visual example of the flow of code in the 'variable' example above.

 

Flow-Of-Code Modifiers

The flow of code can be modified by certain statements. Here are two:

For .. Next

 

If .. Else

 

Specific VBA Examples

Macros that run automatically upon user action:

Let's say you want to have a macro run every time a cell in (only) the column "F" is clicked. Do the following: 1) Go into the Visual Basic Editor (Alt+F11) 2) Open a sheet's code 3) From the left-most drop-down select "Worksheet" 4) From the right-most drop-down select "SelectionChange". 5) Type the code shown further below. Once you've done the above procedure, clicking any cell in column "F" will result in the message shown below.

 

Selective removal of characters from cells:

Let's say you have a HUGE list of data that (for some odd reason) is littered with unneeded commas. You can use a macro to automate this comma-removal process.

 

 

Opening a program:

 

Retrieve user input:

 

Other Resources

Plenty of good Macro/VBA sources exist. Here are a few:

Good VBA code sources:

http://www.cpearson.com/Excel/Topic.aspx (professional-level code) http://www.ozgrid.com/ (a good forum) http://www.google.com/ (best of all - simply perform a search and include "VBA")

ExcelFunction.net:

This site has a wealth of knowledge. How to set up your Excel workbook to allow macros About the Visual Basic Editor Recording a macro VBA-specific links: Code presentation (what VBA code looks like) Data types, variables, constants Arrays (pretty advanced) Functions and subroutines Conditional Statements (like "If.. Else") Loops (like the "For loop") Operators and built-in functions Excel Objects (pretty advanced) Events (macros that run when a user does something) Errors and error-trapping (advanced)

 

Summary

Because writing macros / using VBA is essentially programming, VBA's usefulness is limited only by one's programming expertise and creativity (short of walking on water). Learning VBA, however, is not a task, but rather a process - it takes time. Next time you have a task you think you can automate, spend a little time (if appropriate) to automate it. This initial investment of time up front will pay dividends in the future.

If you have any questions about the content of this post, please leave a comment or contact my young professional ChE friend, Todd Krueger.

- Greg the Orange Cat