Power, Max, Min Functions

  • Thread starter Thread starter James
  • Start date Start date
J

James

Hi All,

I am trying to convert some VBA code to VB code and have become stuck on the
Power, Max and Min function built into excel.

Does anybody know of any code that will offer the same ability in VB without
reference to the excel dll's

Excel code

Application.power
Application.Max
Application.Min

This is what I need to convert to VB.

Thanks for your help

James
 
For POWER use ^ (exponential)

Use IF to test for MAX and MIN, for example,

Sub aa()
Dim a, b
a = 5
b = 6

If a > b Then
Debug.Print a
Else
Debug.Print b
End If
End Sub

OR ...

Sub aaa()
Dim a, b
a = 5
b = 6

Debug.Print IIf(a > b, a, b)

End Sub

--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
 
Andy Wiggins said:
Use IF to test for MAX and MIN, for example,

Sub aa()
Dim a, b
a = 5
b = 6

If a > b Then
Debug.Print a
Else
Debug.Print b
End If
End Sub

OR ...

Sub aaa()
Dim a, b
a = 5
b = 6

Debug.Print IIf(a > b, a, b)

End Sub
Click to expand...
....

That works for pairs of numbers, but what if the OP wants to pass a single
array to MAX or MIN?


Function MyMax(ParamArray a() As Variant) As Double
Dim s As Boolean, x As Variant, y As Variant, z As Variant

For Each x In a
'unsure what this first bit would do in VBA if Excel.Range weren't
'a registered type - probably throw a runtime error - dump as needed
If TypeOf x Is Excel.Range Then
For Each y In x.Cells
z = y.Value
If s Imp (MyMax < z) Then
MyMax = z
s = True
End If
Next y
ElseIf IsArray(x) Then
For Each y In x
If IsArray(y) Then z = MyMax(y) Else z = y
If s Imp (MyMax < z) Then
MyMax = z
s = True
End If
Next y
Else
'adding support for numeric types left as an exercise
If InStr("ByteIntegerLongSingleDouble", TypeName(x)) > 0 Then
If s Imp (MyMax < x) Then
MyMax = x
s = True
End If
End If
End If
Next x

End Function


For MyMin replace < with >. Note that these functions return 0 rather than
#VALUE! when fed no numeric values, unlike their Excel 'equivalents' (but
like their 123 counterparts).
 
Thanks Guys

Harlan Grove said:
...

That works for pairs of numbers, but what if the OP wants to pass a single
array to MAX or MIN?


Function MyMax(ParamArray a() As Variant) As Double
Dim s As Boolean, x As Variant, y As Variant, z As Variant

For Each x In a
'unsure what this first bit would do in VBA if Excel.Range weren't
'a registered type - probably throw a runtime error - dump as needed
If TypeOf x Is Excel.Range Then
For Each y In x.Cells
z = y.Value
If s Imp (MyMax < z) Then
MyMax = z
s = True
End If
Next y
ElseIf IsArray(x) Then
For Each y In x
If IsArray(y) Then z = MyMax(y) Else z = y
If s Imp (MyMax < z) Then
MyMax = z
s = True
End If
Next y
Else
'adding support for numeric types left as an exercise
If InStr("ByteIntegerLongSingleDouble", TypeName(x)) > 0 Then
If s Imp (MyMax < x) Then
MyMax = x
s = True
End If
End If
End If
Next x

End Function


For MyMin replace < with >. Note that these functions return 0 rather than
#VALUE! when fed no numeric values, unlike their Excel 'equivalents' (but
like their 123 counterparts).
Click to expand...
 
Back
Top