Every once in a while, I like to post a tutorial about a VBA function that’s rarely used. This is one of those tutorials. The VBA Sgn function is a quick way to check if a number is positive, negative or zero. It’s very simple and returns an integer based on the sign of the number you’re testing.
- If the number is greater than zero, Sgn returns 1
- If the number equals zero, Sgn returns 0
- If the number is less than zero, Sgn returns -1
If you try executing the Sgn function on a value that isn’t a number, like a string, you’ll get a type mismatch so it’s a good idea to pair it with the VBA IsNumeric function, which is what we’re going to do in the following example:
VBA Sgn Function Example Macro
Sub SgnFunctionDemo() myval = Range("A1") If IsNumeric(myval) Then If Sgn(myval) = 1 Then MsgBox ("Cell A1 is positive") ElseIf Sgn(myval) = 0 Then MsgBox ("Cell A1 is 0 or blank") ElseIf Sgn(myval) = -1 Then MsgBox ("Cell A1 is negative") End If Else MsgBox ("Cell A1 is not numeric") End If End Sub
In this macro, we grab the value from cell
If statements. You could use this structure to take different actions based on the sign of the cell, or even to force all values to be either negative or positive. Granted, you could just use the VBA Abs function for that, but there’s always more than one way to do something in VBA.
If you’re serious about learning VBA, consider subscribing using the form below. I’ll send a VBA tutorial each week to help you master both simple and challenging VBA concepts.