Is this a bug in Excel?

[D-niss]

Not sure if anyone's good enough to figure this out, but I have difficulty with a Worksheet_Change event procedure.

I did some research but didn’t find any satisfying answer.

Basically the following procedure runs fine by itself, but throws an error if a UDF is present. It appears that the UDF event triggers before the Worksheet_Change event, thus causing an error if target is a range instead of an individual cells. But I don't know if that's the cause for the error.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Err_End
Dim uCell As Range

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Target.Cells.Count > 1 Then
For Each uCell In Target
Cells(uCell.Row, 4) = 1
Cells(uCell.Row, 5) = 1
Next
End If

Err_Resume:
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Exit Sub

Err_End:
MsgBox Err.Description
Resume Err_Resume

End Sub


Now put this UDF in a module; copy one cell to a range of cells that is part of the UDF range argument.

This function sums the cell in the range argument if the row is visible e.g. =sumvisible(A1:A10)


Function SUMIFVISIBLE(MyRange As Range) As Single
On Error GoTo Err_SUMIFVISIBLE

Dim Rng As Range
Dim DaTotal As Single

DaTotal = 0

For Each Rng In MyRange
If Rng.EntireRow.Hidden = False Then
DaTotal = DaTotal + Rng.Value
End If

Next Rng

SUMIFVISIBLE = DaTotal
Exit Function



Err_SUMIFVISIBLE:
MsgBox Err.Description
End Function

Does any one have any idea what’s the problem? Boy would I ever appreciate to know the answer to that.

Thanks

[DKenny]

Try changing this line in the SUMIFVISIBLE function

For Each Rng In MyRange

to

For Each Rng In MyRange.Cells

Also, I would refer to MyRange ByVal, rather than ByRef.

[D-niss]

Hi there DKenny!

Ah man that was to good to be true. It's still giving me an application.defined or object-defined error at line Cells(uCell.Row, 4) = 1

I've been on this since last week; if one cell is copied to a range of cells within the range of the UDF argument, it gives an error in the Worksheet_Change procedure.

I whish someone could take 2 min to really try it out, cause I've been doing coding in XL for ages and this one eludes me.

Thanks all the same.

Dniss

[DKenny]

Dniss

I tested it and now see the issue - not sure of the solution yet but...

As a possible solution, have you considered using the built in Excel function SUMIF rather than your custom function?
If you set the Function_Num argument of this function to 109, it will sum only the visible rows. (Just an idea)

[DKenny]

PS. I re-tested using SUMIF and cannot replicate the error.

[D-niss]

Dniss

I tested it and now see the issue - not sure of the solution yet but...

As a possible solution, have you considered using the built in Excel function SUMIF rather than your custom function?
If you set the Function_Num argument of this function to 109, it will sum only the visible rows. (Just an idea)

It's a tough one hey? I was expecting some of our MVP to put their two cents.. no luck I guess.

At least if the error desc was more explicit. I tried just about everything I could think of with no success.

I thought visible cells (criteria) only worked with subtotal function. If you're right you just made my day, I'll drop the UDF altogether.

But regardless, for academic sake, if you find why this error arise you'll have my endless devotion.

[DKenny]

From the Excel Help File

For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the Hide command under the Row submenu of the Format menu). Use these constants when you want to subtotal only nonhidden numbers in a list.

As per my previous post, I tested this - got the same answer as your UDF but no errors. I still want to understand why its throwing an error though, so will keep investigating.

[D-niss]

Dniss

I tested it and now see the issue - not sure of the solution yet but...

As a possible solution, have you considered using the built in Excel function SUMIF rather than your custom function?
If you set the Function_Num argument of this function to 109, it will sum only the visible rows. (Just an idea)

Would you mind being more explicit on the "setting the arg to 109"?

Do you mean something like =SUMIF(a1:a10,"109") ?

[DKenny]

Sorry, my bad -typing too fast. I should have said the SUBTOTAL function.

Please do a mental FIND and REPLACE on all my previous posts.

Example

=SUBTOTAL(109,a1:a10)

[D-niss]

From the Excel Help File


As per my previous post, I tested this - got the same answer as your UDF but no errors. I still want to understand why its throwing an error though, so will keep investigating.

lol you completly lost me there. Do you mean the subtotal function or the sumif function? I can't use subtotal cause it has to be part of a filter. That would require to much change to my template.

Worst to worst I'll write a proc that calculates those cells upon a Worksheet_calculate event.

[D-niss]

Sorry, my bad -typing too fast. I should have said the SUBTOTAL function.

Please do a mental FIND and REPLACE on all my previous posts.

Example

=SUBTOTAL(109,a1:a10)

lol OK Declan, that's what I thought.

If you ever find what's causing the error let me know, it could be usefull to know for future reference.

Tx bro

[D-niss]

On topic:

You know before posting this here, I've made considerable reaserch on UDF to try to find the problem by myslef. I mean I'm no slouch when it comes to digging up stuff.

I did see something about UDF using multiple areas causing excel to be confused about which sheet is the active sheet.

But honestly, I think I just found a bug in Excel.