Application.Volatile messing up other function

[itchyII]

Hi Everyone,
I have a workbook with a few different sheets which have code behin
them as well as some additional functions in a module. It is
workbook to track survey responses. In the first sheet "Surve
Tracking", I track who has returned a survey and who has not. In th
next sheet "Responses" I track the responses to the questions that eac
person made. In the Third sheet "Survey Stats" I calculate som
statistics based on the "Survey Tracking" sheet (stuff like how man
people responded to the survey etc.). There is a field in the “Surve
Tracking” sheet with a drop down list containing “Yes, No, Declined an
Cancelled”. If the user makes a selection in this box, the sheet fire
some functions that format the cells in that row based on the input an
then prompts the user asking if they want to go to the “Responses
sheet to enter the responses now (if they click yes, they are brough
to the corresponding row in the “Responses” sheet. Now, once the fiel
in the “Survey Tracking” sheet changes, it will affect some function
in the “Survey Stats” sheet. In that sheet, I have cells referencin
functions in my module that make calculations based on the “Surve
Tracking” sheet. The problem is that in order for the functions in th
“Survey Stats” sheet to be recalculated every time there are changes i
the “Survey Tracking” sheet, I have declared them as volatile.
However, in doing so, my functions in the “Survey Tracking” sheet n
longer work! If I comment out all of the ‘Application.Volatile’ line
in my functions, the functions in the “Survey Tracking” sheet wor
fine! Why is this happening and how can I fix it?

Here is the code in “Survey Tracking”

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Call formatHighlight(Target.row)
Call formatFont(Target.row)
With ActiveCell
If .Column = 13 Then
If .Value = "Yes" Then
If MsgBox("Do you wish to enter this client's surve
responses now?", vbYesNo, "Enter Survey Responses") = vbYes Then
Worksheets("Responses").Activate
ActiveSheet.Range("B" & Target.row).Select
End If
End If
End If
End With

End Sub

Function formatHighlight(row As Integer)

With ActiveSheet.Range("O" & row)
If .Value = "Yes" Then
ActiveSheet.Range("B" & row & ":" & "P"
row).Interior.ColorIndex = 4
ActiveWorkbook.Worksheets("Responses").Range("A" & row & ":"
"Y" & row).Interior.ColorIndex = 4
Else
With ActiveSheet.Range("M" & row)
If .Value = "Yes" Then
ActiveSheet.Range("B" & row & ":" & "P"
row).Interior.ColorIndex = xlColorIndexNone
ActiveWorkbook.Worksheets("Responses").Range("A" & ro
& ":" & "Y" & row).Interior.ColorIndex = xlColorIndexNone
ElseIf .Value = "No" Then
ActiveSheet.Range("B" & row & ":" & "P"
row).Interior.ColorIndex = 6
ActiveWorkbook.Worksheets("Responses").Range("A" & ro
& ":" & "Y" & row).Interior.ColorIndex = 6
ElseIf .Value = "Declined" Then
ActiveSheet.Range("B" & row & ":" & "P"
row).Interior.ColorIndex = xlColorIndexNone
ActiveWorkbook.Worksheets("Responses").Range("A" & ro
& ":" & "Y" & row).Interior.ColorIndex = xlColorIndexNone
ElseIf .Value = "Cancelled" Then
ActiveSheet.Range("B" & row & ":" & "P"
row).Interior.ColorIndex = xlColorIndexNone
ActiveWorkbook.Worksheets("Responses").Range("A" & ro
& ":" & "Y" & row).Interior.ColorIndex = xlColorIndexNone
End If
End With
End If
End With

End Function

Function formatFont(row As Integer)

With ActiveSheet.Range("M" & row)
If .Value = "Yes" Then
ActiveSheet.Range("B" & row & ":" & "N"
row).Font.Strikethrough = False
ActiveWorkbook.Worksheets("Responses").Range("A" & row & ":"
"Y" & row).Font.Strikethrough = False
ElseIf .Value = "No" Then
ActiveSheet.Range("B" & row & ":" & "N"
row).Font.Strikethrough = False
ActiveWorkbook.Worksheets("Responses").Range("A" & row & ":"
"Y" & row).Font.Strikethrough = False
ElseIf .Value = "Declined" Then
ActiveSheet.Range("B" & row & ":" & "N"
row).Font.Strikethrough = True
ActiveWorkbook.Worksheets("Responses").Range("A" & row & ":"
"Y" & row).Font.Strikethrough = True
ElseIf .Value = "Cancelled" Then
ActiveSheet.Range("B" & row & ":" & "N" &
row).Font.Strikethrough = True
ActiveWorkbook.Worksheets("Responses").Range("A" & row & ":" &
"Y" & row).Font.Strikethrough = True
End If
End With

End Function


Here is the code in my module that is referenced in “Survey Stats”

Option Explicit

Public Function findNumRows(wks As Worksheet, col As String, headers As
Integer) As Integer
Dim row As Integer

'set starting row
row = headers
While wks.Range(col & row + 1).Value <> "" 'loop until no data in row
row = row + 1
Wend
row = row - headers 'remove number of rows for headers

findNumRows = row

End Function

Function countSent(which As String) As Integer

'Application.volatile

Dim numRows As Integer
Dim x As Integer
Dim count As Integer

numRows = findNumRows(Worksheets("Survey Tracking"), "F", 2)
count = 0
If which = "Total" Then
For x = 1 To numRows
If Not ((Worksheets("Survey Tracking").Range("J" & (x + 2)) =
"" Or IsNull(Worksheets("Survey Tracking").Range("J" & (x + 2)))) _
And (Worksheets("Survey Tracking").Range("K" & (x + 2)) =
"" Or IsNull(Worksheets("Survey Tracking").Range("K" & (x + 2)))))
Then
count = count + 1
End If
Next x
Else
For x = 1 To numRows
If Worksheets("Survey Tracking").Range("D" & (x + 2)) = which
_
And (Not (IsNull(Worksheets("Survey Tracking").Range("J" &
(x + 2))) _
Or IsNull(Worksheets("Survey Tracking").Range("K" & (x
+ 2))))) Then
count = count + 1
End If
Next x
End If

countSent = count

End Function


Function countRCVD(which As String) As Integer

'Application.volatile

Dim numRows As Integer
Dim x As Integer
Dim count As Integer

numRows = findNumRows(Worksheets("Survey Tracking"), "F", 2)
count = 0

If which = "Total" Then
For x = 1 To numRows
If Worksheets("Survey Tracking").Range("M" & (x + 2)) = "Yes"
Then
count = count + 1
End If
Next x
Else
For x = 1 To numRows
If Worksheets("Survey Tracking").Range("D" & x) = which _
And Worksheets("Survey Tracking").Range("M" & x) = "Yes"
Then
count = count + 1
End If
Next x
End If

countRCVD = count

End Function


Sorry for being so long winded!!!

 

[Niek Otten]

<in order for the functions in the
“Survey Stats” sheet to be recalculated every time there are changes in
the “Survey Tracking” sheet, I have declared them as volatile>

That should not be true.
The only really correct way to get functions to recalculate correctly, is to
include all input in the argument list. If you refer to cells directly from
within the function, Excel does not know it has to recalc the function,
because it does not see the dependency.
By including Application.Volatile the functions will recalc, but there is no
guarantee they will do so in the correct order. They may do so and may not
do so the next release, because you're doing something that is not
specified.

I did not look into all the rest of your post, just wanted to give you the
hint to redesign your functions to be safe.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[itchyII]

Thanks for the reply. But it still doesn't work. I tried removing al
calls to the other sheet out of my functions and passing the name o
the sheet as an argument but it still didn't recalculate automaticall
(maybe because the arguments never change). I am really stumped o
this one. But can anyone tell me why the Application.Volatile i
blocking the other functions from running

 

[Niek Otten]

I don't think passing the name of the sheet will Excel to recognize the
dependency. You didn't give your code, but how would Excel understand that
something between quotes means a whole sheet? You might as well want to
print that text.
You really have to pass the ranges.
As for your second question: No. But that is just because I find it
difficult to understand what your functions do.

I know it's a lot of work and feel sorry for you, but I strongly suggest you
follow the recommendations to pass all arguments, without "tricks" like
passing worksheetnames.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[lucaow]

It works!

I had a similar problem.

I followed your suggestion of fully avoiding shortcuts or tricks while passing parameters to the UDF.

Now it works.

Thanks for your useful suggestion.