Excel은 다시 입력하지 않는 한 VBA 함수가 있는 공식을 오차로 계산하고 있습니다.

Excel은 다시 입력하지 않는 한 VBA 함수가 있는 공식을 오차로 계산하고 있습니다.

if 조건이 VBA 사용자 정의 함수인 워크시트에 간단한 if 문을 설정했습니다.

Function CellIsFormula(ByRef rng)
    CellIsFormula = rng(1).HasFormula
End Function

이 기능은 정상적으로 작동하는 것 같습니다.

하지만 제가 알아낼 수 없는 어떤 이유 때문에, 세포는 오류를 평가하고 있습니다.더 나쁜 것은 공식을 평가할 때 Excel이 오류를 발생시키지 않는 계산 단계로 돌리고 있다는 것입니다.

모든 것을 끝내고, 정말로 내 마음을 뒤흔드는 것은 내가 단순히 공식을 다시 입력하거나 전체 재계산(++)CtrlAltF9을 강요한다면 공식은 문제가 없다고 평가한다는 것입니다!

저는 공식을 휘발성으로 만들려고 시도했습니다.Application.Volatile기능 코드로 변경되지 않았습니다.계산을 수동으로 설정했다가 다시 자동으로 설정하거나, "재계산 시트"를 숨기거나,F9 또는 +만 사용하거나, 공식 또는 AltF9++만 다시 입력하면 함수가 제대로 재계산됩니다.

if 문에서 참조한 셀 중 하나를 변경해도 문제가 해결되지 않지만 "CellIsFormula" 함수에서 참조한 셀을 변경하면 문제가 해결됩니다.그러나 시트를 다시 열 때마다 오류가 다시 발생합니다.

저는 정확한 문제를 발견했지만, 저는 제가 이 문제를 해결하는 데 도움을 주려고 노력한 여러분 모두에게 찬성표를 던지고 싶고, Gerg에게 공을 주고 싶습니다. 왜냐하면, 제가 완전히 운이 없었던 것은 아니지만, 그는 완전히 그의 제안을 하고 있었기 때문입니다.

Excel은 특정 계산 단계에서 특정 범위의 특정 속성을 사용할 수 없도록 설정하는 것을 좋아합니다.

Gerg를 잘 찾았습니다.

문제는 이벤트 처리기에 있습니다.워크북에는 Workbook_Open, Workbook_Change 등과 같은 일련의 이벤트 처리기가 포함되어 있습니다.때때로 이러한 이벤트 핸들러가 수행하는 작업 중 하나로 인해 워크북의 일부 셀이 다시 계산됩니다.매크로가 실행되는 동안 Excel이 재계산을 트리거하면 이 UDF를 포함하는 모든 셀에 오류가 발생합니다.이는 어떤 이유에서인지 VBA가 재계산을 트리거하는 동안 에 해당하기 때문입니다.@GSERG가 말한 것처럼 HasFormula 속성을 사용할 수 없었습니다.

아마도 - 다음 비트는 Excel의 부분에 대한 실수이지만, 매크로 실행이 완료되면 재계산이 수행되어 UDF가 제대로 실행되지 않아 오류가 발생하면 Excel은 UDF를 다시 실행하려고 시도하지 않습니다.결과 오류 값은 호출의 반환 값으로 가정되며, UDF에 대한 매개 변수가 변경되었다고 생각하지 않는 한 변경되지 않습니다.Excel은 셀의 매개 변수 참조가 변경될 때까지 사용자 정의 함수 호출의 결과를 캐시합니다.

그렇기 때문에 '공식 평가' 단계를 수행하면 마지막 단계까지 작동하는 모든 것이 표시됩니다. 마지막 단계는 실제로 평가하지 않고 마지막으로 계산한 대로 스프레드시트의 값만 표시됩니다.

해결책

실제로 두 가지 가능한 해결책이 있었습니다.제가 찾은 첫 번째 해결책은 이벤트 핸들러를 시작할 때 자동 계산을 사용하지 않도록 설정하고 나중에 다시 사용하도록 설정하는 것이었습니다.어떤 이유로 인해 매크로가 실행 중일 때에도 계산이 xlCalculation으로 다시 설정됩니다.자동으로 UDF가 다시 평가되고 속성을 사용할 수 있습니다.

제가 선호하는 두 번째 해결책은 실수로 다시는 이런 일이 일어나지 않도록 하기 때문에 공식을 확인하기 위해 다른 방법을 사용하는 것입니다.

Function CellIsFormula(ByRef rng As Range) As Boolean
    CellIsFormula = Left(rng(1).Formula, 1) = "="
End Function

.Formula 속성은 사용할 수 없습니다.그래서 이 문제는 절대 발생하지 않습니다.

이 오류를 재현할 수 없습니다.

1. 서명은 다음과 같아야 합니다.

   Public Function CellIsFormula2(ByVal rng As Range) As Boolean
     CellIsFormula2 = rng.Cells(1).HasFormula
   End Function
   

2. Excel은 특정 계산 단계에서 특정 범위의 특정 속성을 사용할 수 없도록 설정하는 것을 좋아합니다.저는 여러 번 봤습니다..Text속성을 갑자기 사용할 수 없습니다.따라서 서명을 변경하는 것이 효과가 없다면 운이 없을 수도 있습니다.

문제는 'HasFormula' 속성이 부울이 아닌 변형을 반환하기 때문이라고 생각합니다.범위에 수식과 값이 혼합된 경우 HasFormula는 null을 반환합니다.또한 rng를 Range 개체로 정의하지 않고 출력 유형을 지정하지 않습니다.저는 이런 접근법을 제안합니다.부울을 매우 쉽게 반환하도록 수정할 수 있습니다.

Public Function CellIsFormula(rng As Range) As String

Application.Volatile

    Dim testVal As Variant

    testVal = rng.HasFormula 'HasFormula returns variant type

    'testval is null if cells are mixed formulas and values
    If IsNull(testVal) Then
        testVal = "Mixed"
    End If

    Select Case testVal
        Case True
            CellIsFormula = "All Cells in Range Have formula"
        Case False
            CellIsFormula = "No Cells in Range Have formula"
        Case "Mixed"
            CellIsFormula = "Some Cells in Range Have formula"
        Case Else
            CellIsFormula = "Error"
    End Select
End Function

언급URL : https://stackoverflow.com/questions/6470263/excel-is-calculating-a-formula-with-a-vba-function-as-an-error-unless-it-is-re-e