DocBrown
09-21-2009, 08:25 AM
I'm trying to understand what's happening and find a workaround.
In my WB I am using the Worksheet_Change event to invokes a routine, FillAcctCode, that looks in cells in several columns, then based on those columns changes the contents of cells in other related columns. (I can provide more detail if needed.)
If I put ANY User Defined Function in a formula in other cells, then FillAcctCode will fail with error 1004 at the line where I assign the value to cells in those other related columns. That code is:
colCategory = Range("Bud_AllocationTable").Column + 1
colAccount = Range("Bud_AllocationTable").Column
strAcctCode = BuildAccountCode(rngCurrRow)
If Len(Trim(strAcctCode)) > 0 Then
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = strAcctCode <-- Fails here.
Else
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = ""
End If
I believe I'm running into something related to the what I understand is a restriction on UDFs in cells where they aren't allowed to modify other cells. That's fine but my question is why does my other FillAcctCode macro work fine until I add a UDF in other cells.
I've also determined that the UDF is called many times and will fail with other errors I don't understand why. Oh, this is occurring on Excel 2002 (Office XP). It seems to work on 2003 and 2007.
The UDF is trying to determine if any of the AutoFilters in another part of the WS are ON. Here's the code:
Public Function MyFilters(MyRange As Range) As String
Application.Volatile
Dim i As Integer
Dim rng As Range
Debug.Print "-> MyFilters: " & Err.Number & " " & MyRange.Address
On Error GoTo ErrThisFun
MyFilters = False
With ActiveSheet.AutoFilter
Debug.Print "-- MyFilters: Auto:" & .Range.Address
For i = 1 To .Range.Columns.Count
With .Filters(i)
If .On Then
MyFilters = True
End If
End With
Next
End With
GoTo ExitFun
ErrThisFun:
Debug.Print "-- MyFilters: ERR: " & Err.Number
Resume ExitFun
ExitFun:
Debug.Print "<- MyFilters: " & Err.Number
End Function
Some of the time this function is called, it fails accessing the .Autofilter object in that Debug statement and the For statement with error 91. That's really unexpected. Any insights into what's going on?
Thanks,
DocBrown
In my WB I am using the Worksheet_Change event to invokes a routine, FillAcctCode, that looks in cells in several columns, then based on those columns changes the contents of cells in other related columns. (I can provide more detail if needed.)
If I put ANY User Defined Function in a formula in other cells, then FillAcctCode will fail with error 1004 at the line where I assign the value to cells in those other related columns. That code is:
colCategory = Range("Bud_AllocationTable").Column + 1
colAccount = Range("Bud_AllocationTable").Column
strAcctCode = BuildAccountCode(rngCurrRow)
If Len(Trim(strAcctCode)) > 0 Then
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = strAcctCode <-- Fails here.
Else
ActiveSheet.Cells(rngCurrRow.Row, colAccount).Value = ""
End If
I believe I'm running into something related to the what I understand is a restriction on UDFs in cells where they aren't allowed to modify other cells. That's fine but my question is why does my other FillAcctCode macro work fine until I add a UDF in other cells.
I've also determined that the UDF is called many times and will fail with other errors I don't understand why. Oh, this is occurring on Excel 2002 (Office XP). It seems to work on 2003 and 2007.
The UDF is trying to determine if any of the AutoFilters in another part of the WS are ON. Here's the code:
Public Function MyFilters(MyRange As Range) As String
Application.Volatile
Dim i As Integer
Dim rng As Range
Debug.Print "-> MyFilters: " & Err.Number & " " & MyRange.Address
On Error GoTo ErrThisFun
MyFilters = False
With ActiveSheet.AutoFilter
Debug.Print "-- MyFilters: Auto:" & .Range.Address
For i = 1 To .Range.Columns.Count
With .Filters(i)
If .On Then
MyFilters = True
End If
End With
Next
End With
GoTo ExitFun
ErrThisFun:
Debug.Print "-- MyFilters: ERR: " & Err.Number
Resume ExitFun
ExitFun:
Debug.Print "<- MyFilters: " & Err.Number
End Function
Some of the time this function is called, it fails accessing the .Autofilter object in that Debug statement and the For statement with error 91. That's really unexpected. Any insights into what's going on?
Thanks,
DocBrown