Advanced Excel: Macro Examples

Creating Excel spreadsheet macros is a great way to save time and reduce the possibility of human error by automating repetitive processes. Other reasons for learning how to write VBA macros include: standardization, improve efficiency, expand capabilities, and streamline tasks. Macros in Excel are created through two primary methods: the macro recorder or by writing custom code with the macro editor (or even with notepad). Recording is a great way to figure out syntax or a specific, simple task if you are stuck but writing custom code is far superior, especially if you are dealing with multiple Excel files. Here are a few basic, easy lines of code which are helpful to learn when beginning to create macros. Most of these deal with layout or formatting of your spreadsheet. I typically would add these types of commands to the end of my macro after all the data is where I would like it.


To insert a row above an active cell use this code:

ActiveCell.EntireRow.Insert

Conversely, to insert a  row below the active cell:

ActiveCell.Offset(1).EntireRow.Insert

To insert a new, blank column to the left of the active, selected cell, use this:

ActiveCell.EntireColumn.Insert

Or, to insert a column to the right of the active cell, change to this:

ActiveCell.EntireColumn.Offset(0, 1).Insert


To delete a column, specify the column number (A being 1, B = 2, and so on):


ActiveWorkbook.ActiveSheet.Columns(16).Delete


To add a diagonal line through a range of cells with a macro use the following code:


Range(insert desired range , ie A2:B2).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    With Selection.Borders(xlDiagonalUp)
        .LineStyle = xlContinuous
        .ColorIndex = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With

To change the background color to white, select a range of cells then type this:


With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With


To create a thick border on the right of a cell:


Selection.Borders(xlEdgeRight).Weight = xlThick


Or, make the bottom border thick:


Selection.Borders(xlEdgeBottom).Weight = xlThick


To Autofit a columns width, specify a range:


Range(AA:CC).EntireColumn.Autofit
Read more about creating Excel spreadsheetmacros.

How to use the SUMIF formula in Excel

SUMIF is a useful formula to know how to use when creating Microsoft Excel spreadsheet templates. An example I can give is from the world of engineering. Say you have to create a part list or a bill of material (BOM for an assembly, such as a snow blower. The snow blower has multiple parts with have standard parts, such as washers, bolts, nuts, etc,, attached to it. At the bottom of my BOM I want to sum all of the parts for my top level assembly but I don’t want to include the number of standard nuts and bolts. In this case I use the SUMIF formula along with some nifty formatting.

 

 

To count the number of parts in a specified column use the =SUMIF() formula. In my case, =SUMIF(K5:K17,">0").

If a number exists in a column which is greater than zero then sum the given range in the column.

One thing that I did was to change the standard part counts, which used to be entered as (1), (2), (3), etc. (entered '(1)). I have since changed this to a new format: -1, -2, -3... yet the entries still look like (1), (2), (3).  This way the SUMIF formula can count the numbers since they are not being recorded as text.  With the numbers being negative it can also make it so the formulas can selectively count them in the different parts of the spreadsheet.  So, when entering parts like weld nuts, nut plates, pin, collars, etc. enter the count as a negative (-) number.  This can either be done as -1 or (1), both will mean the same. I also put in a custom number format so you  no longer have  to type "-1", "-2", etc.  Just enter 1, 2, 3, etc. and the "-" will be placed in front automatically. Little improvements like this go a long way in improving the time it takes to compile one of these massive spreadsheets.

Let me know if you have any questions or concerns and if this format is more or less helpful and an improvement in function and readability.

Why doesn't center alignment macro work?

Troubleshooting Excel macros
I was recently working on an Excel spreadsheet macro to automatically center the text with a cell on the cells within a sheet. This is a VBScript macro executed from a CAD system and exports desired data to an Excel file. I used the following code:

With Excel.Range("A"&"1", "F"&RwNum)

.Font.Name = "Arial"
.Font.Size = 9
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.ColumnWidth = 25
.RowHeight = 20
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlThick
.Borders.ColorIndex = 1
.WrapText = True
.EntireColumn.Autofit

End With

Well, the Horizontal and Vertical alignment was not working at all. I tried numerous different methods, spellings, etc. Nothing seemed to work. Then I discovered I was missing a line of code. Simply add this statement before the With loop:

Const xlCenter = -4108

Because we're accessing a spreadsheet from outside of Excel the xlCenter constant is not declared which makes VBA treat it as 0. So in our code we need to set a value for xlCenter, hence the code line of: Const xlCenter = -4108. Here are some other values you may need to set if you are formatting or sorting your Excel sheet.

Const xlCenter = -4108
'Const xlAscending = 1
'Const xlYes = 1
'Const xlSortOnValues = 0
'Const xlSortNormal = 0
'Const xlTopToBottom = 1
'Const xlPinYin = 1

To figure out the value number go into excel VBA editor and press crtl+g then type ?xlAscending and it will list the current value.

To sort a list using VBScript as opposed to VBA here is an example code you might need:

'sort parts in numerical order then sort fasteners
Excel.Range("A:G").Select
Excel.Selection.Sort Excel.Range("G1"),1,Excel.Range("A1"),,1,Excel.Range("B1"),1,1,1,False 

How to delete a row based on condition?

I created a macro to delete an entire row in an Excel spreadsheet based upon a specific given condition. I've seen a few others code for this same problem and I thought they overthought it and wrote a unnecessarily long code. Here is my quick and easy solution. Let's say I have a few cells in column A named "FINAL_BODY" where I want to delete the entire row.

Sub Delete_FINALBODY_Rows()

Dim r As Long

For r = Range("A" & Rows.Count).End(xlUp).Row to 1 Step -1

If UCase(Left(Cells(r, "A").Text, 10)) = "FINAL_BODY" Then Rows(r).Delete

Next r

End Sub

Dilbert Comic Strip: How to do Formulas in Excel

I saw these Dilbert strip today in the Sunday paper and thought I would share it as it mentions Microsoft Excel. (Click on the image to see the full strip)

VBA to Export Properties to Excel

 Excel is a powerful program and I often find myself using it in conjunction with other software programs. As a CAD engineer, I am often making parts lists in Micosoft Excel based upon 3D data files in CAD software, such as CATIA V5. Lucky for me CATIA has its own catscript VBA editor which allows you to write macros which can automatically export data from Catia to Excel, a huge timesaver! 

I recently created an example where I take several key properties from a 3D CAD model part including the mass, thickness, material, and part number and export them into a nice little spreadsheet. I can then format the spreadsheet from within my Catia macro. Every little thing I can do to cut back on those extra mouse clicks (call me lazy I guess). 

This code can also help you to learn how to program in visual basic which is very helpful in automating repetitive tasks. Here is a small sample:

'to excel
Dim Excel As Object
Dim workbooks As workbooks
Dim workbook As workbook
Dim Sheets As Object
Dim Sheet As Object
Dim worksheet As Excel.worksheet
Dim myworkbook As Excel.workbook
Dim myworksheet As Excel.worksheet

On Error Resume Next
Set Excel = GetObject(, "EXCEL.Application")
If Err.Number <> 0 Then
Err.Clear
Set Excel = CreateObject("EXCEL.Application")
Else
Err.Clear
MsgBox "Please note you have to close Excel", vbCritical
Exit Sub
End If
Excel.Visible = True

'load a sheet
Set workbooks = Excel.Application.workbooks
Set myworkbook = Excel.workbooks.Add
Set myworksheet = Excel.ActiveWorkbook.Add
Set myworksheet = Excel.Sheets.Add

'download the properties
'row one
Excel.Cells(1,1)="Part Number"
Excel.Cells(1,2)="Thickness"
Excel.Cells(1,3)="Material"
Excel.Cells(1,4)="Mass"  

Download the full code here. You can also import Excel files into programs such as CATIA but that is a post for another day!

 CATIA V5 to Excel Properties Exporter Macro.catscript download

How to view mpp files without Mircosoft Project

This may not be an excel tip but it is in the Microsoft Office family of products. If you're searching for how to open a .mpp file without Microsoft Project you may have found yourself in a similar situation which I was in not too long ago. I needed to open a Microsoft Project file but I didn't have the software on my pc. I did what most people would probably do and browsed the internet for a free mpp viewer. The problem is all the viewers I found had to be downloaded and installed. Well, I had no administrative privileges on my machine and could not install any new programs. Luckily I found a great solution. Go to www.amiproject,com. You can open and view your mpp files in an internet browser without having to download and install any software. I think it even lets you edit and save your project file as well.

Check out other project management resources here.