Swipe to show menu

VBA ships with several hundred built-in functions, and you'll never use most of them. The ones below come up so often in business automation — cleaning up text, working with dates, and converting types.

Date Functions

Debug.Print Now()                    ' current date and time
Debug.Print Date                     ' today's date only
Debug.Print DateAdd("d", 30, Date)   ' 30 days from today
Debug.Print DateDiff("d", #01/09/2023#, Date)  ' days since hire date
Debug.Print Format(Date, "dd/mm/yyyy")

Now() and Date look similar but answer different questions — Now() includes the current time down to the second (useful for timestamping when a macro ran), while Date gives you just the calendar day, which is what you want for anything involving due dates or ages. DateAdd and DateDiff are mirror images of each other: DateAdd projects forward or backward from a date by a given unit ("d" for days, but "m" for months and "yyyy" for years also work), while DateDiff measures the gap between two existing dates.

Note

VBA date literals — anything written between # symbols, like #01/09/2023# — always use MM/DD/YYYY order internally, regardless of your Windows regional settings or system locale. So #01/09/2023# means January 9, 2023, not September 1st, even for learners whose everyday date format is DD/MM/YYYY. This is different from how dates are typed into a worksheet cell, where regional settings do apply — the #...# literal format is a VBA-specific rule that stays fixed no matter where the code runs.

Text Functions

Debug.Print UCase("wireless mouse")     ' WIRELESS MOUSE
Debug.Print LCase("WIRELESS MOUSE")     ' wireless mouse
Debug.Print Left("P001", 1)             ' P
Debug.Print Right("P001", 3)            ' 001
Debug.Print Mid("P001", 2, 3)           ' 001
Debug.Print Trim("  Keyboard  ")        ' Keyboard
Debug.Print Len("Laptop Stand")         ' 12
Debug.Print InStr("Wireless Mouse", "Mouse")  ' 10 (position found)

UCase and LCase are the two you'd reach for to make a comparison case-insensitive, for example, If UCase(category) = UCase("electronics") Then works regardless of how the category was originally typed. Left, Right, and Mid all extract a piece of a string, differing only in where they start counting from: Left and Right count from the two ends, while Mid takes a starting position and a length, which is why Mid("P001", 2, 3) and Right("P001", 3) happen to return the same thing here — "P001" only has one digit-prefix character, so "starting from position 2" and "the last 3 characters" land on the same substring. Trim quietly removes leading and trailing spaces — an unglamorous function that saves you constantly when data has been copy-pasted from somewhere with inconsistent spacing. InStr searches for one string inside another and returns the position where it's found (or 0 if it isn't there at all), which is how you'd test "does this product name contain the word Mouse?" without needing an exact match.

Numeric Functions

Debug.Print Round(24.996, 2)   ' 25
Debug.Print Abs(-15)           ' 15
Debug.Print Int(7.9)           ' 7

Round and Int both shrink a number, but not the same way — Round(24.996, 2) rounds to the nearest value at 2 decimal places (25.00, which displays as 25), while Int(7.9) always truncates down toward zero regardless of how close the decimal is to rounding up, giving 7 rather than 8. Mixing these up is a common source of pricing bugs: rounding a price to the nearest cent should almost always use Round, not Int, or you'll systematically undercharge by a fraction of a cent on every transaction.

Type Conversion

Critical when pulling values off a worksheet, since cell values often arrive as Variant:

Dim priceText As String
priceText = "39.50"
Dim price As Double
price = CDbl(priceText)     ' text → number
 
Dim stockValue As Variant
stockValue = "44"
Dim stockCount As Integer
stockCount = CInt(stockValue)

CDbl converts to Double, CInt to Integer, CStr converts to String and CDate converts to a proper Date value.

Why not just let VBA convert automatically when needed? It usually will, but relying on that is fragile: a cell that looks like a number but was actually typed or imported as text can cause an If comparison or an arithmetic operation to fail or behave unexpectedly, and explicit conversion is what catches that early, with an error at the exact line where the bad data lives rather than three steps downstream.

Task

Write a line that extracts just the numeric part of "P003" using Mid (hint: start at position 2).
Write a line that converts that extracted text into a real number with CInt or CLng.
Use DateDiff to calculate how many days ago Emma Davis was hired (15/03/2020) compared to today.

Hint

1. Extracting the numeric part

Mid takes three pieces: the string itself, where to start counting from, and how many characters to grab.
"P003" has one letter followed by three digits — you want to skip past the "P" and grab everything after it.
Starting position 2 means "begin at the 2nd character" — count "P003" out on your fingers to confirm position 2 is the first "0".

2. Converting to a real number

Whatever Mid gives back is still text, even though it looks like a number.
Wrap the entire Mid(...) expression inside CInt(...) or CLng(...) — you're converting the result of one function using another.
Reminder from earlier in the chapter: CInt/CLng convert text to a whole number; use CLng if you're not sure the number could ever be large.

3. Days since the hire date

DateDiff needs three things: what unit to measure in ("d" for days), a start date, and an end date.
The tricky part is writing a literal date in VBA — wrap it in # symbols, like #03/15/2020#. Inside #...#, VBA always expects month/day/year order, regardless of your regional settings — so March 15 is #03/15/2020#, not #15/03/2020#.
For "today," you don't need to type a date at all — there's a keyword from section 2.3 that always returns the current date.
Put those two dates into DateDiff in the right order — the earlier date first — or you'll get a negative number back.

Everything was clear?

Thanks for your feedback!

Section 2. Chapter 3

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Using Built-in Functions