UDF Question

[dev-all]

I have a spreadsheet that tracks "violations" by setting background colors of
cells and by placing text in certain "custom" cells. I wrote a UDF to
calculate how many violations appear in a specified range. I then call this
UDF using a formula like...

=GetViolationCount($A1:$AF1)

It works great in a worksheet. In other words, every cell in which I call
the UDF with an appropriate range, the cell shows the correct value.
In another portion of the spreadsheet, I have UserForm that searches for
any of several, user chosen, criteria. One of the choices is "has
violations". So I want to look at the cell that contains the result of my
GetViolationCount UDF and simply test if it is greater than 0. ie....

CInt(allDataRange(curRow, kViolationCountColumnIndex).Value) > 0

No matter which property I use to access that cell, I've tried Value,
Value2 and Text among others, I always get back an empty string. Other
properties like Address etc return appropriate values. Am I trying to do
something impossible or am I being stupid?

Here is the entire UDF if it matters...

Public Function GetViolationCount(inRange As Range) As Integer
Dim numViolations As Integer

numViolations = 0

Dim numRows As Integer
Dim nthRow As Integer

numRows = inRange.Rows.Count

Dim numCols As Integer
Dim nthColumn As Integer

numCols = inRange.Columns.Count

For nthRow = 1 To numRows
For nthColumn = kFirstViolationColumnIndex To numCols
Dim tStr As String
tStr = CStr(inRange(nthRow, nthColumn).Value)

If IsCustomColumn(nthColumn) Then
If inRange(nthRow, nthColumn).Value <> "" Then
numViolations = numViolations + 1
End If
Else
If inRange(nthRow, nthColumn).Interior.colorIndex <> xlNone
Then
numViolations = numViolations + 1
End If
End If
Next nthColumn
Next nthRow

GetViolationCount = numViolations
End Function

 

[Barb Reinhardt]

I've added some comments to your code.

Public Function GetViolationCount(inRange As Range) As Integer
Dim numViolations As Integer
Dim kFirstViolationColumnIndex As Long

numViolations = 0

Dim numRows As Integer
Dim nthRow As Integer

numRows = inRange.Rows.Count

Dim numCols As Integer
Dim nthColumn As Integer

numCols = inRange.Columns.Count

For nthRow = 1 To numRows
'Do you really want nthRow to start at 1 if the range starts in row
(say) 10
For nthColumn = kFirstViolationColumnIndex To numCols
'Where is kFirstViolationCOlumnIndex defined
Dim tStr As String
tStr = CStr(inRange(nthRow, nthColumn).Value)

If Iscustomcolumn(nthColumn) Then '<~~~I presume this is
another function
'Do you have this defined somewhere?
If inRange(nthRow, nthColumn).Value <> "" Then
'<~~~another function?
numViolations = numViolations + 1
End If
Else
If inRange(nthRow, nthColumn).Interior.ColorIndex <> xlNone
Then
numViolations = numViolations + 1
End If
End If
Next nthColumn
Next nthRow

GetViolationCount = numViolations
End Function

 

[Jim Cone]

Function AnyViolations() As Boolean
Dim x As Boolean
'The cell checked is 4 down and 1 to the right of the active cell.
x = CBool(ActiveCell(5, 2).Value > 0)
AnyViolations = x
End Function

Sub FindOut()
MsgBox AnyViolations
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"dev-all" <[email protected]>
wrote in message
I have a spreadsheet that tracks "violations" by setting background colors of
cells and by placing text in certain "custom" cells. I wrote a UDF to
calculate how many violations appear in a specified range. I then call this
UDF using a formula like...

=GetViolationCount($A1:$AF1)

It works great in a worksheet. In other words, every cell in which I call
the UDF with an appropriate range, the cell shows the correct value.
In another portion of the spreadsheet, I have UserForm that searches for
any of several, user chosen, criteria. One of the choices is "has
violations". So I want to look at the cell that contains the result of my
GetViolationCount UDF and simply test if it is greater than 0. ie....

CInt(allDataRange(curRow, kViolationCountColumnIndex).Value) > 0

No matter which property I use to access that cell, I've tried Value,
Value2 and Text among others, I always get back an empty string. Other
properties like Address etc return appropriate values. Am I trying to do
something impossible or am I being stupid?

Here is the entire UDF if it matters...

Public Function GetViolationCount(inRange As Range) As Integer
Dim numViolations As Integer

numViolations = 0

Dim numRows As Integer
Dim nthRow As Integer

numRows = inRange.Rows.Count

Dim numCols As Integer
Dim nthColumn As Integer

numCols = inRange.Columns.Count

For nthRow = 1 To numRows
For nthColumn = kFirstViolationColumnIndex To numCols
Dim tStr As String
tStr = CStr(inRange(nthRow, nthColumn).Value)

If IsCustomColumn(nthColumn) Then
If inRange(nthRow, nthColumn).Value <> "" Then
numViolations = numViolations + 1
End If
Else
If inRange(nthRow, nthColumn).Interior.colorIndex <> xlNone
Then
numViolations = numViolations + 1
End If
End If
Next nthColumn
Next nthRow

GetViolationCount = numViolations
End Function

 

[dev-all]

Hi Barb,
Thank you for your time and efforts on this.

Yes, I do want nthRow to start at 1. I want to be able to support ranges
like $A1:$AF1 as well as ranges like $A1:$AF20 where the difference would be
counting violations from one or more incidents. I also want nthColumn to
start at 1 too but for debugging purposes, I have "optimized" it for my
current data set.
Given 2 example ranges of $A10:$AF10 and $A5:$AF5 2, when nthRow = 1, the
code will start at row 10 and 5 respectively. Please correct me if I am
wrong but I have a lot of code that shows that to be correct.
kFirstViolationColumnIndex is defined in a Module (the only Module in my
project) as...

Public Const kFirstViolationColumnIndex = 8

The GetViolationCount function is defined there as well. I apologize for
the tStr related code. That is remnant debugging code.

Yes, IsCustomColumn is another function also defined in that Module. It
is...

Public Function IsCustomColumn(inColumnIndex As Integer) As Boolean
Select Case inColumnIndex
Case kCustom1ColumnIndex, kCustom2ColumnIndex, kCustom3ColumnIndex,
kCustom4ColumnIndex
IsCustomColumn = True
Case Else
IsCustomColumn = False
End Select
End Function

For completeness...

Public Const kCustom1ColumnIndex = 14
Public Const kCustom2ColumnIndex = 23
Public Const kCustom3ColumnIndex = 27
Public Const kCustom4ColumnIndex = 31

inRange(nthRow, nthColumn).Value is not another function. I am accessing
cell(nthRow, nthColumn) of the range that was passed in. Assuming the Range
$A1:$AF1 was passed in and nthRow = 1 and nthColumn = 3, then that point of
the code will access the value of cell $C$1.

 

[dev-all]

Hi Jim,
Thank you for your time and efforts on this problem. This is very
interesting. Accessing the value through ActiveCell produces the correct
result. My code is highly selection and worksheet independent so I can't use
this directly but I will try accessing the cells via alternate means to see
if I can find something compatible.