# New Math Functions in Excel VBA

Posted
Comments None

The Excel VBA code for all the new 39 math functions to be added to the built-in math functions of GeneXproTools is extremely important for two main reasons. First of all, the Excel VBA code is being used to double check the generated code through the deployment of models and ensembles to Excel.

Second of all, because of the double checking, the VBA code can be used as a new template grammar for some of the programming languages that GeneXproTools supports, like Visual Basic, VB.Net and others.

As we will see below, the Excel VBA code for the new 39 math functions differs quite a bit from the C++ code. As it turns out, there are some transformations that are easily done with a simple Replace All, but others require extra typing. For example, the function declarations, although quite different, can be easily converted to VBA with find & replace; the IF THEN ELSE statements, however, need some attention as VBA requires the word "Then" after each If and ElseIf and also End If at the end of each If block.

Another difference is in the declaration and initialization of variables, which in Excel VBA require decoupling the variable declaration from its initialization and also the word "Dim" before the variable name and "As Double" or "As Long" after the variable/constant name. The return statement is also a pain, as VBA uses the name of the function for that purpose.

There are also other minor differences such as the comment marks, "And" instead of "&&", "<>" instead of "!=", and so on, but these are easily fixed with a simple Replace All. But you can take a look at the complete Excel VBA code below to check all the differences:

Function gepRamp1(ByVal x As Double) As Double
If x > 0.0 Then
gepRamp1 = x
Else
gepRamp1 = 0
End If
End Function

Function gepRamp2(ByVal x As Double) As Double
If x > 0.0 Then
gepRamp2 = 0
Else
gepRamp2 = x
End If
End Function

Function gepRamp3(ByVal x As Double) As Double
If x > 0.0 Then
gepRamp3 = 0
Else
gepRamp3 = -x
End If
End Function

Function gepRamp4(ByVal x As Double) As Double
If x > 0.0 Then
gepRamp4 = -x
Else
gepRamp4 = 0
End If
End Function

Function gepStep1(ByVal x As Double) As Double
If x > 0.0 Then
gepStep1 = 1
Else
gepStep1 = -1
End If
End Function

Function gepStep2(ByVal x As Double) As Double
If x > 0.0 Then
gepStep2 = 1
Else
gepStep2 = 0
End If
End Function

Function gepStep3(ByVal x As Double) As Double
If x >= 1 Then
gepStep3 = 1
ElseIf x <= -1 Then
gepStep3 = -1
Else
gepStep3 = x
End If
End Function

Function gepStep4(ByVal x As Double) As Double
If x >= 1 Then
gepStep4 = 1
ElseIf x <= 0 Then
gepStep4 = 0
Else
gepStep4 = x
End If
End Function

Function gepCL2A(ByVal x As Double, ByVal y As Double) As Double
If x > 0.0 And y > 0.0 Then
gepCL2A = 1
Else
gepCL2A = -1
End If
End Function

Function gepCL2B(ByVal x As Double, ByVal y As Double) As Double
If x >= 0 And y < 0.0 Then
gepCL2B = -1
Else
gepCL2B = 1
End If
End Function

Function gepCL2C(ByVal x As Double, ByVal y As Double) As Double
If x > 1.0 And y < -1.0 Then
gepCL2C = -1
Else
gepCL2C = 1
End If
End Function

Function gepCL2D(ByVal x As Double, ByVal y As Double) As Double
If x > 0.0 And y > 0.0 Then
gepCL2D = 1
Else
gepCL2D = 0
End If
End Function

Function gepCL2E(ByVal x As Double, ByVal y As Double) As Double
If x >= 0 And y <= 0 Then
gepCL2E = 0
Else
gepCL2E = 1
End If
End Function

Function gepCL2F(ByVal x As Double, ByVal y As Double) As Double
If x > 1.0 And y < -1.0 Then
gepCL2F = 0
Else
gepCL2F = 1
End If
End Function

Function gepCL3A(ByVal x As Double, ByVal y As Double) As Double
If x > 0.0 And y < 0.0 Then
gepCL3A = 1
ElseIf x < 0.0 And y > 0.0 Then
gepCL3A = -1
Else
gepCL3A = 0
End If
End Function

Function gepCL3B(ByVal x As Double, ByVal y As Double) As Double
If x >= 1 And y >= 1 Then
gepCL3B = 1
ElseIf x <= -1 And y <= -1 Then
gepCL3B = -1
Else
gepCL3B = 0
End If
End Function

Function gepCL3C(ByVal x As Double, ByVal y As Double) As Double
If x > 0.0 And y > 0.0 Then
gepCL3C = 1
ElseIf x < 0.0 And y < 0.0 Then
gepCL3C = -1
Else
gepCL3C = 0
End If
End Function

Function gepMap3A(ByVal x As Double, ByVal y As Double) As Double
Const SLACK As Double = 10.0
If y < (x - SLACK) Then
gepMap3A = -1
ElseIf y > (x + SLACK) Then
gepMap3A = 1
Else
gepMap3A = 0
End If
End Function

Function gepMap3B(ByVal x As Double, ByVal y As Double, ByVal z As Double) As Double
' evaluate minValue(x,y) and maxValue(x,y)
Dim minValue As Double
Dim maxValue As Double
minValue = x
maxValue = y
If minValue > y Then
minValue = y
maxValue = x
End If

If z < minValue Then
gepMap3B = -1
ElseIf z > maxValue Then
gepMap3B = 1
Else
gepMap3B = 0
End If
End Function

Function gepMap3C(ByVal a As Double, ByVal b As Double, ByVal c As Double, ByVal d As Double) As Double
' evaluate minValue(a,b,c) and maxValue(a,b,c)
'
' evaluate minValue(a,b,c)
Dim minValue As Double
minValue = a
If minValue > b Then minValue = b
If minValue > c Then minValue = c
' evaluate maxValue(a,b,c)
Dim maxValue As Double
maxValue = a
If maxValue < b Then maxValue = b
If maxValue < c Then maxValue = c

If d < minValue Then
gepMap3C = -1
ElseIf d > maxValue Then
gepMap3C = 1
Else
gepMap3C = 0
End If
End Function

Function gepMap4A(ByVal x As Double, ByVal y As Double) As Double
Const SLACK As Double = 10.0
If y < (x - SLACK) Then
gepMap4A = 0
ElseIf y >= (x - SLACK) And y < x Then
gepMap4A = 1
ElseIf y >= x And y < (x + SLACK) Then
gepMap4A = 2
ElseIf y >= (x + SLACK) Then
gepMap4A = 3
End If
End Function

Function gepMap4B(ByVal x As Double, ByVal y As Double, ByVal z As Double) As Double
' evaluate minValue(x,y), maxValue(x,y) and midrange
Dim minValue As Double
Dim maxValue As Double
minValue = x
maxValue = y
If minValue > y Then
minValue = y
maxValue = x
End If
Dim midrange As Double
midrange = (maxValue + minValue)/2.0

If z < minValue Then
gepMap4B = 0
ElseIf z >= minValue And z < midrange Then
gepMap4B = 1
ElseIf z >= midrange And z < maxValue Then
gepMap4B = 2
ElseIf z >= maxValue Then
gepMap4B = 3
End If
End Function

Function gepMap4C(ByVal a As Double, ByVal b As Double, ByVal c As Double, ByVal d As Double) As Double
' evaluate minValue(a,b,c), maxValue(a,b,c) and midleValue(a,b,c)
'
' evaluate minValue(a,b,c)
Dim minValue As Double
Dim argMin As Long
minValue = a
argMin = 0
If minValue > b Then
minValue = b
argMin = 1
End If
If minValue > c Then
minValue = c
argMin = 2
End If
' evaluate maxValue(a,b,c)
Dim maxValue As Double
Dim argMax As Long
maxValue = a
argMax = 0
If maxValue < b Then
maxValue = b
argMax = 1
End If
If maxValue < c Then
maxValue = c
argMax = 2
End If
' evaluate midleValue(a,b,c)
Dim midleValue As Double
midleValue = c
If 0 <> argMin And 0 <> argMax Then
midleValue = a
End If
If 1 <> argMin And 1 <> argMax Then
midleValue = b
End If

If d < minValue Then
gepMap4C = 0
ElseIf d >= minValue And d < midleValue Then
gepMap4C = 1
ElseIf d >= midleValue And d < maxValue Then
gepMap4C = 2
ElseIf d >= maxValue Then
gepMap4C = 3
End If
End Function

Function gepMap5A(ByVal x As Double, ByVal y As Double) As Double
Const SLACK As Double = 15.0
If y < (x - SLACK) Then
gepMap5A = 0
ElseIf y >= (x - SLACK) And y < (x - SLACK/3.0) Then
gepMap5A = 1
ElseIf y >= (x - SLACK/3.0) And y < (x + SLACK/3.0) Then
gepMap5A = 2
ElseIf y >= (x + SLACK/3.0) And y < (x + SLACK) Then
gepMap5A = 3
ElseIf y >= (x + SLACK) Then
gepMap5A = 4
End If
End Function

Function gepMap5B(ByVal x As Double, ByVal y As Double, ByVal z As Double) As Double
' evaluate minValue(x,y), maxValue(x,y), midpoint1, midpoint2
Dim minValue As Double
Dim maxValue As Double
minValue = x
maxValue = y
If minValue > y Then
minValue = y
maxValue = x
End If
Dim intervalLength As Double
Dim midpoint1 As Double
Dim midpoint2 As Double
intervalLength = (maxValue - minValue)/3.0
midpoint1 = minValue + intervalLength
midpoint2 = minValue + 2.0*intervalLength

If z < minValue Then
gepMap5B = 0
ElseIf z >= minValue And z < midpoint1 Then
gepMap5B = 1
ElseIf z >= midpoint1 And z < midpoint2 Then
gepMap5B = 2
ElseIf z >= midpoint2 And z < maxValue Then
gepMap5B = 3
ElseIf z >= maxValue Then
gepMap5B = 4
End If
End Function

Function gepMap5C(ByVal a As Double, ByVal b As Double, ByVal c As Double, ByVal d As Double) As Double
' evaluate minValue(a,b,c), maxValue(a,b,c), midleValue(a,b,c), midrange1, midrange2
'
' evaluate minValue(a,b,c)
Dim minValue As Double
Dim argMin As Long
minValue = a
argMin = 0
If minValue > b Then
minValue = b
argMin = 1
End If
If minValue > c Then
minValue = c
argMin = 2
End If
' evaluate maxValue(a,b,c)
Dim maxValue As Double
Dim argMax As Long
maxValue = a
argMax = 0
If maxValue < b Then
maxValue = b
argMax = 1
End If
If maxValue < c Then
maxValue = c
argMax = 2
End If
' evaluate midleValue(a,b,c)
Dim midleValue As Double
midleValue = c
If 0 <> argMin And 0 <> argMax Then midleValue = a
If 1 <> argMin And 1 <> argMax Then midleValue = b
Dim midrange1 As Double
Dim midrange2 As Double
midrange1 = (minValue + midleValue)/2.0
midrange2 = (midleValue + maxValue)/2.0

If d < minValue Then
gepMap5C = 0
ElseIf d >= minValue And d < midrange1 Then
gepMap5C = 1
ElseIf d >= midrange1 And d < midrange2 Then
gepMap5C = 2
ElseIf d >= midrange2 And d < maxValue Then
gepMap5C = 3
ElseIf d >= maxValue Then
gepMap5C = 4
End If
End Function

Function gepMap6A(ByVal x As Double, ByVal y As Double) As Double
Const SLACK As Double = 10.0
If y < (x - SLACK) Then
gepMap6A = 0
ElseIf y >= (x - SLACK) And y < (x - SLACK/2.0) Then
gepMap6A = 1
ElseIf y >= (x - SLACK/2.0) And y < x Then
gepMap6A = 2
ElseIf y >= x And y < (x + SLACK/2.0) Then
gepMap6A = 3
ElseIf y >= (x + SLACK/2.0) And y < (x + SLACK) Then
gepMap6A = 4
ElseIf y >= (x + SLACK) Then
gepMap6A = 5
End If
End Function

Function gepMap6B(ByVal x As Double, ByVal y As Double, ByVal z As Double) As Double
' evaluate minValue(x,y), maxValue(x,y), midrange, midpoint1, midpoint2
Dim minValue As Double
Dim maxValue As Double
minValue = x
maxValue = y
If minValue > y Then
minValue = y
maxValue = x
End If
Dim midrange As Double
Dim midpoint1 As Double
Dim midpoint2 As Double
midrange = (minValue + maxValue)/2.0
midpoint1 = (minValue + midrange)/2.0
midpoint2 = (midrange + maxValue)/2.0

If z < minValue Then
gepMap6B = 0
ElseIf z >= minValue And z < midpoint1 Then
gepMap6B = 1
ElseIf z >= midpoint1 And z < midrange Then
gepMap6B = 2
ElseIf z >= midrange And z < midpoint2 Then
gepMap6B = 3
ElseIf z >= midpoint2 And z < maxValue Then
gepMap6B = 4
ElseIf z >= maxValue Then
gepMap6B = 5
End If
End Function

Function gepMap6C(ByVal a As Double, ByVal b As Double, ByVal c As Double, ByVal d As Double) As Double
' evaluate minValue(a,b,c), maxValue(a,b,c), midleValue(a,b,c), midrange1, midrange2
'
' evaluate minValue(a,b,c)
Dim minValue As Double
Dim argMin As Long
minValue = a
argMin = 0
If minValue > b Then
minValue = b
argMin = 1
End If
If minValue > c Then
minValue = c
argMin = 2
End If
' evaluate maxValue(a,b,c)
Dim maxValue As Double
Dim argMax As Long
maxValue = a
argMax = 0
If maxValue < b Then
maxValue = b
argMax = 1
End If
If maxValue < c Then
maxValue = c
argMax = 2
End If
' evaluate midleValue(a,b,c)
Dim midleValue As Double
midleValue = c
If 0 <> argMin And 0 <> argMax Then midleValue = a
If 1 <> argMin And 1 <> argMax Then midleValue = b
' evaluate midrange1 and midrange2
Dim midrange1 As Double
Dim midrange2 As Double
midrange1 = (minValue + midleValue)/2.0
midrange2 = (midleValue + maxValue)/2.0

If d < minValue Then
gepMap6C = 0
ElseIf d >= minValue And d < midrange1 Then
gepMap6C = 1
ElseIf d >= midrange1 And d < midleValue Then
gepMap6C = 2
ElseIf d >= midleValue And d < midrange2 Then
gepMap6C = 3
ElseIf d >= midrange2 And d < maxValue Then
gepMap6C = 4
ElseIf d >= maxValue Then
gepMap6C = 5
End If
End Function

Function gepECL3A(ByVal x As Double, ByVal y As Double, ByVal z As Double) As Double
If y > x And z < x Then
gepECL3A = 1
ElseIf y < x And z > x Then
gepECL3A = -1
Else
gepECL3A = 0
End If
End Function

Function gepECL3B(ByVal x As Double, ByVal y As Double, ByVal z As Double) As Double
If y > x And z > x Then
gepECL3B = 1
ElseIf y < x And z < x Then
gepECL3B = -1
Else
gepECL3B = 0
End If
End Function

Function gepECL3C(ByVal x As Double, ByVal y As Double, ByVal z As Double) As Double
If y >= x And z >= x Then
gepECL3C = 1
ElseIf y <= -x And z <= -x Then
gepECL3C = -1
Else
gepECL3C = 0
End If
End Function

Function gepECL3D(ByVal a As Double, ByVal b As Double, ByVal c As Double, ByVal d As Double) As Double
' evaluate minValue(a,b) and maxValue(a,b)
Dim minValue As Double
Dim maxValue As Double
minValue = a
maxValue = b
If minValue > b Then
minValue = b
maxValue = a
End If

If c >= maxValue And d >= maxValue Then
gepECL3D = 1
ElseIf c <= minValue And d <= minValue Then
gepECL3D = -1
Else
gepECL3D = 0
End If
End Function

Function gepAMin2(ByVal x As Double, ByVal y As Double) As Double
If x < y Then
gepAMin2 = 0
Else
gepAMin2 = 1
End If
End Function

Function gepAMin3(ByVal x As Double, ByVal y As Double, ByVal z As Double) As Double
Dim temp As Double
Dim argMin As Double
temp = x
argMin = 0
If temp >= y Then
temp = y
argMin = 1
End If
If temp >= z Then argMin = 2
gepAMin3 = argMin
End Function

Function gepAMin4(ByVal a As Double, ByVal b As Double, ByVal c As Double, ByVal d As Double) As Double
Dim temp As Double
Dim argMin As Double
temp = a
argMin = 0
If temp >= b Then
temp = b
argMin = 1
End If
If temp >= c Then
temp = c
argMin = 2
End If
If temp >= d Then argMin = 3
gepAMin4 = argMin
End Function

Function gepAMax2(ByVal x As Double, ByVal y As Double) As Double
If x >= y Then
gepAMax2 = 0
Else
gepAMax2 = 1
End If
End Function

Function gepAMax3(ByVal x As Double, ByVal y As Double, ByVal z As Double) As Double
Dim temp As Double
Dim argMax As Double
temp = x
argMax = 0
If temp < y Then
temp = y
argMax = 1
End If
If temp < z Then argMax = 2
gepAMax3 = argMax
End Function

Function gepAMax4(ByVal a As Double, ByVal b As Double, ByVal c As Double, ByVal d As Double) As Double
Dim temp As Double
Dim argMax As Double
temp = a
argMax = 0
If temp < b Then
temp = b
argMax = 1
End If
If temp < c Then
temp = c
argMax = 2
End If
If temp < d Then argMax = 3
gepAMax4 = argMax
End Function

Author

### Comments

There are currently no comments on this article.

### Comment

Enter your comment below. Fields marked * are required. You must preview your comment first before finally posting.