Create a Macro in MS Excel for a Schedule
sales@landfx.com
+1 805-541-1003

Create a Macro in MS Excel for a Schedule

Issue

You want to create a macro in MS Excel to format schedules created with our software. For example, you may need a way to calculate the percentage of your total plants used in a project that are of a particular species. After creating your macro, you'll be able to export your schedules to Excel and run any necessary formatting and calculations automatically with the click of a button.

 

You can create macros for any or all of the following schedule types:

  • Plant Schedules
  • Irrigation Schedules
  • Site schedules such as Reference Notes Schedules

 

 

 

Solution

 

Step 1: Create & record your macro

1A. In CAD, run the type of schedule you want to format with your macro – for example, a Plant Schedule.

 

When creating the schedule, select the formatting options you're likely to want in this type of schedule the majority of the time – for example, which columns you generally like to include in this type of schedule, plant code format, etc.

 

Select the option to send the schedule to a spreadsheet.

Plant Schedule formatting options

 

 

 

Open schedule file in MS Excel

1B. Open the schedule file in MS Excel.

 

In this example, we'll use the schedule pictured to the left as a basis to create and record a macro.

 

 

 

1C. Select the DEVELOPER tab.

 

Then click the Record Macro button.

MS Excel, DEVELOPER tab, Record Macro button

 

 

 

Record Macro dialog box, adding a macro name and description

1D. In the Record Macro dialog box, enter a name for your macro and, if you want, a description. Then click OK.

 

 

 

1E. Create your macro by formatting your schedule exactly how you want it.

 

For example, you may want to use the Conditional Formatting tool to apply formulas to specific columns. You can apply a specific font and point size to text, highlight certain columns in a specific color, and carry out any number of other formatting changes.

Example: Conditional Formatting tool

 

 

 

Example of columns created using the Conditional Formatting tool

In our example, we've formatted the column titles and used the Conditional Formatting tool to add a Perc. (percentage) column that calculates the percentage of the total plant count occupied by each variety, as well as Unit Value and Total Units columns that also run calculations.

 

 

 

1F. When finished creating your macro, stop the recording by clicking the Stop button in the bottom left corner of your Excel interface.

MX Excel, Stop button

 

 

 

You've created your macro and are ready to use it on a schedule. You can create different macros for different types of schedules, different clients, etc.

 

 

 

Step 2: Run your macro on a schedule

Plant Schedule dialog box, Spreadsheet destination option

2A. In CAD, run the type of schedule you want to format with your macro – for example, a Plant Schedule.

 

Select the option to send the schedule to a spreadsheet.

 

 

 

2B. With your schedule open in Excel, select the DEVELOPER tab, then click the Macros button.

MS Excel, DEVELOPER tab, Macros button

 

 

 

Macro dialog box, selecting a macro and clicking Run

2C. In the Macro dialog box, select the macro you want to run on the schedule.

 

Click Run to run the macro.

 

 

 

The macro will run on the schedule.

 

In our example, the macro has applied the stylistic and conditional formatting from the macro to a Plant Schedule we've exported from CAD.

Macro applied to schedule, example
Last modified on May 25, 2022

Contact

  • Land F/X

Our software tailors AutoCAD to the needs of landscape architects, irrigation designers, and other professionals. We automate your most tedious tasks and ensure accuracy, giving you more time to design.

Log in to landfx.com:

Username:

Password: