UDF Issue - Recalculating on Multiple Worksheets

[Agame21]

Hello,

I am experiencing an error in my spreadsheet that is causing a lot of confusion and I cannot pinpoint the source...

I have multiple worksheets with numerous cost items on each worksheet. Each item is assigned a Type in Column B. Some of these costs have been given red text and some have green text. The goal of my spreadsheet is to track the costs of red and green items by type on multiple worksheets.

Excel 2010
	B	C	D	E	F	G	H
2	Type	Cost Item	Option 1	Option 2	Option 3		Option 1
3	1	Item A	$ 55	$ 61	$ 60
4	1	Item B	$ 281	$ 305	$ 299
5	3	Item C	$ 90	$ 77	$ 85
6	2	Item D	$ 125	$ 123	$ 142
7	1	Item E	$ 168	$ 194	$ 150
8	2	Item F	$ 20	$ 18	$ 17
9	2	Item G	$ 59	$ 57	$ 66
10	3	Item H	$ 179	$ 169	$ 165
11
12		Red Costs
13	1	Type 1	$ 180	$ 77	$ 359		$ 180
14	2	Type 2	$ 168	$ 18	$ 167		$ 168
15	3	Type 3	$ -	$ 226	$ 165		$ -
16
17		Green Costs
18	1	Type 1	$ 371	$ 61	$ 142		$ 371
19	2	Type 2	$ 20	$ 194	$ -		$ 20
20	3	Type 3	$ -	$ -	$ -		$ -
Sheet1

Excel 2010
	B	C	D	E	F	G	H
2	Type	Cost Item	Option 1	Option 2	Option 3		Option 3
3	1	Item A	$ 10	$ 15	$ 10
4	1	Item B	$ 20	$ 30	$ 20
5	1	Item C	$ 10	$ 10	$ 10
6	1	Item D	$ 10	$ 10	$ 10
7	2	Item E	$ 20	$ 20	$ 10
8	2	Item F	$ 20	$ 10	$ 10
9	3	Item G	$ 10	$ 10	$ 10
10	3	Item H	$ 30	$ 10	$ 10
11
12		Red Costs
13	1	Type 1	$ 20	$ 10	$ 30		$ 30
14	2	Type 2	$ 20	$ 10	$ 20		$ 20
15	3	Type 3	$ -	$ 20	$ 10		$ 10
16
17		Green Costs
18	1	Type 1	$ 30	$ 15	$ 10		$ 10
19	2	Type 2	$ 20	$ 20	$ -		$ -
20	3	Type 3	$ -	$ -	$ -		$ -
Sheet2

Cell H2 is a dropdown box where the user selects which option they wish to report on the summary table. The cells below in Column H use a SUMIF to select the corresponding numbers for the selected option.

The main calculation is being performed with a UDF. I am fairly inexperienced with UDFs and used multiple internet sources to develop the code below:

Public Function SumColorArea(pRange1 As Range, pRange2 As Range, Area1 As String) As Double
Application.Volatile
Dim wk As Worksheet
Dim rng As Range
Dim xTotal As Double
With ActiveWorkbook.ActiveSheet
xTotal = 0
For Each rng In pRange1
If rng.Font.Color = pRange2.Font.Color And Range("B" & rng.Row).Value = Area1 Then
xTotal = xTotal + rng.Value
End If
Next
SumColorArea = xTotal
End With
End Function

I am using the following synatx in cell D13 and copying it through the rest of the calculated cells: =SumColorArea(D$3:D$10,$C13,$B13)

Here is the summary table on a different worksheet:

Excel 2010
	B	C	D	E	F	G	H	I	J	K
2			Red				Green
3			Type 1	Type 2	Type 3	Total	Type 1	Type 2	Type 3	Total
4	Sheet 1	Option 1	180	168	0	348	371	20	0	391
5	Sheet 2	Option 3	30	20	10	60	10	0	0	10
Summary

Now to my issue...

Everytime I recalculate one sheet, it changes the results on the other. These are completely separate sets of numbers and should have no impact on eachother. Likewise, when I hit F9 or recalculate on the summary tab, it turns every number on that table and the tables on the other sheets to 0.

There has to be something in my UDF that is causing the issue but I can't find what it is. Any help at all would be greatly appreciated. Please let me know if I need to clarify anything else.

 

[Jerry Sullivan]

Hi Agame21,

The reason for the incorrect results is that the UDF is referencing...

Range("B" & rng.Row).Value

I think your intent was to reference Column B on the same sheet that has the Prange cells passed to the UDF, but VBA doesn't interpret it that way.

Because there's no explicit worksheet reference, VBA interprets that as a reference to the ActiveSheet in the ActiveWorkbook, whenever that formula is calculated. So if you activate Sheet1 then recalculate your formulas, the UDF formulas on Sheet2 will be reading the Type criteria from Sheet1 Column B.

BTW, The statement...

With ActiveWorkbook.ActiveSheet

...is ignored because their are no expressions within the With block that reference that with a "." dot.
But that's actually a good thing because you don't want to reference the ActiveSheet.

To reference Column B on the same sheet as Prange1, use

Prange1.Parent

(the Parent object of a range is its Worksheet).

Here's a revised version that should return the correct results....

Public Function SumColorArea(pRange1 As Range, _
   pRange2 As Range, Area1 As String) As Double
   
 Application.Volatile
 
 Dim wk As Worksheet
 Dim rng As Range
 Dim xTotal As Double
 
 Set wk = pRange1.Parent
 xTotal = 0
 For Each rng In pRange1
   If rng.Font.Color = pRange2.Font.Color And _
         wk.Range("B" & rng.Row).Value = Area1 Then
      xTotal = xTotal + rng.Value
   End If
 Next rng
 
 SumColorArea = xTotal
 
End Function

I'm not sure if you were questioning why the UDF's recalculate on sheets that aren't Active or changed.

The statement Application.Volatile directs VBA to recalculate the UDF each time there is a calculation in any open workbook in the Application.

If you eliminated that statement, then the UDF would only recalculate when a cell upon which the UDF is dependent is changed.

So for the formula in Sheet1!D13, if any of the cells in D$3:D$10,$C13,$B13 change the UDF will recalculate.

The problem is that if one the cells in B3:B10 (Types) changes. Those cells are not referenced directly as arguments of the formula, so if B5 changes, then D13 will not be recalculated automatically.

Because of that, you should either leave the Application.Volatile statement in your UDF, or modify your UDF to reference the Types (e.g. B3:B10) as an argument. The latter is a more robust design and makes your UDF less specific to this workbook layout. The only downside is it adds another argument to your formulas making them a little longer.

 

[Agame21]

Wow, thank you so much, Jerry!

I really appreciate you taking the time to not only point out the error but also to explain why it was producing the results that it was. Your revised version does exactly what I was trying to accomplish.

For now I will keep the Application.Volatile statement but will play around with adding another argument for the Types as you suggested.

Thanks again!