The following UDF will return the formulae for a given cell reference:
Kaps | 
10 24th, 2008| 
Function Show_Cell_Formulae(Cell As Range) As String
Show_Cell_Formulae = "Cell " & Cell.Address & " has the formulae : ' " & Cell.Formula & " '"
End Function
So if we have:
Where the cell B7 has the formulae : ‘=MAX(B5:G5) then we can type in another cell:
B9 = Show_Cell_Formulae(b7)
And we get a text string with the cell reference and the formulae contained within it.
The following tutorial will describe how to create a bar chart using VBA.
Steps:
1. Enter the VBA project window by right clicking on a sheet name and selecting “View Code” or by selecting “ALT, F11”.
2. On the right hand side, right click on your project name and select inset “module”.
3. Copy and paste the following code into the new module you just created:
Sub MakeChart()
Dim myRange As Range
Set myRange = Application.InputBox _
(Prompt:="Select chart inputs", Type:=8)
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=myRange, _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
End Sub
4. Click on the save button.
5. Click on the little Excel icon on the top right under the “File” menu to exit the VBA project window and to return to Excel.
6. Next run the macro by pressing “Alt F8” to bring up the list of macro’s available and selecting “MakeChart”.
The following subroutine will highlight all the duplicate values in range in yellow. It does not matter whether the values are text or numbers. It uses Excel’s COUNTIF function to count up the duplicates and then sets the colour to yellow:
Sub Highlight_Duplicates(Values As Range)
Dim Cell
For Each Cell In Values
If WorksheetFunction.CountIf(Values, Cell.Value) > 1 Then
Cell.Interior.ColorIndex = 6
End If
Next Cell
End Sub
And we have invoked it using a the click event:
Private Sub CommandButton1_Click()
Highlight_Duplicates (Sheets("Sheet1").Range("C10:F14"))
End Sub
and then if we press the button we see all the duplicates:
The following function will examine a range of Excel cells and return the number that fall within a specified range:
Function Count_Selected_Cells(Data_Range As Range, Min As Double, Max As Double) As Integer
Dim Cell
Count_Selected_Cells = 0#
For Each Cell In Data_Range
If ((Cell.Value >= Min) And (Cell.Value <= Max)) Then
Count_Selected_Cells = Count_Selected_Cells + 1
End If
Next Cell
End Function
It can be used as:
=Count_Selected_Cells(B5:F14,12,50)
Will return the number of cells having values between 12 and 50 in the range B5:F14:
The following function evaluates Acronyms from strings i.e it concatenates the first letter in every word in a string. E.g “trees are green” becomes “TAG”.
The routine traverses every character in a string and if it is a space then it takes the next character in the string. Before evaluating the string, it removes all trailing and duplicate spaces.
Function Acroymn (Original_String As String) As String Dim Trimmed_String As String Dim Length As Integer Dim Pos As Integer Trimmed_String = Application.WorksheetFunction.Trim(Original_String) 'work out the length of the string Length = Len(Trimmed_String) Acroymn = UCase(Left(Trimmed_String, 1)) For Pos = 2 To Length - 1 If (Mid(Trimmed_String, Pos, 1) = " ") Then Acroymn = Acroymn & UCase(Mid(Trimmed_String, Pos + 1, 1)) End If Next Pos End Function
So for example :
Acroymn (“British Broadcasting Corporation”) gives BBC
Acroymn (“Funky”) gives F
Acroymn (“”) gives NULL
Consider the following data table:
The standard Vlookup function within Excel has the following format:
VLOOKUP(“”Mark”, B6:G12”,2,FALSE)
Which will return “Brown”.
However, what about if we wanted to look up on 2 or more columns e.g the first name, last name and the age in the above table ? The following UDF allows us to do this:
Function ThreeParameterVlookup(Data_Range As Range, Col As Integer, Parameter1 As Variant, Parameter2 As Variant, Parameter3 As Variant) As Variant 'Declare Variables Dim Cell Dim Current_Row As Integer Dim No_Of_Rows_in_Range As Integer Dim No_of_Cols_in_Range As Integer Dim Matching_Row As Integer 'set answer to N/A by default ThreeParameterVlookup = CVErr(xlErrNA) Matching_Row = 0 Current_Row = 1 No_Of_Rows_in_Range = Data_Range.Rows.Count No_of_Cols_in_Range = Data_Range.Columns.Count 'Check if Col is greater than number of columns in range If (Col > No_of_Cols_in_Range) Then ThreeParameterVlookup = CVErr(xlErrRef) End If If (Col <= No_of_Cols_in_Range) Then Do If ((Data_Range.Cells(Current_Row, 1).Value = Parameter1) And _ (Data_Range.Cells(Current_Row, 2).Value = Parameter2) And _ (Data_Range.Cells(Current_Row, 3).Value = Parameter3)) Then Matching_Row = Current_Row End If Current_Row = Current_Row + 1 Loop Until ((Current_Row = No_Of_Rows_in_Range) Or (Matching_Row <> 0)) If Matching_Row <> 0 Then ThreeParameterVlookup = Data_Range.Cells(Matching_Row, Col) End If End If End Function
It has the following syntax:
ThreeParameterVlookup(Data_Range, Col , Parameter1, Parameter2 , Parameter3 )
Where:
• Data_Range is the range of the data
• Col is an integer for the required column
• Parameter1, Parameter2 and Parameter3 are the values from the first three columns respectively
So that:
=ThreeParameterVlookup(B6:G12,6,”Mark”,”Brown”,7) will return ”Tolworth” as this is a match on “Mark”, “Brown”, and 7 and a reference to the 6th column
Note that this function will also worked with (dynamic) named ranges as well:
=ThreeParameterVlookup(named_range,6,”Adrian”,”White”,7) will return “Chessington” where we have set up the named range “Named_Range”.
If Excel can’t locate a match then “N/A” is returned by default. In fact, the function assumes a value of N/A at the outset and then only changes when it finds an exact match.
Also if the value of Col exceeds the number of columns then a Reference error occurs.
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.
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
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.
The VBA Routine below allows the user to enter a string, and is then presented with the same string backwards. For example “Monday” becomes “yadnoM”:-
Option Explicit
Private Sub CommandButton1_Click()
'Define Variables
Dim Original_String As String
Dim Reversed_String As String
Dim Next_Char As String
Dim Length As Integer
Dim Pos As Integer
'Get the Original String
Original_String = InputBox("Pls enter the original string: ")
'Find the revised length of the string
Length = Len(Original_String)
'Set up the reversed string
Reversed_String = ""
'Progress through the string on a character by character basis
'Starting at the last character and going towards the first character
For Pos = Length To 1 Step -1
Next_Char = Mid(Original_String, Pos, 1)
Reversed_String = Reversed_String & Next_Char
Next Pos
MsgBox "The reversed string is " & Reversed_String
End Sub
The main features of the code are :-
• It needs a command button to activate the code – on the click event
• The following variables are set up :-
o Original_String – the original string that will be reversed (“Monday”)
o Reversed_String – the reversed string (“yadnoM”)
o Next_Char – the next character in the string that will be reversed
o Length – the length of the string
o Pos – the current position in the string
• An InputBox to capture the string. This is stored in the variable “Original String”
• The Length of the String is calculated using the LEN function and stored in the variable Length
• A FOR…NEXT loop is set up to go through the string starting at the last character and working backwards one character at a time
• The next character in the original string (working backwards) is then added to the reversed string
• This loop is iterated until we have traversed the entire length of the string
• The reversed string is displayed in a MsgBox.
Imagine that we have 20,000 rows of data in an Excel spreadsheet:
However, what about if you wanted to cut this data down such as take every 5th row ? This article shows how it can be done. The Row() function gives the number of each row:
The mod function allows us to perform modulo arithmetic:
Mod(Number,Divisor)
Gives the remainder when “Number” is divided by “Divisor”. So for example:
Mod(28,5)
Will give 3 the remainder when 28 is divided by 5.
So we can look at the row number and see what happens when we perform modulo arithmetic on it:
IF(Mod(Row(),5)=0,1,0)
i.e consider the remainder when the row number is divided 5. If the remainder is 0 then put a 1 in the cell otherwise put a zero. For convenience I have hardcoded the row numbers in the above table . Now we insert additional column to work out when this happens. The formulae will be the modula formulae as above:
We see that rows that have row numbers that are divisible by 5 have modulo 1. If we now FILTER the data to show only the 1’s:
If we now select Visible Cells only and then copy this data only, we have our subset of our data.
The following tutorial will describe how to create a chart in a cell like the one displayed in the table above under the “Trend” column.
The chart is created using a function called “CellChart”. You would enter it in Excel like any other standard function i.e. SUM, AVERAGE or VLOOKUP etc. This function is called a “User Defined Function” and is not a standard function available within Microsoft Excel. It must be created by the user using VBA.
When entered into Excel, the CellChart function looks like this:
Taking a closer look at the CellChart function, the range for the chart is defined in the first part of the function, C3:F3 in the example above. Next the color of the chart is defined, 203 using the example above.
Now for the VBA stuff
1. Enter the VBA project window by right clicking on a sheet name and selecting “View Code” or by selecting “ALT, F11”.
2. On the right hand side, right click on your project name and select inset “module”.
3. Copy and paste the following code into the new module you just created:
'Creates a new function called Cell Chart
Function CellChart(Plots As Range, Color As Long) As String
'Defines the variables that will be used later on in the code
Const cMargin = 2
Dim rng As Range, arr() As Variant, i As Long, j As Long, k As Long
Dim dblMin As Double, dblMax As Double, shp As Shape
'The following calculates the plots to be used for the chart
Set rng = Application.Caller
ShapeDelete rng
For i = 1 To Plots.Count
If j = 0 Then
j = i
ElseIf Plots(, j) > Plots(, i) Then
j = i
End If
If k = 0 Then
k = i
ElseIf Plots(, k) < Plots(, i) Then
k = i
End If
Next
dblMin = Plots(, j)
dblMax = Plots(, k)
'The next piece of code determines the shape and position of the chart
With rng.Worksheet.Shapes
For i = 0 To Plots.Count - 2
Set shp = .AddLine( _
cMargin + rng.Left + (i * (rng.Width - (cMargin * 2)) / (Plots.Count - 1)), _
cMargin + rng.Top + (dblMax - Plots(, i + 1)) * (rng.Height - (cMargin * 2)) / (dblMax - dblMin), _
cMargin + rng.Left + ((i + 1) * (rng.Width - (cMargin * 2)) / (Plots.Count - 1)), _
cMargin + rng.Top + (dblMax - Plots(, i + 2)) * (rng.Height - (cMargin * 2)) / (dblMax - dblMin))
'Difines what happens if there is an error
On Error Resume Next
j = 0: j = UBound(arr) + 1
On Error GoTo 0
ReDim Preserve arr(j)
arr(j) = shp.Name
Next
With rng.Worksheet.Shapes.Range(arr)
.Group
If Color > 0 Then .Line.ForeColor.RGB = Color Else .Line.ForeColor.SchemeColor = -Color
End With
End With
CellChart = ""
End Function
Sub ShapeDelete(rngSelect As Range)
'Defines the variables that will be used later on in the code
Dim rng As Range, shp As Shape, blnDelete As Boolean
For Each shp In rngSelect.Worksheet.Shapes
blnDelete = False
Set rng = Intersect(Range(shp.TopLeftCell, shp.BottomRightCell), rngSelect)
If Not rng Is Nothing Then
If rng.Address = Range(shp.TopLeftCell, shp.BottomRightCell).Address Then blnDelete = True
End If
If blnDelete Then shp.Delete
Next
End Sub
4. Click on the save button.
5. Click on the little Excel icon on the top right under the “File” menu to exit the VBA project window and to return to Excel
6. Enter the CellChart function into any cell as displayed above.
7. See the attached workbook for a working example of the above.
For further information on this type of in cell charting, please visit:
http://www.dailydoseofexcel.com/archives/2006/02/05/in-cell-charting/
Awesome! The first remix of one of my spreadsheets: Backlink Checker for Google and Yahoo!
Claus has taken my Google API Keyword Spreadsheet, gave it a facelift, and added Yahoo backlink results side-by-side with the Google results.
For a large screenshot click here, or view the top ten results for the search Automate Excel with their Google and Yahoo backlinks in this screenshot:
The remixed version is the result of the nice folks at ResearchBuzz posting a link which was then picked up by ThreadWatch, who requested to see a Yahoo version also. Cluas then ran with it.
Note 1: A Yahoo developer key is not required. Notice in the large screenshot I entered foobar for the Yahoo API key and got the same results. I believe they are still throttling 5000 queries/day by IP (Thank You Yahoo).
Note 2: The VBA code requires a password, so any curious coders are out of luck, Claus? (You can download the original to see the google query. Not that a vba password has stopped anybody before 
Note 3: If any SEO gurus care to do a follow up post on how to use this data in keyword research, that would be great!
Note 4: The spreadsheets I make available for download are free to build upon provided a link and proper credit is given, and they’re not sold. Any questions, simply shoot me an email.
Note 5: Woops, getting carried away with the notes… Nice job Claus.
A DomDocument is a container (variable/object) for holding an XML document in your VBA code.
Just as you use a String variable to hold a strings value, you can use a DomDocument to hold an XML document.
(for a complete list of a DomDocuments properties, see halfway down this page)
Why do I care what a DomDocument is?
Excel has some very intuitive ways for moving XML into a spreadsheet (XML Maps), but:
•What if you want to manipulate the data after you’ve retrieved it, but before the data appears in your cells?
•What if you want to import XML data into controls instead of mapped cells, like comboboxes, labels, or textboxes?
Both of these tasks are difficult (if not impossible) to accomplish using XML maps. We can however import the XML data to a DomDocument, then pull out the data we need, write to controls, filter the data, or manipulate the data before it appears in a spreadsheet.
How do I Load XML into a DomDocument?
The following example uses Excel 2003. In the Visual Basic Editor Goto Tools->References and place a checkmark in the box for “Microsoft XML v5.0″
Now we need some XML. Recently I created a Google sitemap for this site, and it’s in XML, so lets use that: AutomateExcel Google XML Sitemap
To load my SiteMap XML document into a DomDocument object in Excel, use the following code (which is commented explaining things):
Sub DomDocumentBasic()
Dim oDom As MSXML2.DOMDocument
'Create the DomDocument Object
Set oDom = CreateObject("MSXML2.DOMDocument")
'Load entire Document before moving on
oDom.async = False
'Don't Validate
oDom.validateOnParse = False
oDom.Load ("http://www.automateexcel.com/sitemap.php")
MsgBox oDom.XML
End Sub
The code simply loads the XML and displays it in a message box:
Note: If you load XML from a URL and are having trouble, make sure you didn’t forget the line “oDom.async = False“.
How do I traverse the DomDocument?
Now that I’ve got the XML data in an object, how do I “do something” with it?
LOL, I’m still learning this part. I’ll post the basics in a future post and drop a link to it here.
The post title was “What is a DomDocument”, hopefully you have an idea now.
MS has a new KB article with some basic code examples for filling an array with spreadsheet data, and writing data to a spreadsheet from an array:
If you always know the length of a string, it’s easy to remove characters from it. Example: If you have a string that is 10 characters and you want to remove 1 character from the Left side, simply return the right 9 characters:
msgbox Right(Mystring, 9)
This doesn’t work for a variable length string, or one which you don’t know beforehand it’s length. In this case you can use the formula (Length - N) to designate how many characters to extract:
MsgBox Right(Mystring, Len(Mystring) - 1)
Where 1 is the number of characters to remove from the left side of the string. This will return the string minus the left most character.
To remove characters from the right side of a string, replace Right with Left
