{"id":80,"date":"2008-10-23T13:20:04","date_gmt":"2008-10-23T12:20:04","guid":{"rendered":"http:\/\/www.setfiremedia.com\/blog\/?p=80"},"modified":"2008-10-23T14:12:15","modified_gmt":"2008-10-23T13:12:15","slug":"7-quick-easy-vba-macros-tips-for-excel","status":"publish","type":"post","link":"https:\/\/www.setfiremedia.com\/blog\/7-quick-easy-vba-macros-tips-for-excel","title":{"rendered":"7 Quick and Easy VBA Macros & Tips for Excel"},"content":{"rendered":"

\"\"<\/a>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!<\/p>\n

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!<\/p>\n

<\/p>\n

1. VBA Basics<\/h3>\n

To program visual basic for excel we need to define our own functions and create buttons or define actions that initiate them.<\/p>\n

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.\u00a0 You also need to declare some variables to work with:<\/p>\n

Dim inty1,inty2,inty3 as Integer<\/pre>\n
For the purpose of the macros I introduce you will only need the Integer<\/em> (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<\/a> (essential if you plan to use numbers in the tens of thousands).<\/p>\n
To reference an excel cell you can call the Cells<\/em> function specifying the row and column i.e.:<\/p>\n
Cells(Row,Column)<\/pre>\n
so:<\/p>\n
inty1 = Cells(1,2)<\/pre>\n
would reference the cell B1 and loads its contents into inty1.<\/p>\n
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 <\/em>loops, for these you need to specify what criteria you wish to loop for i.e.<\/p>\n
For inty1 = 3 To 15\r\n  Cells(1,Inty1)=Cells(1,Inty1)+5\r\n\r\nNext<\/pre>\n
In the above example you see that we can use inty1 to sequentially set the value of some cells, the Next <\/em>keyword clarifies the end of the For <\/em>loop where the program returns to the start and increments inty1<\/em>. Note that the Cells<\/em> function can be used to set the value of a cell as well as read it.<\/p>\n
2. Function: Compare Two Large Ranges For Subsets<\/h3>\n
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!
\n\"Range<\/a><\/p>\n
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.<\/p>\n
Private Sub Activate_Click\r\n\r\n  \/\/ sub = sub-phrase\r\n  \/\/ com = comparison\r\n\r\n  Dim subRow, listRow, hashSubRows, hashListRows, outputRow as Integer\r\n\r\n  outputRow = 2 \r\n\r\n  \/\/ skip over the column titles and output at row2\r\n\r\n  hashSubRows = Cells(1, 4)+1 \r\n\r\n  \/\/ Read in the number of sub rows and convert to Row#\r\n\r\n  hashListRows = Cells(2, 4)+1 \r\n\r\n  \/\/ Read in the number of list rows and convert to Row#\r\n\r\n  For subRow = 2 To (hashSubRows)\r\n  For comRow = 2 To (hashListRows)\r\n  If (InStr(Cells(comRow, 2), Cells(subRow, 1)) > 0) Then\r\n\r\n  \/\/ InStr is similar to Find(), if it returns >0\r\n  \/\/ - that\u2019s a positive hit!\r\n\r\n  Cells(outputRow, 10) = Cells(subRow, 1)\r\n  \/\/Write column1 to output\r\n\r\n  Cells(outputRow, 11) = Cells(comRow, 2)\r\n  \/\/Write column2 to output\r\n\r\n  outputRow = outputRow + 1\r\n  \/\/ Write to the next row next time\r\n\r\n  End If\r\n  Next\r\n  Next\r\nEnd Sub<\/pre>\n
A seasoned programmer will quickly realise that there is room for improvement in this macro, we could easily:<\/p>\n