Sgn Function

Returns an Integer indicating the sign of a number.

Syntax

Sgn(number)

Parameters

number - Required. Any valid numeric expression.

Return Value

Returns an Integer indicating the sign of the number: - Returns -1 if number is negative (less than zero) - Returns 0 if number is zero - Returns 1 if number is positive (greater than zero)

Remarks

The Sgn function is used to determine the sign (positive, negative, or zero) of a numeric expression. It is particularly useful for: - Determining the direction of change in values - Implementing sign-dependent logic without complex conditionals - Comparing signs of two numbers - Normalizing values to -1, 0, or 1 - Mathematical algorithms requiring sign information

The sign of number determines the return value: - If number > 0, Sgn returns 1 - If number = 0, Sgn returns 0 - If number < 0, Sgn returns -1

If number is Null, Sgn returns Null.

The Sgn function is often used in combination with Abs to separate magnitude from direction: - Abs(number) gives the magnitude (absolute value) - Sgn(number) gives the direction (sign) - number = Abs(number) * Sgn(number) (reconstruction)

Typical Uses

Direction Detection: Determine if a value is increasing or decreasing
Sign Comparison: Compare signs of two numbers
Conditional Logic: Simplify sign-based branching
Mathematical Operations: Algorithms requiring sign information
Data Validation: Check if values are positive, negative, or zero
Trend Analysis: Determine direction of change in time series
Game Logic: Movement direction, score changes
Financial Calculations: Profit/loss direction, balance changes

Basic Examples

' Example 1: Basic sign detection
Dim result As Integer
result = Sgn(10)      ' Returns 1 (positive)
result = Sgn(-5.5)    ' Returns -1 (negative)
result = Sgn(0)       ' Returns 0 (zero)

' Example 2: Determine direction of change
Dim oldValue As Double
Dim newValue As Double
Dim direction As Integer

oldValue = 100
newValue = 120
direction = Sgn(newValue - oldValue)  ' Returns 1 (increasing)

If direction = 1 Then
MsgBox "Value increased"
ElseIf direction = -1 Then
MsgBox "Value decreased"
Else
MsgBox "No change"
End If

' Example 3: Compare signs of two numbers
Dim a As Double
Dim b As Double

a = -15
b = -20

If Sgn(a) = Sgn(b) Then
MsgBox "Same sign"
Else
MsgBox "Different signs"
End If

' Example 4: Normalize to unit sign
Dim value As Double
Dim unitSign As Integer

value = 42.7
unitSign = Sgn(value)  ' Returns 1
' Now unitSign can be used as a multiplier: 1, 0, or -1

Common Patterns

Pattern 1: `GetChangeDirection`

Determine if value increased, decreased, or stayed same

Function GetChangeDirection(oldVal As Double, newVal As Double) As String
Dim direction As Integer
direction = Sgn(newVal - oldVal)

Select Case direction
Case 1
GetChangeDirection = "Increased"
Case -1
GetChangeDirection = "Decreased"
Case 0
GetChangeDirection = "No Change"
End Select
End Function

Pattern 2: `SameSign`

Check if two numbers have the same sign

Function SameSign(a As Double, b As Double) As Boolean
' Both zero, or both have same non-zero sign
If a = 0 And b = 0 Then
SameSign = True
Else
SameSign = (Sgn(a) = Sgn(b))
End If
End Function

Pattern 3: `OppositeSign`

Check if two numbers have opposite signs

Function OppositeSign(a As Double, b As Double) As Boolean
OppositeSign = (Sgn(a) = -Sgn(b)) And (a <> 0) And (b <> 0)
End Function

Pattern 4: `SignMultiplier`

Get sign as multiplier for calculations

Function SignMultiplier(value As Double) As Integer
SignMultiplier = Sgn(value)
' Returns: -1, 0, or 1 which can be used in calculations
End Function

Pattern 5: `ClampToSign`

Ensure value has specific sign

Function ClampToSign(value As Double, requiredSign As Integer) As Double
If Sgn(value) <> requiredSign Then
ClampToSign = Abs(value) * requiredSign
Else
ClampToSign = value
End If
End Function

Pattern 6: `SignString`

Convert sign to string representation

Function SignString(value As Double) As String
Select Case Sgn(value)
Case 1
SignString = "+"
Case -1
SignString = "-"
Case 0
SignString = "0"
End Select
End Function

Pattern 7: `CompareBySign`

Three-way comparison using sign

Function CompareBySign(a As Double, b As Double) As Integer
' Returns: -1 if a < b, 0 if a = b, 1 if a > b
CompareBySign = Sgn(a - b)
End Function

Pattern 8: `CountBySign`

Count positive, negative, and zero values

Sub CountBySign(arr() As Double, ByRef positive As Long, _
ByRef negative As Long, ByRef zero As Long)
Dim i As Long
positive = 0
negative = 0
zero = 0

For i = LBound(arr) To UBound(arr)
Select Case Sgn(arr(i))
Case 1
positive = positive + 1
Case -1
negative = negative + 1
Case 0
zero = zero + 1
End Select
Next i
End Sub

Pattern 9: `TrendDirection`

Determine overall trend in series

Function TrendDirection(values() As Double) As String
Dim i As Long
Dim changes As Long
Dim positiveChanges As Long
Dim negativeChanges As Long

For i = LBound(values) + 1 To UBound(values)
Dim change As Integer
change = Sgn(values(i) - values(i - 1))

If change = 1 Then positiveChanges = positiveChanges + 1
If change = -1 Then negativeChanges = negativeChanges + 1
Next i

If positiveChanges > negativeChanges Then
TrendDirection = "Upward"
ElseIf negativeChanges > positiveChanges Then
TrendDirection = "Downward"
Else
TrendDirection = "Stable"
End If
End Function

Pattern 10: `ApplySignTo`

Apply sign of one number to another

Function ApplySignTo(magnitude As Double, signSource As Double) As Double
' Take absolute value of magnitude and apply sign from signSource
ApplySignTo = Abs(magnitude) * Sgn(signSource)
End Function

Advanced Usage

Example 1: `ChangeAnalyzer` Class

Analyze changes in data series with trend detection

' Class: ChangeAnalyzer
Private m_values() As Double
Private m_count As Long

Public Sub Initialize(initialCapacity As Long)
ReDim m_values(1 To initialCapacity)
m_count = 0
End Sub

Public Sub AddValue(value As Double)
m_count = m_count + 1

If m_count > UBound(m_values) Then
ReDim Preserve m_values(1 To m_count * 2)
End If

m_values(m_count) = value
End Sub

Public Function GetChangeDirection(index As Long) As Integer
' Returns direction of change at index
If index < 2 Or index > m_count Then
GetChangeDirection = 0
Else
GetChangeDirection = Sgn(m_values(index) - m_values(index - 1))
End If
End Function

Public Function GetOverallTrend() As String
Dim i As Long
Dim upCount As Long
Dim downCount As Long

For i = 2 To m_count
Select Case Sgn(m_values(i) - m_values(i - 1))
Case 1
upCount = upCount + 1
Case -1
downCount = downCount + 1
End Select
Next i

If upCount > downCount Then
GetOverallTrend = "Upward Trend"
ElseIf downCount > upCount Then
GetOverallTrend = "Downward Trend"
Else
GetOverallTrend = "No Clear Trend"
End If
End Function

Public Function GetConsecutiveChanges() As Long
' Find longest sequence of consecutive changes in same direction
Dim i As Long
Dim currentDirection As Integer
Dim currentCount As Long
Dim maxCount As Long

If m_count < 2 Then
GetConsecutiveChanges = 0
Exit Function
End If

currentDirection = Sgn(m_values(2) - m_values(1))
currentCount = 1
maxCount = 1

For i = 3 To m_count
Dim direction As Integer
direction = Sgn(m_values(i) - m_values(i - 1))

If direction = currentDirection And direction <> 0 Then
currentCount = currentCount + 1
If currentCount > maxCount Then maxCount = currentCount
Else
currentDirection = direction
currentCount = 1
End If
Next i

GetConsecutiveChanges = maxCount
End Function

Public Function GetTrendStrength() As Double
' Returns value between -1 and 1 indicating trend strength
' 1 = strong upward, -1 = strong downward, 0 = no trend
Dim i As Long
Dim sumSign As Long
Dim changes As Long

For i = 2 To m_count
Dim sign As Integer
sign = Sgn(m_values(i) - m_values(i - 1))
If sign <> 0 Then
sumSign = sumSign + sign
changes = changes + 1
End If
Next i

If changes > 0 Then
GetTrendStrength = CDbl(sumSign) / CDbl(changes)
Else
GetTrendStrength = 0
End If
End Function

Example 2: `SignComparator` Module

Compare and analyze signs in numeric data

' Module: SignComparator

Public Function AllSameSign(values() As Double) As Boolean
' Check if all values have the same sign
Dim i As Long
Dim firstSign As Integer

If UBound(values) < LBound(values) Then
AllSameSign = True
Exit Function
End If

' Get first non-zero sign
firstSign = 0
For i = LBound(values) To UBound(values)
firstSign = Sgn(values(i))
If firstSign <> 0 Then Exit For
Next i

' Check all others
For i = LBound(values) To UBound(values)
If Sgn(values(i)) <> 0 And Sgn(values(i)) <> firstSign Then
AllSameSign = False
Exit Function
End If
Next i

AllSameSign = True
End Function

Public Function GetSignCounts(values() As Double) As String
Dim i As Long
Dim posCount As Long
Dim negCount As Long
Dim zeroCount As Long

For i = LBound(values) To UBound(values)
Select Case Sgn(values(i))
Case 1
posCount = posCount + 1
Case -1
negCount = negCount + 1
Case 0
zeroCount = zeroCount + 1
End Select
Next i

GetSignCounts = "Positive: " & posCount & _
", Negative: " & negCount & _
", Zero: " & zeroCount
End Function

Public Function AlternatingSign(values() As Double) As Boolean
' Check if signs alternate (ignoring zeros)
Dim i As Long
Dim lastSign As Integer

For i = LBound(values) To UBound(values)
Dim currentSign As Integer
currentSign = Sgn(values(i))

If currentSign <> 0 Then
If lastSign <> 0 And currentSign = lastSign Then
AlternatingSign = False
Exit Function
End If
lastSign = currentSign
End If
Next i

AlternatingSign = True
End Function

Public Function SignTransitions(values() As Double) As Long
' Count how many times the sign changes
Dim i As Long
Dim transitions As Long
Dim lastSign As Integer

For i = LBound(values) To UBound(values)
Dim currentSign As Integer
currentSign = Sgn(values(i))

If currentSign <> 0 Then
If lastSign <> 0 And currentSign <> lastSign Then
transitions = transitions + 1
End If
lastSign = currentSign
End If
Next i

SignTransitions = transitions
End Function

Example 3: `DirectionIndicator` Class

Track and display directional changes with symbols

' Class: DirectionIndicator
Private m_lastValue As Double
Private m_initialized As Boolean

Public Sub SetInitialValue(value As Double)
m_lastValue = value
m_initialized = True
End Sub

Public Function UpdateAndGetSymbol(newValue As Double) As String
If Not m_initialized Then
m_lastValue = newValue
m_initialized = True
UpdateAndGetSymbol = "―"  ' Neutral symbol
Exit Function
End If

Dim direction As Integer
direction = Sgn(newValue - m_lastValue)

Select Case direction
Case 1
UpdateAndGetSymbol = "▲"  ' Up arrow
Case -1
UpdateAndGetSymbol = "▼"  ' Down arrow
Case 0
UpdateAndGetSymbol = "―"  ' Neutral
End Select

m_lastValue = newValue
End Function

Public Function GetDirection() As String
' Not implemented - would require storing current value
GetDirection = "N/A"
End Function

Public Function GetChangeText(newValue As Double) As String
If Not m_initialized Then
GetChangeText = "Initial Value"
Else
Dim change As Double
Dim direction As Integer

change = newValue - m_lastValue
direction = Sgn(change)

Select Case direction
Case 1
GetChangeText = "Increased by " & Abs(change)
Case -1
GetChangeText = "Decreased by " & Abs(change)
Case 0
GetChangeText = "No Change"
End Select
End If
End Function

Public Sub Reset()
m_initialized = False
m_lastValue = 0
End Sub

Example 4: `MathSignHelper` Module

Mathematical operations using sign function

' Module: MathSignHelper

Public Function CopySign(magnitude As Double, signSource As Double) As Double
' Copy the sign from signSource to magnitude
' Similar to copysign() in C math library
CopySign = Abs(magnitude) * Sgn(signSource)
End Function

Public Function RoundAwayFromZero(value As Double) As Long
' Round away from zero (ceiling for positive, floor for negative)
Dim sign As Integer
sign = Sgn(value)

If sign >= 0 Then
RoundAwayFromZero = -Int(-value)  ' Ceiling
Else
RoundAwayFromZero = Int(value)    ' Floor
End If
End Function

Public Function RoundTowardZero(value As Double) As Long
' Round toward zero (floor for positive, ceiling for negative)
RoundTowardZero = Fix(value)
End Function

Public Function StepInDirection(value As Double, stepSize As Double, _
direction As Integer) As Double
' Step in the direction indicated by sign (-1, 0, or 1)
StepInDirection = value + (Abs(stepSize) * direction)
End Function

Public Function Clamp(value As Double, minVal As Double, maxVal As Double) As Double
' Clamp value between min and max
If value < minVal Then
Clamp = minVal
ElseIf value > maxVal Then
Clamp = maxVal
Else
Clamp = value
End If
End Function

Public Function SignedMin(a As Double, b As Double) As Double
' Return the value with smaller absolute value, preserving sign
If Abs(a) < Abs(b) Then
SignedMin = a
Else
SignedMin = b
End If
End Function

Public Function SignedMax(a As Double, b As Double) As Double
' Return the value with larger absolute value, preserving sign
If Abs(a) > Abs(b) Then
SignedMax = a
Else
SignedMax = b
End If
End Function

Public Function CompareNumbers(a As Double, b As Double) As Integer
' Three-way comparison: -1 if a<b, 0 if a=b, 1 if a>b
CompareNumbers = Sgn(a - b)
End Function

Error Handling

The Sgn function can generate the following errors:

Error 13 (Type mismatch): Argument cannot be interpreted as numeric
Error 94 (Invalid use of Null): If Null is passed and not properly handled

Always use error handling when working with user input or uncertain data:

On Error Resume Next
result = Sgn(userValue)
If Err.Number <> 0 Then
MsgBox "Invalid numeric value"
End If

Performance Considerations

Sgn is very fast (simple comparison operation)
Much faster than If...Then...Else chains for sign checking
Use Sgn instead of multiple comparisons when appropriate
No performance penalty for any numeric type
Ideal for use in tight loops

Best Practices

Use for Comparisons: Prefer Sgn(a - b) over complex If statements for three-way comparison
Handle Zero Case: Remember that zero has its own return value (0)
Type Safety: Ensure argument is numeric to avoid Type Mismatch error
Null Handling: Check for Null if input might be Null
Sign vs. Value: Don't confuse sign with actual value
Combine with Abs: Use together to separate magnitude and direction
Clear Intent: Use Sgn to make sign-dependent logic more readable
Avoid Redundancy: Don't use Sgn(Abs(x)) - always returns 1 or 0
Consider Zero: Zero is neither positive nor negative
Document Usage: Comment when using Sgn in non-obvious ways

Function	Purpose	Returns	Zero Handling
Sgn	Get sign	-1, 0, or 1	Returns 0
Abs	Get magnitude	Non-negative number	Returns 0
Fix	Truncate to integer	Integer toward zero	Returns 0
Int	Round down	Integer (floor)	Returns 0
Round	Round to nearest	Rounded number	Returns 0
If...Then	Conditional logic	Any value	Requires explicit check

Platform Considerations

Available in VB6, VBA (all versions)
Available in VBScript
Part of core VB language
Consistent behavior across all VB variants
No platform-specific quirks

Limitations

Only returns three values: -1, 0, 1
Does not provide magnitude information (use Abs for that)
Cannot distinguish between different magnitudes of same sign
Returns Null if argument is Null (may need special handling)
Not suitable for distinguishing "nearly zero" from actual zero

Abs: Returns the absolute value (magnitude without sign)
Fix: Returns the integer portion of a number, truncating toward zero
Int: Returns the integer portion of a number, rounding down
Round: Rounds a number to specified decimal places
IIf: Conditional function that can implement sign-based logic

Sgn Function

Syntax

Parameters

Return Value

Remarks

Typical Uses

Basic Examples

Common Patterns

Pattern 1: GetChangeDirection

Pattern 2: SameSign

Pattern 3: OppositeSign

Pattern 4: SignMultiplier

Pattern 5: ClampToSign

Pattern 6: SignString

Pattern 7: CompareBySign

Pattern 8: CountBySign

Pattern 9: TrendDirection

Pattern 10: ApplySignTo