VBA
 

My friend gave me the following as a solution to a question.

Public Function CjsAverage(ParamArray DataRanges()) As Double

Dim rDataRange As Range
Dim rCell As Range
Dim dTotal As Double
Dim iCount As Long
Dim i As Long

For i = LBound(DataRanges) To UBound(DataRanges)
For Each rCell In DataRanges(i).Cells
If Not IsError(rCell.Value) Then
If IsNumeric(rCell.Value) Then
dTotal = dTotal + rCell.Value
iCount = iCount + 1
End If
End If
Next rCell
Next i

CjsAverage = dTotal / iCount

I am to save it in a module in VBA but when I go to the Excel view and "run"
macros, I do not find it listed. How do I find it in Excel view? My friend
has left for the weekend and is unavailable to assist me further with this
quandary.

VBA
 

Because its a Function and not a Sub, it doesn't show up in

Tools Macro Macros...

and you don't call it with a mouse click. Just enter it in a worksheet cell
like =SUM(), etc.
--
Gary's Student


"whispagirl" wrote:

My friend gave me the following as a solution to a question.

Public Function CjsAverage(ParamArray DataRanges()) As Double

Dim rDataRange As Range
Dim rCell As Range
Dim dTotal As Double
Dim iCount As Long
Dim i As Long

For i = LBound(DataRanges) To UBound(DataRanges)
For Each rCell In DataRanges(i).Cells
If Not IsError(rCell.Value) Then
If IsNumeric(rCell.Value) Then
dTotal = dTotal + rCell.Value
iCount = iCount + 1
End If
End If
Next rCell
Next i

CjsAverage = dTotal / iCount

I am to save it in a module in VBA but when I go to the Excel view and "run"
macros, I do not find it listed. How do I find it in Excel view? My friend
has left for the weekend and is unavailable to assist me further with this
quandary.

VBA
 

To elaborate on what Gary''s Student said -
use it like you would a regular Excel function in a cell, like this:
=CjsAverage(B1:B99)


"whispagirl" wrote:

My friend gave me the following as a solution to a question.

Public Function CjsAverage(ParamArray DataRanges()) As Double

Dim rDataRange As Range
Dim rCell As Range
Dim dTotal As Double
Dim iCount As Long
Dim i As Long

For i = LBound(DataRanges) To UBound(DataRanges)
For Each rCell In DataRanges(i).Cells
If Not IsError(rCell.Value) Then
If IsNumeric(rCell.Value) Then
dTotal = dTotal + rCell.Value
iCount = iCount + 1
End If
End If
Next rCell
Next i

CjsAverage = dTotal / iCount

I am to save it in a module in VBA but when I go to the Excel view and "run"
macros, I do not find it listed. How do I find it in Excel view? My friend
has left for the weekend and is unavailable to assist me further with this
quandary.