Tip#3: MacrosAccording 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 MacroFirst, 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.
First, view all macros that have been created.
Then, select the desired macro and click "Run".
Write a MacroWriting 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 1997VBA (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 FormatsThe 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): subroutine (macro) does something as opposed to returning a value; cell B1 is assigned the value of 15.
VBA FunctionsHere 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"