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 A1 and first check whether or not it’s numeric. This helps avoid type mismatch errors. After that, we determine the sign of the value using the VBA Sgn function in a series of conditional 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.