Определяемая пользователем функция VBA, возвращающая # ЗНАЧЕНИЕ, когда открыта другая книга с похожими листами

Question

У меня есть рабочая книга с несколькими пользовательскими функциями, которые иногда выдают ошибку #VALUE. Я определил, что эти пользовательские функции выдают ошибку #VALUE, когда другая рабочая книга с аналогичными именами листов / цветами вкладок открыта одновременно с рабочей книгой с пользовательскими функциями. Пользовательские функции ссылаются на другие рабочие листы, используя цвет вкладки в рабочей книге, чтобы вычислить ее значение. Поэтому я предполагаю, что эти пользовательские функции выдают ошибку #VALUE, потому что она не может различить разницу в одноименных / цветных рабочих листах в разных открытых рабочих книгах.

Например, две открытые книги с одинаковым цветом вкладки дают этим пользовательским функциям ошибку #VAULE. Я подтвердил это, открыв новый лист с общими именами листов и белыми цветами табуляции, и пользовательские функции не выдавали эту ошибку. Эти UDF иначе работают отлично, как и предполагалось, за исключением этого случая. Ниже приведен пример одного из UDF, которые выдают эту ошибку в данных обстоятельствах. Я пытался исправить эту ошибку, ссылаясь на "ThisWorkbook", но, похоже, это не исправляет ошибку. Я даже пытался переместить функцию в модуль "ThisWorkbook", но, очевидно, функции не заполняются в Excel в этом модуле. Я не могу понять, как решить эту проблему. Ваша помощь очень ценится!

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

Answer 1

Вас укусили неявные квалификаторы.

когда Range явно не квалифицирован с конкретным Worksheet экземпляр объекта, он становится неявным вызовом члена на [_Global] объект.

неквалифицированный Range Поэтому вызовы неявно ссылаются на то, какой лист в настоящее время активен, в какой бы книге он ни был активен.

Когда вы хотите работать против конкретного Worksheet объект, как WS в вашем случае, тогда вы должны квалифицироваться Range, Rows, Columns, Names, а также Cells вызовы с этим объектом листа.

Вот почему ColumnSum = Application.WorksheetFunction.Sum(WS.Range(...)) исправляет это.

---

ThisWorkbook относится к конкретной книге, в которой написан код VBA, которая может быть или не быть книгой WS принадлежит. Вот почему это не сработало.