Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|