Category Archives: Functions

Use Any Formula: Worksheetfunction in VBA

Formulas in EXCEL Microsoft Excel offers several built-in functions that can be used as formulas in Excel worksheets. It displays the categories of functions available in the Insert Function window, as seen in the image below: Usage of Formula in an Excel Sheet Just like

Using Exit Function in VBA

To start with, make sure you have a good understanding of what a function is and how it works. You can get a great overview in my articles here: What is “You’ve Entered too Many Arguments”? How Do You Fix “Compile Error: Argument not optional”?

Call Sub vs. Functions in VBA

Introduction In the MS Excel’s Visual Basic Editor, Sub is a statement, like a function fun(), that performs a specific task. A sub is a piece of code that performs a specific task and does not return a result. The purpose of a subroutine is to

Cross Products with Excel and VBA

Microsoft Excel does not have the built-in worksheet functions to calculate the cross product of two vectors. Therefore, some techniques are required to do the calculation — either by setting up your own formula in a worksheet or by creating a custom VBA function. The

Using the UCase Function in Excel VBA

There may be a time in Excel that you need data that you are fetching from an Excel spreadsheet or importing from external data to be amended to upper case.  This can, of course, be done manually, but if it is a large amount of

How to Use the VBA Replace Function

The VBA Replace function is a function which you can use in your code to replace one set of string characters with another set of string characters, or to replace a set of string characters with an empty string. For example, you may have a

IsNumeric in VBA: 8 Ways to Use the Function

In VBA, to test whether an expression is a number, the IsNumeric function can be used. Description The IsNumeric function evaluates whether the input expression is a number and returns a Boolean value (TRUE or FALSE). It returns True if the entire expression is a number; otherwise, it returns False. Syntax IsNumeric(expression) The

Rounding with VBA and Excel: Functions and Gotchas

VBA is often used for calculations – when you create these calculations, you can end up with a number that has multitude of decimal places.  The Round function in VBA lets you round the number to a set amount of decimal points, or to remove

How to Use ByVal in VBA

Using the ByVal argument in VBA is a way to pass values to a function in VBA while retaining the original value of the variable. For example, you may have a list of names and dates of birth in your Excel sheet, and you wish