7 Quick and Easy VBA Macros & Tips for Excel

With large PPC customers comes large problems! Manipulating keywords and producing new combinations is great when you have 50 keywords, when working with 50,000 keywords the PPC professional needs to make a choice between getting RSI and getting their macro on!

With very little knowledge you can brute force a lot of simple problems using macros, here is a quick introduction to macro semantics and some functions that PPC has called for!

1. VBA Basics

To program visual basic for excel we need to define our own functions and create buttons or define actions that initiate them.

To create a new vba function for a button-click, create a button using the ‘Control Toolbox’ / ‘Developer’-‘Insert’ menu and double click it. This will automatically create an on-click sub program for you to work in.  You also need to declare some variables to work with:

Dim inty1,inty2,inty3 as Integer

For the purpose of the macros I introduce you will only need the Integer (number) and String types, this allows us to load numerical and text values from Excel cells and perform functions on them without having to re-read them into excel. If you want to know more about the different number types then check out this MSDN article (essential if you plan to use numbers in the tens of thousands).

To reference an excel cell you can call the Cells function specifying the row and column i.e.:

Cells(Row,Column)

so:

inty1 = Cells(1,2)

would reference the cell B1 and loads its contents into inty1.

You will also find it useful to loop through a range of cells, for example if you wanted to perform a function on columns A & B (such as checking them against a complex criteria) and place the result in Column C. There are several loop types but to keep things simple we can work with FOR loops, for these you need to specify what criteria you wish to loop for i.e.

For inty1 = 3 To 15
  Cells(1,Inty1)=Cells(1,Inty1)+5

Next

In the above example you see that we can use inty1 to sequentially set the value of some cells, the Next keyword clarifies the end of the For loop where the program returns to the start and increments inty1. Note that the Cells function can be used to set the value of a cell as well as read it.

2. Function: Compare Two Large Ranges For Subsets

We are designing a macro that will perform the same as the excel Find() function for each cell in a column to find if the contents of that cell are in any of the cells in an adjacent column. For small data sets, especially of the same size there are easier and quicker ways of making this comparison but as soon as you hit a few dozen rows and columns of different sizes then a macro becomes handy. This macro is very simplified and will only look for occuraces of X in Y (not Y in X) but is incredibly easy to fix and modify, for occurances of Y in X simply swap the input columns around!

Create the above layout in a new sheet [click to enlarge], 4 columns have been coloured in, a button created and 2 blank fields outlined in the centre. The first 2 columns are our inputs and the last 2 columns are our outputs, the macro will explain their function more.

Private Sub Activate_Click

  // sub = sub-phrase
  // com = comparison

  Dim subRow, listRow, hashSubRows, hashListRows, outputRow as Integer

  outputRow = 2 

  // skip over the column titles and output at row2

  hashSubRows = Cells(1, 4)+1 

  // Read in the number of sub rows and convert to Row#

  hashListRows = Cells(2, 4)+1 

  // Read in the number of list rows and convert to Row#

  For subRow = 2 To (hashSubRows)
  For comRow = 2 To (hashListRows)
  If (InStr(Cells(comRow, 2), Cells(subRow, 1)) > 0) Then

  // InStr is similar to Find(), if it returns >0
  // - that’s a positive hit!

  Cells(outputRow, 10) = Cells(subRow, 1)
  //Write column1 to output

  Cells(outputRow, 11) = Cells(comRow, 2)
  //Write column2 to output

  outputRow = outputRow + 1
  // Write to the next row next time

  End If
  Next
  Next
End Sub

A seasoned programmer will quickly realise that there is room for improvement in this macro, we could easily:

• automatically detect the end of the range and not need to enter the number of rows
• automatically remove duplicates from the output
• clear the output columns before the macro starts

I leave implementing these to you!

3. Directly Call Excel Functions Within A Macro

A whiz on Excel but lost as to which VBA function to use? Most Excel functions can be called directly within a Macro i.e.

Application.WorksheetFunction.Sum(inty1,inty2)

4. Function: Substitute Multiple Different Values Into All The Cells of a Range

Does this look familiar?

=Substitute(substitute(Substitute(substitute(Substitute(substitute(Substitute(substitute(….

As far as I know Excel 2003 allowed eight substitutions if you had the patience to write them.

Let’s put point #3 into practice. As before, replicate the below template [click to enlarge] and code.

Dim subRow, comRow, outputRow As Long
  outputRow = 2
  For subRow = 2 To (Cells(1, 4) + 1)
  For comRow = 2 To (Cells(2, 4) + 1)
  If (InStr(Cells(comRow, 2), Cells(subRow, 1)) > 0) Then
    Cells(outputRow, 5) = Cells(subRow, 1)
    Cells(outputRow, 6) = Cells(comRow, 2)
    outputRow = outputRow + 1
  End If
  Next
Next

Notice how we can refer to Cells directly, although this wouldn’t be ideal for hundreds of substitutions it makes for a very short macro.

5. The Automatically Refreshing Pivot

Create your pivot and get your layout how you like it, right click the pivots sheet and click view code, from the drop down list select your worksheet and the sub “Worksheet_Activate()” which allows you to specify a function to run every time the worksheet is opened.

Private Sub Worksheet_Activate()
  PivotTables("PivotTable1").Refresh
End Sub

This works best if you play with the Pivot options to disable ‘AutoFormat’ (which resets the layout every refresh – very irritating) and enable ‘AutoSort’ on your desired field. Using this method I prefer to make “Templates” – a blank but formatted excel book with an automatically refreshing pivot on the 2nd sheet with pretty colours and large fonts which will turn your weekly input data into something fancy with minimal intervention.

A final comment on pivots: If there is only one table on your worksheet then you can let your pivot see the entire worksheet and simply filter out blanks, to do this enter $A:$Z into the range selection at the start of the pivot wizard. Remember to replace $Z with a column thats way past your last column, you can re-arrange your table and the macro/pivot will still work but inserting blank columns or renaming columns used in your pivot will cause errors.

6. Function: Create All Combinations of W&X&Y&Z

This macro will create a brute-force list for all combinations of the inputs, Create the below template [click to enlarge]:

Enter ‘1’ for blank columns and the number of rows to read for inputted columns.

Dim col1Row, col2Row, col3Row, col4Row, noCol1Rows, noCol2Rows, noCol3Rows, noCol4Rows, outputRow As Integer
  outputRow = 6

  noCol1Rows = Cells(1, 2)
  noCol2Rows = Cells(2, 2)
  noCol3Rows = Cells(3, 2)
  noCol4Rows = Cells(4, 2)

  For col1Row = 6 To (noCol1Rows + 5)
  For col2Row = 6 To (noCol2Rows + 5)
  For col3Row = 6 To (noCol3Rows + 5)
  For col4Row = 6 To (noCol4Rows + 5)

  Trim(
  Cells(outputRow, 5) = Cells(col1Row, 1) & " " & Cells(col2Row, 2) & " "  &  Cells(col3Row, 3) & " " & Cells(col4Row, 4)
  )

  outputRow = outputRow + 1

  Next
  Next
  Next
  Next

You can carefully remove some of the FOR loops to reduce the comparison to just Z&Y, Z&Y&W or insert more loops to your hearts desire but remember to create all of the necessary variables and carefully change the template if you want extra loops.

7. Some Extra Information

You may find your large comparison macros take a long time to run, Excel may be refreshing the page every single time a calculation is made, to combat this toggle screen updating at the start of your sub:

Application.ScreenUpdating = False
Application.xlCalculationManual

And then resume at the end if desired:

Application.ScreenUpdating = True
Application.xlCalculationAutomatic

Happy Macroing!