What is a macro?

Factual Questions
1

I have no problems with Excel but, as you can judge from my question, I don’t use it routinely and hence am not familiar with some of its capabilities. As such, I haven’t ever been in need of creating or using a macro. I just have the general idea that it is some sort of small script embedded in an Excel Worksheet that somehow facilitates your work by reducing the number of commands that you must punch in to achieve a desired result.

That is my vague, possibly incorrect idea. What I would like is for someone to clarify what exactly is a macro and to provide specific examples of when its use is advisable.

Anyone want to take a shot and help drag some ignorance out my brain? Thanks.

And on the 8th day God created beer

2

Until some one who knows a lot more than I do comes along, and I’m sure they will, I can tell you from my limited knowledge that a macro is a device wherein the computer automatically inserts words upon certain commands. It is a boilerplate general language format, but has variables, which the computer can generate. As far as Excel, I guess it is what enables Excel to do the computation.

That’s what I think, but I, too, will be interested in the correct answer.

3

** http://wombat.doc.ic.ac.uk/foldoc/foldoc.cgi?query=macro

4

A macro will be able to tell Excel what to do every time you enter new data into specified cell. For instance, you can prepare a template for your taxes. Instead or calculating them every year, you just enter different numbers and it will “add line 7 to line 10, then subtract line 5 and multiply it by lihe 28 from the workshheet on p.9”. Something like that. Or automatically multiply a number entered in cell K12 by your state tax. Etc.

5

Ah, macros! How I love them!

Actually, you can write macros to do just about anything you can possibly imagine in Excel. And now that Excel uses Visual Basic as its macro language, they are (fairly) easy to write and to work with.

I have written a lot of macros in Excel, usually to perform tasks that are either (1) too complicated for Excel to perform through one of its standard features or (2) too repetitive to do through the standard Excel features.

Probably the most common use for macros is to automate a series of steps into one macro that you can launch by hitting a certain key or clicking on a certain button. To do this, you simply start to record a macro, perform the commands you want, and then stop recording the macro. The commands you used are then saved and you can execute the same commands again by simply running the macro. For example, you can assign a certain key to automatically format a cell into your preferred font, size, color, etc.

On the other end of the macro spectrum, you can write a full Visual Basic procedure that will search for files, read and write to databases, create custom dialogue boxes that pop up based on what the user enters, format whole sheets or workbooks, and a whole bunch of other cool stuff.

One of the most useful kinds of macros in Excel are the ones that go through a large file and selectively pull out information. We get a lot of text files here at work that are output as printed reports. Excel can’t open them as a database because the columns, headers, etc. don’t match up. So a macro that goes through each line of the file and pulls out names, addresses, ZIP codes, etc. is a much faster way of getting that data the way we want than if we had to do it manually.

I could go on and on, but I think I am rambling already.

So, am I as big an Excel geek as I sound?

6

From www.whatis.com:

Basically, a macro is a tool for automating a task within a program. Macros are program specific (i.e. a macro from Microsoft Word will not function in WordPerfect) because the means of automation are different. This is changing; as of Office 97, Microsoft has been using VBA, or Visual Basic for Applications (a subset of the Visual Basic programming language), as their macro scripting language. This has added quite a bit of power and flexibility to the Office applications. A near-complete object model is exposed, giving the macro writer the ability to do virtually anything that the parent program can do. This can help a lot in terms of productivity, but also opens the door for abuse (hence the ongoing threat of macro viruses).

7

The second part of your sentence is implied by the second. If you did use Excel routinely, you would have problems with it. All the rest of us do, anyway.

Sorry I didn’t have anything useful to contribute.

8

…is implied by the first.

At least, that’s what it should have said. I hate having to correct my jokes.

9

Smoke’s answer was very complete except that one detail is missing. You don’t have to know Visual Basic to set up a macro in Excel.

If you have a series of menu choices, text typing, etc., that you would like to capture and execute in a single operation, you can click Tools, Macro, Record new macro. Then Excel will start recording every move you make, and writing the Visual Basic automatically for you. You never even have to see it. Use Excel Help for nitty gritty details.

10

>> Smoke’s answer was very complete except that one detail is missing. You don’t have to know Visual Basic to set up a macro in Excel.

Either your computer is defective or you did not read the entire post. I found this paragraph:

11

A macro is a script. It can be as simple as an instruction to paste the current date or as complicated as an instruction to open a different document and tell that document to perform a different macro, then to copy a range of cells, return to the original document, create a new sheet, paste values only, sort, make the text red, save the document under a new name, close the current document without saving changes, open the new document, etc etc…

Macros and scripts exists within many programs. The macros in Excel are powerful as they are written in a powerful but horrid language called “Visual Basic”, which is neither very visual nor very basic, and for even the simplest operations looks something like this:

Sub Macro1
Range(“A1:L180”).Select
Range(“L180”).Activate
Selection.Copy
Sheets.Add
ActiveSheet.Paste
End Sub
Other scripting languages are more elegant and easier to understand at first glance, although some of them lack the versatility of VB.

Here is the exact same operation expressed in AppleScript:

tell application “Excel”
activate
Select Range “R1C1:R180C12”
CopyObject Selection
Create New Sheet
paste
end tell

And here is how you could set up the same operation in QuickKeys (you don’t type this, you record your actions or select these items from a menu):

Process: switch to “Excel”
Select from Menu “Goto” from menu “Edit”
Type “L180”
Keystroke Enter
Select from Menu “Goto” from menu “Edit”
Type “A1”
Keystroke Enter while holding Shift key
Select from Menu “Copy” from menu “Edit”
Select from Menu “Worksheet” from menu “Insert”
Select from Menu “Paste” from menu “Edit”

12

Dudes, you rock. Thanks for the info y’all! :smiley:

Seriously, nice posts. Thanks a bundle. Hey, Bottle of Smoke, quite the macro expert aren’t you? Can I call you Bottle of Macros from now on? :smiley:

I will probably try to create my first macro one of these days. Be sure that if I get confused I will be coming back to dip myself in the fountains of knowledge that permeate the holy SDMB forums.

In the meantime, feel free to continue posting whatever macro-related information you can share!

And on the 8th day God created beer, And on the 9th…macros! :slight_smile:

13

One of the easiest ways for non-programming types to learn macro creation is to record a macro and then examine the code that it generated.

Having no prior experience might actually be a plus in VBA. It’s object oriented (sort of) approach can really confuse old-school procedural programmers.

The really short version of how it works:

You have objects: worksheet, range, workbook, etc can each be an object.

Objects have the following:

Properties: Information about the object. For example, the worksheets have a ProtectContents property that is either true or false, depending on whether protection is turned on. Many properties can have their values set by your macro, a few are read only (for information purposes).

Methods: These allow your program to tell an object to do something. To use the example above, the Protect method turns protection on. The Unprotect method would turn it off.

Events: These are actions that your object can respond to. The Change event for example is “triggered” when the user changes something (a cell value, etc.) on the object in question.

The format for calling these is pretty straightforward. The object name is first, followed by the property, method, or event name. A period is used to separate the two.

MyWorksheet.ProtectContents (property)

MyWorksheet.Protect (method)

MyWorksheet.Change (event)

This is an extremely simplified introduction, but should get you started on the right foot.

14

I am a “programming type”, I think, but I still used this method heavily when learning to write VBA macros. It’s good to have references on hand when it comes to optimizing, though (Death to the “select” function). IIRC, my first serious macro was about 12000 lines, despite massive reuse of subroutines. My boss wanted me to do something insanely complex with a spreadsheet…

15

Poor spreadsheets! They are so RAD (i.e., you can sketch out your data structure in an incredibly short period of time, especially if it is fundamentally a single-table array), they are ubiquitous, they are quite powerful, and so everyone tries to make them do things they…well, CAN do, but only in the same sense that I can lift weights attached to my little finger, i.e., not well, although a round of applause is in order that they can do it at all.

A spreadsheet is not a database, database functions in Excel or Lotus 123 only helping to illustrate the point.

Someone handed us (the IT department) an Excel workbook containing 8 consecutive macros which, performed in sequence, would acquire data from a tab-separated plain text file exported from another system, paste it into an existing array grid, then filter out data that did not match criteria entered by the user, then format it with summary lines totaling up figures, with empty lines of white space at appropriate points, and would go on to format the results so that certain rows were boldface (the summary rows), the columns were sized appropriately for the data they held, and the printable area was set to create printouts that could be read by mortal eyes and also look good on the page.

This is a job for FileMaker:

Import [restore import order]
Enter Find Mode [Pause]
Go to Layout [“Find things”]
Set Error Capture [on]
Perform Find
If [status(currentfoundcount)>0]
…Sort [no dialog, restore sort order]
…Go to Layout [Printout]
…Page Setup [restore, no dialog]
…Print [no dialog]
Else
…Show Message [“Ain’t none like that”]
End If

(the excel macros took 96 pages total to print out and required up to 4 hours to process an array of 62,000 rows and 276 columns. in FileMaker the solution does the same task in 55 seconds.)

On the other hand, if you want to be able to click into a cell and create a formula to get a sum or an average on the fly, or you want to quickly summarize a small array within a worksheet of information for the first time, FileMaker is so much klunkier (no equivalent of setting up a formula and dragging down and copying down the equivalent formula for equivalent references; you want to add something new, you have to define a field, and if you want it to apply only to a subset of the data, you have to do a “find” and omit the rest of your data or else define TWO fields and define an self-join relationship and then a THIRD field…).

And in general you don’t want to do your graphic in FileMaker Pro.

But if you want to talk to a series of other external programs (including some that aren’t running on your computer or even using the same operating system), grab the resultant data, analyze it, do divergingly different things depending on a cascading set of possible values or combinations of values in different places, then send an email to an address calculated from the contents of several fields, using your chosen email program, and, while waiting for the reply, brew a cup of coffee for you, fill out your time sheet for the time spent on this activity, and launch your favorite game for you to play while you await your printouts, it’s FileMaker time!