Function autoupdate, relative addressing

  • Thread starter Thread starter Thomas
  • Start date Start date
T

Thomas

I have written the VBA function code below (it is in a module attached to
the desired WorkBook) to work with a spreadsheet that is intended to work on
a column of information. The data in the column consists of Y, N or M (yes,
no and maybe).

Two problems.

The function works, once. When data is updated in a column the function is
not called to update the displayed information.

Second, the function is entered in a cell as, e.g.,
=SummaryStatistics("F5:F100"). When I copy that formula to another cell the
range does not change in a relative fashion, i.e., it stays as "F5:F100". I
know with other Excel functions that the addressing is relative unless a "$"
is used.

Advice would be appreciated.

Thanks.

Thomas

Windows 2000 Pro, Excel 2000

= = =

Option Explicit

Function SummaryStatistics(stringDataRange As String) As Double

Dim doubleScore, doubleCounter As Double

doubleScore = doubleCounter = 0

Dim cellValue As Range

For Each cellValue In Range(stringDataRange)

Select Case cellValue.Value

Case "Y"
doubleScore = doubleScore + 1
doubleCounter = doubleCounter + 1

Case "N"
doubleScore = doubleScore - 1
doubleCounter = doubleCounter + 1

Case "M"
doubleScore = doubleScore + 0.1
doubleCounter = doubleCounter + 1

Case Else
doubleScore = doubleScore

End Select

Next

SummaryStatistics = doubleScore / doubleCounter

End Function
 
XL only has relative or absolute *range references*, not strings. Since
your Function's argument is a string, XL won't try to update it. Nor
will it call the function when a value in the "range" is updated.

Also, DIM requires a type for each variable:

Dim doubleScore, doubleCounter As Double

is equivalent to

Dim doubleScore As Variant, doubleCounter As Double

Also,

doubleScore = doubleCounter = 0

is interpreted as

doubleScore = (doubleCounter = 0)

the right-hand term returns a boolean True or False. Since doubleCounter
does = 0 initially, doubleScore is initialized as True, which, when used
in a math function, will be coerced to -1.

Try this:

Function SummaryStatistics(DataRange As Range) As Double
Dim doubleScore As Double
Dim doubleCounter As Double
Dim cellValue As Range

doubleScore = 0
doubleCounter = 0
For Each cellValue In DataRange
Select Case cellValue.Value
Case "Y"
doubleScore = doubleScore + 1
doubleCounter = doubleCounter + 1
Case "N"
doubleScore = doubleScore - 1
doubleCounter = doubleCounter + 1
Case "M"
doubleScore = doubleScore + 0.1
doubleCounter = doubleCounter + 1
Case Else
'Do nothing
End Select
Next cellValue
SummaryStatistics = doubleScore / doubleCounter
End Function

calling it as

=SummaryStatistice(F5:F100)
 
Back
Top