Returning largest value from multiple columns using an array

[akselo]

Hello folks,

I have a function where I pass numbers from a table, and I want to compare them and return the largest horizontal value. I store them in an array, and try the DMAX function on it, but get stuck there.

Function landuse(dblmips As Double, dblcie As Double, dblmed As Double, _
    dblpdr As Double, dblret As Double, dblvis As Double)
 
Dim varsqft(5) As Double
Dim varlargest As Double
 
varsqft(0) = dblmips
varsqft(1) = dblcie
varsqft(2) = dblmed
varsqft(3) = dblpdr
varsqft(4) = dblret
varsqft(5) = dblvis
'the problem seems to be the next line
varlargest = DSum(varsqft)
landuse = varlargest
 
End Function

Any suggestions on how to achieve this, with arrays or otherwise?

[hariharanmca]

Explain your problem detail.
Do you mean, you want to check the biggest value in each field or compare a value in each field?

[akselo]

Explain your problem detail.
Do you mean, you want to check the biggest value in each field or compare a value in each field?

Yes, I have six numerical fields with square foot information for different landuse activities. I want the function to identify the largest value in any one row across the six fields and return that value. If I could perform a numerical operation on values in an array, that seems easier than 6 or so different nested if statements.

[ADezii]

Hello folks,

I have a function where I pass numbers from a table, and I want to compare them and return the largest horizontal value. I store them in an array, and try the DMAX function on it, but get stuck there.

Function landuse(dblmips As Double, dblcie As Double, dblmed As Double, _
    dblpdr As Double, dblret As Double, dblvis As Double)
 
Dim varsqft(5) As Double
Dim varlargest As Double
 
varsqft(0) = dblmips
varsqft(1) = dblcie
varsqft(2) = dblmed
varsqft(3) = dblpdr
varsqft(4) = dblret
varsqft(5) = dblvis
'the problem seems to be the next line
varlargest = DSum(varsqft)
landuse = varlargest
 
End Function

Any suggestions on how to achieve this, with arrays or otherwise?

Copy & Paste the following code segment, and you'll be fine:

Function landuse(dblmips As Double, dblcie As Double, dblmed As Double, _
    dblpdr As Double, dblret As Double, dblvis As Double)
 
Dim varsqft(0 To 6) As Double, dblTemp As Double
Dim intArrayCounter As Integer
 
dblTemp = varsqft(0)    'make the 1st Element a Seed Value
varsqft(1) = dblmips
varsqft(2) = dblcie
varsqft(3) = dblmed
varsqft(4) = dblpdr
varsqft(5) = dblret
varsqft(6) = dblvis
 
For intArrayCounter = 1 To 6
  If varsqft(intArrayCounter) > dblTemp Then
    dblTemp = varsqft(intArrayCounter)
  End If
Next
 
landuse = dblTemp
End Function

'Sample Function Call
Debug.Print "The Maximum Value passed to the landuse Function is: " & landuse(89.123,456.0987,74.879,9.213,237.0,81.2345)
 

OUTPUT:

The Maximum Value passed to the landuse Function is: 456.0987

NOTE: The code does neither Data Type nor Null Value Checking - I'll leave that up to you. If it is imperative that all these Fields have Values (DOUBLE), simply set the Required Property in the Table to True, and the Data type to Numeric/DOUBLE. Good Luck!

[akselo]

Copy & Paste the following code segment, and you'll be fine:

Function landuse(dblmips As Double, dblcie As Double, dblmed As Double, _
    dblpdr As Double, dblret As Double, dblvis As Double)
 
Dim varsqft(0 To 6) As Double, dblTemp As Double
Dim intArrayCounter As Integer
 
dblTemp = varsqft(0)    'make the 1st Element a Seed Value
varsqft(1) = dblmips
varsqft(2) = dblcie
varsqft(3) = dblmed
varsqft(4) = dblpdr
varsqft(5) = dblret
varsqft(6) = dblvis
 
For intArrayCounter = 1 To 6
  If varsqft(intArrayCounter) > dblTemp Then
    dblTemp = varsqft(intArrayCounter)
  End If
Next
 
landuse = dblTemp
End Function

'Sample Function Call
Debug.Print "The Maximum Value passed to the landuse Function is: " & landuse(89.123,456.0987,74.879,9.213,237.0,81.2345)
 

OUTPUT:

The Maximum Value passed to the landuse Function is: 456.0987

NOTE: The code does neither Data Type nor Null Value Checking - I'll leave that up to you. If it is imperative that all these Fields have Values (DOUBLE), simply set the Required Property in the Table to True, and the Data type to Numeric/DOUBLE. Good Luck!

This works and is awesome!! Thank you so much even if I can't see how dblTemp/varsqft(0) is assigned a value to begin with. It appears you suggest that it is better to deal with nulls in the source table rather than to make control flow workarounds in the code at runtime?

[ADezii]

This works and is awesome!! Thank you so much even if I can't see how dblTemp/varsqft(0) is assigned a value to begin with. It appears you suggest that it is better to deal with nulls in the source table rather than to make control flow workarounds in the code at runtime?

Thank you so much even if I can't see how dblTemp/varsqft(0) is assigned a value to begin with.

It is nothing more than a 'Seed' Value. Many times in Arrays, the 1st Element (0) is not used for related data storage but provides some other functionality.

It appears you suggest that it is better to deal with nulls in the source table rather than to make control flow workarounds in the code at runtime?

If all your Fields are required in the first place, why would you want to allow Nulls at the Table level, then check for them later in code? Do not allow the situation to happen in the first place.