Excel Macro Basics

In my last post I wrote about a time saving Excel macro. I just wanted to give a little insight into macros to people who are not familiar with them (so if you are please bear with me and I’ll get back to more advanced tips soon).

There are many engineers, designers, and technicians who want to write macros but simply don’t have time to sit down and learn everything they need to know. Throughout these email tips I will cover those core items to help teach beginners important concepts needed to create custom macros and will show experienced users additional tips and tricks.

What is a Macro and why do we use them? 

What is a macro? If you perform a task repeatedly, you can take advantage of a macro to automate the task. A macro is a series of functions, written in a scripting language, that you group in a single command to perform the requested task automatically. Macros use programming but you don’t need to be a programmer or have programming knowledge to use them (though it definitely helps). Macros are used to save time and reduce the possibility of human error by automating repetitive processes, standardization, improving efficiency, expanding Excel’s capabilities, and by streamlining tasks.

Macros are created by two primary methods:

1.Macro recorder
2.Write custom code with the macro editor
Record Macros is a good tool if you’re stuck or unsure what syntax to use. You can record a few activities related to what you want to accomplish then look at the code that was created in the developer. However, record macros won’t cover certain aspects of coding, particularly the control flow of the code. One won’t see loops, if-else statements, or select case statements in recorded macros. One of the best ways to learn about macros is by looking at and using code that others have written and is proven to work.
Every so often I will be providing VBA code for a macro I have written. I encourage you to use them or modify the code to fit your specific needs.

Time Saving Excel Macro

I often find myself having to create multiple folders and directories before beginning a new project. Many others may take the time consuming method of doing this by hand but you can actually save yourself a lot of time by using a simple VBA macro in an Excel spreadsheet. One method of doing this is to start a new spreadsheet and save it as a macro-enabled workbook in the location where you want to create the multiple folders (such as C:\Work Directory\Parts List).
Next, in column A list all the names of the folders you want to create. Now, hold the “Alt” key down and press “F8” to open the Macros window. Enter the name “CreateFolders” and click the Create button which will open the macro editor. You can copy and paste the following code:

Sub CreateFolders()

‘create the folders where-ever the workbook is saved

Dim Rng As Range

Dim maxRows, maxCols, r, c As Integer

Set Rng = Selection

maxRows = Rng.Rows.Count

maxCols = Rng.Columns.Count

For c = 1 To maxCols

r = 1

Do While r <= maxRows

If Len(Dir(ActiveWorkbook.Path & “\” & Rng(r, c), vbDirectory)) = 0 Then

MkDir (ActiveWorkbook.Path & “\” & Rng(r, c))

On Error Resume Next

End If

r = r + 1


Next c

End Sub

Now all you have to do is highlight the cells and run the macro. Your folders are automatically created just like that! Save the macro and you can use it over and over again, saving you lots of time and impressing your fellow employees!

But what do all those random words in the code mean? I’ll be explaining more about macros and how you can use them to improve your speed and efficiency at work soon, so stay tuned!

Welcome to Excel Spreadsheets Help!

Welcome to Excel Spreadsheets Help, the site where I’ll help you with your Excel spreadsheets.
Why use Microsoft Excel? With its wide variety of extreme uses, Microsoft Excel is the Swiss Army Knife of software tools. From a hand-made quilt designer to a 3D graphics engine, Excel is one of the most versatile and user friendly programs around. It doesn’t matter what your skill level is – anyone can learn to use Excel!
Through a series of example codes and tutorials I’ll explain how to use Microsoft Excel formulas and create VBA macros for real world applications. No programming experience is required! Next time, I’ll be sharing with you one of my best time saving macros.