Volatile UDF v ws_change conflict

  • Thread starter Thread starter The Cube
  • Start date Start date
T

The Cube

Hi all

In a worksheet there are cells that call an Application.Volatile UDF.
The UDF does nothing other than return the rowheight of the cell that calls
it.
That worksheet also has a Worksheet_Change() event handler.

If I delete the cells that call the UDF then the Worksheet_Change() routine
works as planned.
If I un-volatile the UDF then the Worksheet_Change() routine works as
planned.
But if I leave the UDF in the worksheet and leave it volatile (required)
then the Worksheet_Change() event misbehaves.

I shall post the full code of the UDF and the Worksheet_Change() if it would
help, but I was thinking that perhaps there is a standard procedure for
overcoming this?

Thanks

-Cube
 
What does "the Worksheet_Change() event misbehaves" mean to you?

I put this in my sheet module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
MsgBox Target.Address(0, 0) & " Changed"
End Sub

and put this in a regular code module:

Public Function ReturnRowHeight() As Double
Application.Volatile
ReturnRowHeight = Application.Caller.RowHeight
End Function

I then put several

=ReturnRowHeight()

formulas in the sheet, changed some row heights, and made some
entries. The function updated and the event fired OK each time an
entry was made.

by all means, post the relevant code.
 
by all means, post the relevant code.
Click to expand...

Here goes:

UDF first:

Function RowHeight(r As Range) As Double
Application.Volatile
RowHeight = r.RowHeight
End Function 'RowHeight(r As Range) As Double

'By the way I didn't define it as a Public Function. Don't know whether
that matters but it seems to work without.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rRange As Range
Dim rTarget As Range
Dim bTest As Boolean
If pbMeWriting Then Exit Sub 'Public Dim Boolean in main module
Application.ScreenUpdating = False
'Next line probably superfluous
If Application.ThisWorkbook.ActiveSheet.Index <>
Application.ThisWorkbook.Worksheets("Analysis").Index Then GoTo GrandFinita
bTest = False
Set rTarget = Target.EntireRow
Set rRange = Intersect(Target, Range("Analysis!Transaction_Type_Range"))
If Not rRange Is Nothing Then bTest = True
Set rRange = Intersect(Target, Range("Analysis!Account_2_Range"))
If bTest = False And rRange Is Nothing Then GoTo Test2
bTest = False
Set rRange = Intersect(rTarget, Range("Analysis!VAT_Return_Cat_Range"))
Range("Analysis!VAT_Return_Cat_Template").Copy Destination:=rRange
rRange.Formula = rRange.Value
Set rRange = Intersect(rTarget, Range("Analysis!VAT_Code_Range"))
Range("Analysis!VAT_Code_Template").Copy Destination:=rRange
rRange.Formula = rRange.Value
Test2:
Set rRange = Intersect(Target, Range("Analysis!Vat_Return_Cat_Range"))
If Not rRange Is Nothing Then bTest = True
Set rRange = Intersect(Target, Range("Analysis!VAT_Code_Range"))
If Not rRange Is Nothing Then bTest = True
Set rRange = Intersect(Target, Range("Analysis!Gross_Range"))
If rRange Is Nothing And bTest = False Then GoTo GrandFinita
Set rRange = Intersect(rTarget, Range("Analysis!VAT_Range"))
Range("Analysis!VAT_Template").Copy Destination:=rRange
rRange.Formula = rRange.Value
GrandFinita:
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub 'Worksheet_Change(ByVal Target As Range)

OK, now an example of how the thing fails to fire correctly:
(All references are in worksheet "Analysis")

I change the content of cell P14, that is within range Account_2_Range.
Neither Cell R14 (that is within Vat_Return_Cat_range) nor cell S14 (that is
within VAT_Code_Range) change as a result, when they should.
Ironically, if I hit the delete key on P14 then R14 and S14 do change. It
is only other changes to P14 that causes trouble (there may be other
examples).

-Cube
 
A bit more info on the problem below (is top-posting frowned upon here?):

I changed the UDF to:

Public Function testme03() As Long
Application.Volatile
Dim rngF As Range
Dim cell As Range

testme03 = 0
With Worksheets("Analysis")
With .AutoFilter.Range.Columns(1)
For Each cell In .Offset(1, 0).Resize( _
.Rows.Count - 1).Cells
If Not cell.EntireRow.Hidden Then
testme03 = cell.Row
Exit For
End If
Next cell
End With
End With
End Function

The only cell that contains the formula "=testme03" is NOT in the worksheet
"Analysis" nor are there any cells in "Analysis" that are dependent on the
only cell that contains that formula. And yet the Worksheet_Change() event
behind "Analysis" still fails to operate as expected.

Earlier I wrote:
 
OK - here's my take:

I was able to replicate the problem - the UDF is exiting abnormally
when it refers to a cell inthe sheet that's in the middle of the
Worksheet_Change event, at the "If Not cell.EntireRow.Hidden Then"
line. I'm not sure why this is.

One solution seems to be to turn Calculation off during the
Worksheet_Change event. This worked for me (note that I rewrote your
code a bit - I think the logic is the same):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oldCalc As Integer
If PbMeWriting Then Exit Sub
With Application
.ScreenUpdating = False
oldCalc = .Calculation
.Calculation = xlManual
.EnableEvents = False
End With
With Target
If (Not Intersect(.Cells, Range( _
"Analysis!Account_2_Range")) Is Nothing) Or _
(Not Intersect(.Cells, Range( _
"Analysis!Transaction_Type_Range")) Is Nothing) Then
With Intersect(.EntireRow, _
Range("Analysis!VAT_Return_Cat_Range"))
Range("Analysis!VAT_Return_Cat_Template").Copy _
Destination:=.Cells
.Value = .Value
End With
With Intersect(.EntireRow, _
Range("Analysis!VAT_Code_Range"))
Range("Analysis!VAT_Code_Template").Copy _
Destination:=.Cells
.Value = .Value
End With
End If
If (Not Intersect(.Cells, _
Range("Analysis!Vat_Return_Cat_Range")) Is Nothing) _
And (Not Intersect(.Cells, _
Range("Analysis!VAT_Code_Range")) Is Nothing) And _
(Not Intersect(.Cells, _
Range("Analysis!Gross_Range")) Is Nothing) Then
With Intersect(.EntireRow, _
Range("Analysis!VAT_Range"))
Range("Analysis!VAT_Template").Copy .Cells
.Value = .Value
End With
End If
End With
With Application
.CutCopyMode = False
.ScreenUpdating = True
.Calculation = oldCalc
.EnableEvents = True
End With
End Sub 'Worksheet_Change(ByVal Target As Range)

However, I suspect with copying your template ranges, that you want
them to automatically calculate, before being converted to values.
If that's the case, another solution is to create a global boolean
variable, say gbInSheetChangeEvent, and wrap your Worksheet_Change
code with

gbInSheetChangeEvent = True
'your code here
gbInSheetChangeEvent = False
Application.CalculateFull

then in the UDF, as the first line, use

If gbInSheetChangeEvent Then Exit Function

Like I said, these worked for me in a test workbook - since I don't
have your data, and I'm not sure what all the copying is about, I
don't guarantee it will work for you.
 
J.E. McGimpsey said:
OK - here's my take:

I was able to replicate the problem - the UDF is exiting abnormally
when it refers to a cell inthe sheet that's in the middle of the
Worksheet_Change event, at the "If Not cell.EntireRow.Hidden Then"
line. I'm not sure why this is.

One solution seems to be
Click to expand...

<snip the good stuff>

Yup thanks for that it works for me. Your post was quite educational.

As it happens I only needed the UDF to determine the row number of the first
visible row in an autofiltered table and I have since worked out how to
achieve this without the need of a UDF, but I think I will change my
worksheet_change event along your lines anyway.

-Cube
 
Back
Top