Automate Excel

To change the colour of a cell we can use:

Cell.Interior.ColorIndex = Num

Where:
• Cell is the cell reference
• Interior – refers to the colour of the actual cell colour (The interior property)
• Colourindex is a value between 1 and 56 for one of Excel’s 56 predefined colours

And Num is the number colour assigned to the cell. However, it isn’t always easy to remember which number represents which colour. The following subroutine changes the cell colour based on the row number. So for example row 3 will have colour 3 etc.

As there are 56 preset colours in Excel, this means that cells 59, 115 will have the same colour as the cell in row 3:

Option Explicit
Private Sub CommandButton1_Click()
Colour_Range (Sheets("Sheet2").Range("A1:A2000"))
End Sub
Sub Colour_Range(Cell_Range As Range)
' Will Colour each cell in range
Dim Cell
For Each Cell In Cell_Range
Cell.Interior.ColorIndex = Cell.Row Mod 56
Cell.Offset(0, 0).Value = Cell.Row
Next
End Sub

The routine is activated by a click event.

To download the .XLSM file for this tutorial, please click here.

The standard Vlookup function can be used to find a value within a table:

And we would use VLOOKUP like so:

VLOOKUP(A1:10,”Dog”,2,FALSE) to give the value 30.

However, in this list we see that Dog occurs 3 times. The standard VLOOKUP function will only return the value associated with the first item in this list. It won’t return the 125 or 9,250 with the 2nd or 3rd instance of “dog” in this list.

The following function allows us to specify a range, an expression to be searched for, and the instance and then return the corresponding value:

Function Find_nth_Occurrence(Column_Range As Range, Expression As String, Occ As Integer) As Double
Dim Cell
Dim Occurrences_to_date As Integer
Find_nth_Occurrence = 1000000
Occurrences_to_date = 0
For Each Cell In Column_Range
If Cell.Value = Expression Then
    Occurrences_to_date = Occurrences_to_date + 1
    If Occurrences_to_date = Occ Then
      Find_nth_Occurrence = Cell.Offset(0, 1).Value

    End If
End If
Next Cell
End Function

The main difference between this and the standard VLOOKUP function is that in this case, the range is the only the range of labels – not the entire data range.

The following is a subroutine that calls this function based on the click event from a command button. It looks in the range A1:A8 on Sheet2, for the 3rd instance of the word Dog:

Private Sub CommandButton1_Click()
Dim Answer As Double
Answer = Find_nth_Occurrence(Sheets("Sheet2").Range("A1:A8"), "Dog", 3)
MsgBox AnswerEnd Sub

The variable “Answer” stores the result of the function – which is then displayed in a Msgbox on the screen:

However if the word can’t be found in the list or the frequency does not occur e.g there isn’t a 5th instance of the word “Dog”, then the value of 1,000,000 is returned :-

Answer = Find_nth_Occurrence(Sheets(”Sheet2″).Range(”A1:A8″), “Dog”, 5)

Or

Answer = Find_nth_Occurrence(Sheets(”Sheet2″).Range(”A1:A8″), “Horse”, 2)

This example shows how Excel and VBA can interact with each other. VBA will be used to generate function values over a given range. The answers will then be stored within an Excel workbook and used to produce a graph.

This could be done entirely with Excel without the use of VBA. But if the formulae was particularly complicated then the Excel expression would be difficult to decipher. Instead, we just pass the function arguments to a VBA function. The function then evaluates the expression and returns the answer to Excel.

In this case our function will be a “saddle”:

X^2 – Y^2

Or

X*X – Y*Y

So we set up our x and y values in Excel:

Our function is called Demonstrate_Function and will have two arguments (x and y). The following is the code for this function:

Function Demonstrate_Function(x_arg As Long, y_arg As Long) As Long

'Dim Demonstrates purpose of function interacting with Excel
'The answer will be stored in "Demonstrate Function"

'the function will just take the difference between the squared values of x and y

Demonstrate_Function = (x_arg * x_arg) - (y_arg * y_arg)

End Function

Note that this code must be inserted in a module. So to call the function with an x-value of 1 and a y value of 2 we put the following code into a cell:

T3= Demonstrate_Function (1,2)

which gives a value of -3.

And we now put this formulae into all the cells in our range:

However to see the function more clearly we can plot a surface chart:

The following code will populate a given range with a random number between 0 and 1000:

Sub Randomise_Range(Cell_Range As Range)
' Will randomise each cell in Range
Dim Cell
'Turn off screen alerts
Application.ScreenUpdating = False
For Each Cell In Cell_Range
Cell.Value = Rnd * 1000
Next Cell
Application.ScreenUpdating = True
End Sub

The code “Application.ScreenUpdating =FALSE” and “Application.ScreenUpdating = TRUE” serve to turn off and turn on screen alerts – making the code much faster to run.

It can be set up via a click event, with the main routine specifying the range:

Private Sub CommandButton1_Click()
Randomise_Range (Sheets("Sheet3").Range("A1:T8000"))
End Sub

So this case, cells A1:T8000 on sheet 3 are populated with random numbers – once the command button 1 is clicked.

Download the .XLSM file here

The following VBA function will check if a given date is between two other dates and if so, it will return the value TRUE. If the date is not in between the dates, then it will return the value FALSE.

Function Compare_Dates(Start_Date As Date, End_Date As Date, Other_Date As Date) As Boolean
' Boolean Function to compare dates
'Will return TRUE only when Other_Date is between Start_Date and End_Date
'Otherwise will return FALSE
'Set outcome to FALSE - default value
Compare_Dates = False
'Compare Dates
If ((Other_Date >= Start_Date) And (Other_Date <= End_Date)) Then

'If Other Date is between Start and End Date then set to true
Compare_Dates = True

End If
End Function

The function has three arguments:
• Start_ date – the earliest allowable date
• End_ Date – the latest allowable date
• Other_Date – the date being compared

The function must be in an Excel module. It can be run from an Excel Workbook e.g:

A4= Compare_Dates(21-06-2003,12-02,2008,15-09-2008)
A3 =Compare_Dates(A1,A2.A3)
A1= Compare_Dates(13-03-2005,18-08-2005,A6)
So Compare_Dates(25-04-2007,12-07-2008, 23-06-2006) will give the value TRUE as 23-06-2006 is between 25-04-2007 and 12-07-2008.

Likewise Compare_Dates(19-07-2003,12-12-2001,12-08-2008) will return FALSE as 12-12-2001 is NOT between 19-07-2003 and 12-08-2008

Download .XLSM function here

It is quite easy to work out the average of a series of numbers:

We just use the average function in Excel. However what happens if we want the average of only the NON – ZERO Values.  The function COUNTIF will count up the cells in a range that meet a criteria:

COUNTIF(Range, Criteria)
The criteria must be placed in quotes –so if our criteria is greater than 0 we need the phrase “>0”. In this case our criteria is not equal to zero.  Remembering that the average of  a series of numbers is their total divided by the total number we have:

Where the “<>0” allows us to count up the non zero values in the range:

Imagine that we have a table of data such as:

And then we do a VLOOKUP on a city that isn’t there we get an N/A error:

This is not much of an issue if we have only a single N/A error. However, if we have several N/A (each arising from a separate VLOOKUP) in a table then it can look rather unsightly. In addition any totals that involve these cells also result in the value N/A. In short the N/A error can propagate through the entire workbook:

To get around this, Excel has the function ISNA which allows us to check whether or a condition or a cell has the value N/A. It is usually used as part of an IF statement:

B1 =IF (ISNA(A1), 0,A1)

i.e if the cell A1 has the value ISNA then the cell B1 takes the value 0 otherwise it has the value in A1.

Or if we have a VLOOKUP formulae, we can check whether or not it evaluates to NA using the same approach. If does then we take the value 0 otherwise we take the value returned by the VLOOKUP value:

=IF(ISNA(VLOOKUP(B18,B6:C13,2,FALSE)),0, VLOOKUP(B18,B6:C13,2,FALSE))

And if this approach is adopted in the individual rows in the above table we have:

Which is much more presentable and the totals now evaluate sensibly.

Imagine that we have some Sales Figures for a company:

And that we wish to find the Total figures for the year to date. We can add a drop down like so:

So that we can specify the current month. Hence we now want to work out the year to date for March. The simplest format would be to have a formulae that extended across the range:

And then we would just change the formulae every month.

However Excel permits another approach. We could set up a dynamic range whose size varied on the month that we are in. As we change the month in the drop down then the size of the range changes.
So for the month of March the range is 3 columns long, and for the month of June it would be 6 months along.

The size of the range is governed by the month. One way of formulating this is to use the Month function:

=Month(c8)

Where c8 is the cell address of our drop down. However the method that is preferred is to use the MATCH function to determine the position of the current months in all the months in our report:

MATCH(c8,$c$3:$j$3,0)

Where:
• c8 is the cell address of the current month
• C3:J3 is the address of all our months
• 0 is to ensure an exact match

Now we can specify the size of our dynamic range by the OFFSET function which has 5 arguments:
=OFFSET(reference, rows, cols, height, width)

Where:
• Reference is the top left hand corner of our dynamic range – cell C5 – the first cell that we want to sum
• Rows – the number of rows down from our base cell – this is 0
• Cols – the number of cols across from our base call – this is 0
• The width of our dynamic range – which is 3 in this case. However as we wish the range to vary by month we will put our MATCH formulae here
• This is the height of our dynamic range which is 1

So our OFFSET formulae is:
= OFFSET(c5,0,0,MATCH(c8,$c$3:$j$3,0),1)

Finally we need to tell Excel to SUM this to give the complete formulae as:
= SUM(OFFSET(c5,0,0,MATCH(c8,$c$3:$j$3,0),1))

We have:

Now if we change the month in the dropdown, the correct year to date figure flows through:

As this is an automatic update this approach has the following advantages:
• There is no need to change the formulae each month
• As there are less formulae changes, less scope for error
• The spreadsheet can be used by somebody who has limited Excel knowledge – they can just change the dropdown and not be bothered by formulae

The following VBA function counts the number of words in a string:

Function Number_of_Words(Text_String As String) As Integer
'Function counts the number of words in a string
'by looking at each character and seeing whether it is a space or not
Number_of_Words = 0
Dim String_Length As Integer
Dim Current_Character As Integer

String_Length = Len(Text_String)

For Current_Character = 1 To String_Length

If (Mid(Text_String, Current_Character, 1)) = " " Then
    Number_of_Words = Number_of_Words + 1
End If

Next Current_Character
End Function

It is worth noting a couple of points:
• This code must be inserted in a Workbook Module – from the VBA pane, click on Insert and then module

• The function can be called from an excel workbook cell either with an either explicit text or cell reference as an argument:
A3 = Number_of_Words(“Pig Dog Cat”)
A4 = Number_of_Words(D1)

The function works by traversing the length of a string and seeing if the next character is a space and if so it adds one to the number of spaces in the string.

The following code is a subroutine that will get the names of all the files that are present in a directory path:

Sub List_All_The_Files_Within_Path()

Dim Row_No As Integer
Dim No_Of_Files As Integer
Dim kk25 As Integer
Dim File_Path As String

File_Path = "C:\My Documents"

Row_No = 36

'Lists all the files in the current directory

With Application.FileSearch
.NewSearch
.LookIn = File_Path
.Filename = "*.*"
.SearchSubFolders = False
.Execute

    No_Of_Files = .FoundFiles.Count

    For kk25 = 1 To No_Of_Files
       Worksheets("Sheet1").Cells(kk25 + 5, 15).Value = .FoundFiles(kk25)

    Next kk25

End With

End Sub

It will write the filenames to Sheet1 in column O – starting at row 36.

Download the Excel file here