Instant Print in the Immediate Window

Use the “?” preceding a line of code that can evaluate to a value in the VBE Immediate Window. Hit Enter to evalute the code in the next line. See example below:

This is a VBA Trick I was not aware for a lot time but saves a lot of time when debugging code or trying to evaluate a single line or bit of code without needing to debug a whole functions or procedure.

Evaluate formulas shortcut

One of my favorite VBA Trick is using the evaluate function using square brackets “[]”. If you want to read more read my VBA Evaluate article.

Evaluate works both on:

• Evaluation of Named Ranges
• Evaluation of Excel Worksheet Functions

Evaluating Named Ranges in VBA

Here’s an example of Named Range evaluation in VBA:

Evaluating Worksheet Functions in VBA

Here’s an example of Worksheet Function evaluation in VBA:

This is one of the most useful trick as VBA does not allow you to otherwise easily do a lookup of data is a worsheet.

Use VBA. to view all VBA Built in functions and constants

Often you stumble not being able to remind yourself of that one useful VBA Function and need to search the function online. A great VBA Trick is to type in “VBA.” and then you will be provided with a list of available VBA Functions and Constants.

User Defined Functions

Excel has a multitude of functions, half of which you probably don’t know. However, what happens when you lack a particular function or want to save time bundling up common functions? Let me introduce you to the little known world of User Defined Functions (UDF).

Simple UDF

A simple UDF can be found below:

Advanced UDF

Let us not try a more advanced UDF showing how powerful it can be. First I will create a simple table:

Now what I need is to somehow get a number of matches or these records against various criteria e.g. sex, sales, age, country. One way would be to go for a Pivot Table but sometimes a simple UDF can be more useful especially if we want to embed this value in your calculations.

So I will create a UDF that takes these arguments and provides the number of matches:

Public Function GetMatchesUDF(r As Range, sex As String, age As Long, sales As Double, country As String)
    Dim matches As Long, i As Long, rRow As Range
    For i = 1 To r.Rows.Count
        Set rRow = r.Rows(i + 1)
        If rRow.Cells(, 3) <> sex Or rRow.Cells(, 4) < age Or rRow.Cells(, 5) < sales Or rRow.Cells(, 7) <> country Then GoTo NextItem
        matches = matches + 1
NextItem:
    Next i
    GetMatchesUDF = matches
End Function

Now we can use the UDF Function in Excel:

Speeding up your VBA Macros

Your macro running slow? A lot of CPU overhead goes into updating your screen in case and recalculations of formulas impacted by your macros. With a simple VBA Trick you can turn off ScreenUpdating and Calculations until your macro is done.

Application.ScreenUpdating = False
Application.Calculation = xlManual
'****Your code here****
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

What to do when your macros needs to run for a long time and you need to update your screen every now and then? Use the DoEvents procedure to refresh your screen only in selected intervals.
Read here how learn of more ways of optimizing your VBA performance.