Does anyone know of any code for custom functions or VBA functions for Avg,
Std, Min, Max? I had been using the Excel functions (i.e.
application.worksheetfunction.) but have run into the 65326 contraint when
trying to apply these to an array populated by an Excel range. I need to
pass the VBA array to these VBA functions to get the Avg, Std, Min, Max.

Thanks

EM

Without knowing how your spreadsheet is set up and whether you're trying to
get stats for columns of info or rows of info, or for a matrix it's hard to
provide specific code.
One starting point I can think of is that you could select all the cells
involved and call a macro which gets the .Address of the current selection.
You can then quickly examine the address and determine the number of
rows/columns and continue on to calculate stats within that range. In Excel
VBA Selection.Address will return something like $A$1:$A$29 if you just
select a single row of cells (A1 through A29) and it will return something
like $A$1:$D$44 if you were to select all of those cells.

Now I could code up something to get AVG, MIN and MAX, but I'd have to look
up formula somewhere to code the equivalent of STD, unless you have it laying
around handy somewhere. I'm not a statistician.

here's some code that will take the selected range and parse out start/end
columns and row numbers:
Sub ParseAddress()
Dim RangeAddress As String
Dim FirstColumn As String
Dim FirstRow As Long
Dim LastColumn As String
Dim LastRow As Long

With Selection
MsgBox "The address is: " & .Address
End With
RangeAddress = Selection.Address
' $A$4:$D$99
FirstColumn = Mid(RangeAddress, 2, InStr(2, RangeAddress, "$") - 2)
MsgBox "First Column is: " & FirstColumn
'rip off what we just found
RangeAddress = Right(RangeAddress, Len(RangeAddress) - (Len(FirstColumn)
+ 2))
MsgBox "RangeAddress Reduced to: " & RangeAddress
FirstRow = Val(Left(RangeAddress, InStr(RangeAddress, ":") - 1))
MsgBox "First Row is: " & FirstRow
'again rip off what has been extracted
RangeAddress = Right(RangeAddress, Len(RangeAddress) -
InStr(RangeAddress, ":"))
MsgBox "RangeAddress reduced to: " & RangeAddress
LastColumn = Mid(RangeAddress, 2, InStr(2, RangeAddress, "$") - 2)
MsgBox "Last Column is: " & LastColumn
LastRow = Val(Right(RangeAddress, Len(RangeAddress) - InStr(2,
RangeAddress, "$")))
MsgBox "Last Row is: " & LastRow
End Sub

"ExcelMonkey" wrote:

Does anyone know of any code for custom functions or VBA functions for Avg,
Std, Min, Max? I had been using the Excel functions (i.e.
application.worksheetfunction.) but have run into the 65326 contraint when
trying to apply these to an array populated by an Excel range. I need to
pass the VBA array to these VBA functions to get the Avg, Std, Min, Max.

Thanks

EM