VBA User Defined Function returning #VALUE when another Workbook with similar Sheets is open

Question

I have a workbook with multiple User Defined Functions that sometimes give a #VALUE error. I have determined that these UDFs give a #VALUE error when another workbook with similar sheet names/tab colors is open at the same time as the workbook with the UDFs. The UDFs refer to other worksheets using tab color within the workbook to calculate its value. So my guess is that these UDFs are giving a #VALUE error because it cannot distinguish the difference in similarly named/colored worksheets in different open workbooks.

For example, two open workbooks with the same tab color give these UDFs a #VAULE error. I confirmed this by opening a new worksheet with generic sheet names and white tab colors, and the UDFs did not give this error. These UDFs otherwise work perfectly as intended, except for this occurrence. Below is just an example of one of the UDFs that give this error under the circumstances. I have tried to fix this error by referencing "ThisWorkbook", but that does not seem to correct the error. I even tried moving the function to the "ThisWorkbook" Module, but apparently functions do not populate in excel in this module. I cannot figure out how to fix this issue. Your help is greatly appreciated!

Function ExpenseActualSum(Month)

Application.Volatile
ColumnNumber = Month.Column - 1
ExpenseMonthSum = 0
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
    If WS.Tab.Color = 255 Then

        For Each Tbl In WS.ListObjects
            If Tbl.Name Like "Actual*" Then
            TableName = Tbl.Name
            Exit For
            End If
        Next Tbl

    ColumnSum = Application.WorksheetFunction.Sum(Range(TableName & "[[#All],[Column" & ColumnNumber & "]]"))
    ExpenseMonthSum = ExpenseMonthSum + ColumnSum
    End If
Next WS

ExpenseActualSum = ExpenseMonthSum

End Function

@MarcoGetrost Wow, I cant believe it! It worked! Thank you so much for your help! — Mitch103
– Mitch103, Commented Apr 4, 2018 at 15:31

Mathieu Guindon · Accepted Answer · 2018-04-04 15:49:06Z

7

You've been bitten by implicit qualifiers.

When Range isn't explicitly qualified with a specific Worksheet object instance, it becomes an implicit member call on the [_Global] object.

Unqualified Range calls therefore implicitly refer to whatever worksheet is currently active, in whatever workbook happens to be active.

When you mean to work against a specific Worksheet object, like WS in your case, then you should qualify Range, Rows, Columns, Names, and Cells calls with that worksheet object.

That's why ColumnSum = Application.WorksheetFunction.Sum(WS.Range(...)) fixes it.

ThisWorkbook refers to the specific workbook where the VBA code is written in, which may or may not be the workbook WS belongs to. That's why it didn't work.

answered Apr 4, 2018 at 15:49

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

warner_sc Over a year ago

Very elegant answer!

Collectives™ on Stack Overflow

VBA User Defined Function returning #VALUE when another Workbook with similar Sheets is open

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related