These days dynamic arrays, power query and DAX seem to be all the rage. But let’s not forget about an old friend: VBA UDFs (User Defined Function).

 

VBA is still fun

A UDF is a special kind of VBA. It allows you to create functions! Let’s start with an example.

 

The Challenge

Here we shared formula solutions to Leila’s challenge: look for the App in range B5:D45, get column header B4:D4.  See how Geraldo’s GetDiv UDF solves this!

 

 

 

 

 

 

 

 

 

 

 

 

Geraldo’s UDF

Geraldo shared this UDF solution:  (Download zipped Excel file)

Function GetDiv(LookupVal As String, Titles As Range, SearchArea As Range) As String
GetDiv = Cells(Titles.Row, SearchArea.Find(LookupVal).Column)
End Function

Geraldo’s function used in cell L5:

=GetDiv(H5,$B$4:$D$4,$B$5:$D$45)

His explanation helped me understand:

The function expects 3 parameters GetDiv ( LookupVal , Titles , SearchArea ) as below:
1st LookupVal is the value we are looking for ($H5 for the first App name)
2nd Titles is the range where we have the Divisions’ names: $B$4:$D$4
3rd SearchArea is the area where we’ll look for the LookupVal: $B$5:$D$45

Initially I thought SearchArea was a vba property(it’s a name for where we look for the App).

Now let’s explore further by playing with the code!

 

This part SearchArea.Find(LookupVal).Column seemed odd so in sheet ‘test Geraldo’s udf‘ I started playing with some vba.

How does Range.Cells property work? (SearchArea is a range parameter)

In a basic form it needs two numbers: one for a row, one for a column.

This code assigns a text value to cell E3:

‘provide simple co-ordinates to Cells
Sub Assign_Text_To_A_Cell()
Cells(3, 5).Value = “assign text to a single cell”
End Sub

Cell E3 (row 3, column 5) now has text “assign text to a single cell”.

 

We could also extract the existing value from a cell:

‘extract the value from a cell and display it in a message box
Sub Extract_and_Display_value()
Dim getvalue As String
getvalue = Cells(9, 3).Value
MsgBox (getvalue)
End Sub

 

There are many possible applications of Range.Cells! Let’s go back to Geraldo’s code:

Cells(Titles.Row, SearchArea.Find(LookupVal).Column)

Titles.Row is range B4:D4. SearchArea.Find(LookupVal).Column provides a column number so we know which text from B4:D4 to retrieve.

 

Another way to explain  SearchArea.Find(LookupVal).Column is:

In range G16:I18 what is the column number where we find the word “fish”?

 

 

 

 

 

This code displays the column number where “fish” is found:

‘Let’s examine syntax: SearchArea.Find(LookupVal).Column from UDF GetDiv
Sub examinesyntax()
Dim LookupVal As String, SearchArea As Range, GetColumnNumber As Integer
LookupVal = “fish”
‘SearchArea = Range(“G16:I18”) <–wrong syntax. Google search helped to create next line
Set SearchArea = Sheets(“test Geraldo’s udf”).Range(“G16:I18”)
GetColumnNumber = SearchArea.Find(LookupVal).Column
‘Had to google for help with MsgBox syntax
MsgBox (“What is the column number where we find the word fish?” & vbCrLf & “It’s column ” & GetColumnNumber & “.”)
End Sub

Building the code really helped me understand SearchArea.Find(LookupVal).Column.

 

Ready for more vba fun?!

Let’s see how the code behind the right button works (show column number for random item) !

 

 

 

 

 

 

 

In this case the random word is “orange”.

Rerun to see different random word.

 

 

Here’s the code behind the message box:

‘Add more functionality: select random word, show col number
Sub examinesyntaxv2()
Dim LookupVal As String, SearchArea As Range, GetColumnNumber As Integer, Randr As Integer, Randc As Integer, Lookupword As String
Randr = WorksheetFunction.RandBetween(16, 18) ‘random row
Randc = WorksheetFunction.RandBetween(7, 9)     ‘random column
Lookupword = Cells(Randr, Randc).Value
‘SearchArea = Range(“G16:I18”) <–wrong syntax. Google search helped to create next line
Set SearchArea = Sheets(“test Geraldo’s udf”).Range(“G16:I18”)
GetColumnNumber = SearchArea.Find(Lookupword).Column
‘Had to google for help with MsgBox syntax
MsgBox (“What is the column number where we find the word ” & Lookupword & “?” & vbCrLf & “It’s column ” & GetColumnNumber & “.”)
End Sub

Now you won’t always get “fish” 🙂 Notice that vbCrLf creates a carriage return in the MsgBox.

 

Practice!

The knowledge doesn’t fall from the sky. I have to practice! Necessity and curiosity (and caffeine) fuel my learning but there’s a lot of tinkering, many google searches for syntax ideas and some grit to get the code just right 🙂

 

About Geraldo

By the way…who is Geraldo??? Who is the person behind the GetDiv UDF?

 

Geraldo is a Data Analyst from São Paulo Brazil.

He loves challenges and creating solutions to solve them!

 

Geraldo has worked in Finance and knows his way around Excel (pivots, formulas, vba), SQL, Cobol, DBase III, VBS, ASP/XML and now the new stuff: Power BI (M, Dax) with Python being his next thing to conquer! An amazing set of skills!

Thanks for the UDF Geraldo and I look forward to learning more from you!

 

Learn More VBA!

Learn, build stuff, repeat. That’s it! You can learn a lot of vba on your own but there are many tips and nuances that you can learn from a professional. More structured learning often helps get past certain hurdles.

How you learn is up to you (books, videos, etc). Here are some suggestions:

• Paul Kelley’s website
• Leila Gharani’s YouTube channel
• Jon Acampora’s How to write a UDF
• Dan Strong’s YouTube channel
• Dinesh Kumar Takyar’s YouTube channel
• Sumit Bansal’s Guide to creating a UDF

 

About Me

My name is Kevin Lehrbass. I’m a Data Analyst living in Markham Ontario Canada.

I often spend hours playing with Microsoft Excel but eventually a few things happen:

• My dogs get mad at me (they want to play)
• I need more coffee
• There’s a strange pain in my stomach. Oh yeah…food.

I have two wonderful dogs Cali and Fenton. Here you see Cali demanding that I take a break from the spreadsheet. We’ll go play in the backyard for awhile and then read a book (Excel or Power BI) on the couch 🙂