UDF causes other macro to fail w/ err 1004

[DocBrown]

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

[Bob Phillips]

Can you post the workbook?

[DocBrown]

It's a pretty complicated WB. The file starts as a template and when a xls file is created from the template, the Auto_Open macro does some checks for updates to a worksheet in the original template file. there's several other complex migration and data manipulation routines.

There's also complicated dependent lists that are referenced and other functions. I'm trying to distill down the essence of my problem, but it could take a bit. So far, I can't duplicate the issue in my stripped down test file I'm creating.

I'll try to create a stripped down version. But in the mean time is there something I could look at to try and see what's happening? Is there other data I could gather to help with this?

Thanks,
John

[DocBrown]

OK, here's a version of the WB that shows the problem. I've removed all the functions that I could. To see the error, click in a blue cell in the Budget by Category table. From the drop down, select any entry. You will see an error window displayed by the FillAcctCode subroutine. In VBA, in the immediate window you can see debug statements from the key routines. -> enter and <- exit.

The cell L10 contains the formula =MyFilters() which is the UDF I'm trying to implement. The L10 cell is referenced in Cells J13:J25 and Q13:Q15. To see the purpose of the UDF select an AutoFilter item from the Category field of the header of the main table. The cells in the regions J13:J25 and Q13:Q15 will become blank.

To make the error go away, clear the contents of cell L10. When L10 is true, the regions blank. When L10 is false, the remaining balance is calculated from Allocated budget and Expense Subtotal.

I hope you can help figure out what's going on.

Much thanks,
John (DocBrown)

[DocBrown]

I have localized the error 91 which occurs in Excel 2007. I am getting from the MyFilters() routine. This is really strange. BTW, the 1004 error doesn't seem to occur in Excel 2007

In the sample WB I uploaded, the following is the output of the debug statements included in the code:

-> MyFilters:
<- MyFilters: 0
-> Worksheet_Change: $G$16
-> FillAcctCode: 0
-> MyFilters:
<- MyFilters: 0
<- FillAcctCode: 0
-> GetUniqueAccts:
-- GetUniqueAccts: $E$13
-- GetUniqueAccts: $E$14
-- GetUniqueAccts: $E$15
-- GetUniqueAccts: $E$16
-- GetUniqueAccts: $L$30
-- GetUniqueAccts: $L$31
-- GetUniqueAccts: $L$32
-> MyFilters:
-- MyFilters: ERR: 91
<- MyFilters: 0
<- GetUniqueAccts:
<- Worksheet_Change:


Notice the ERR 91 on the third call. This is occurring at this line in MyFilters()

With ActiveSheet.AutoFilter
'Debug.Print "-- MyFilters: Auto:" & .Range.Address
For i = 1 To .Range.Columns.Count <--- Fails here

Apparently, the current call environment, the AutoFilter object has ceased to exist or is not accessible for some reason. Setting a breakpoint just before the above code, the following is found from the Immediate window:

?activesheet.autofiltermode
True
?activesheet.autofilter.range.address
<Run-time error 91:
Object variable or With Block variable not set.

Looking at the call stack shows that the call to MyFilters() is being made indirectly via 'Non-Basic code' from the GetUniqueAccts at this line:

Range("l13:o25").Sort key1:=Range("L13"), order1:=xlAscending

Any ideas how to stop this error? Or I guess I might need to just ignore it
?
John (DocBrown)

[Bob Phillips]

I get a different experience, I get a 1004 in Excel 2007. It fails on clearing the cell adjacent to the cell being processed.

[DocBrown]

If you are referring to the Account Code column, that would make sense because the Named Region that is processed by the FillAcctCode macro includes that column.

I haven't investigated cells other than those blue ones in the header and working with the data in the Expenditure table below the autofilter. In use, the WSs are protected and users can only modify the blue cells and the Expenditure table.

[Bob Phillips]

It is the code that is trying to modify the non-blue cell, whicjh I guess intended, but it is failing, for why I don't yet understand.

[mdmackillop]

I'm not finding any problems in Excel 2003. The only thing I see though is that the Q cells reference K12, not L10

[DocBrown]

Yes, that seems to be my conclusion also. The main problem occurs on Excel 2002 (Office XP) but not on newer versions. Those cells are referencing K12 because I forgot to update them when I created my test version of my workbook. The cells in J13:J25 reference the L10. In my full WB, I hide the results of the MyFilters in cell K12. I did because I have the following code in the auto_open macro:

' Workaround issue where UDF causes FillAcctCode to fail. If we are on XL2002 or earlier
' Disable call to check if filters are engaged. This means we can't blank the Remaining
' Balance cells if filters are engaged.
If Application.Version < 11 Then
Range("K12").Value = ""
Else
Range("K12").Value = "=MyFilters()"
End If
This allows the WB to work on 2002, but without that blanking feature.