Excel 셀에서 호출된 VBA 함수가 F9, Shift+F9 등을 사용하여 자동으로 업데이트되거나 수동으로 업데이트되지 않는 데 어려움을 겪고 있습니다.
Windows 10에서 "Microsoft 365 MSO용 Microsoft® Excel®(버전 2207 빌드 16.0.15427.20166) 32비트"를 사용하고 있습니다.
예산을 준비하는 데 사용되는 다양한 값의 테이블이 있습니다. 각 열은 팀 구성원이고 각 행은 작업입니다. 각 사용자에게는 각 작업에 대한 시간이 할당됩니다. 각 작업에는 이름별로 승수가 할당됩니다.
간단한 예를 들면 다음과 같습니다.
| 작업 [cellA1] | 사람 1 | 사람 2 | 승수 |
|---|---|---|---|
| 첫 번째 작업 | 1.00 | 0.25 | 주제 |
| 두 번째 작업 | 0.05 | 0.15 | 참가자 |
표시된 대로 개인 1은 주제당 1시간, 참가자당 0.05시간이 필요합니다. 2인에게는 주제당 0.25시간, 참가자당 0.15시간이 필요합니다.
주제 및 참가자 수는 명명된 범위(예: "_topics", "_participants")에 포함됩니다. "주제" 및 "참가자"는 자리 표시자 이름입니다. 무엇이 중요한지는 중요하지 않습니다("개" 또는 "고양이"일 수 있음). 중요한 것은 각각에 단일 숫자 값이 할당되어 있다는 것입니다. 작업은 승수를 공유할 수 있습니다(즉, 둘 이상의 행이 "주제"를 사용할 수 있음).
목표는 매우 간단한 입력(주제 수, 참가자 수 등 변경)을 사용하여 복잡한 예산(많은 구성원 및 많은 작업)의 요구 사항을 모델링하는 것입니다.
이것이 작은 테이블이라면 다음과 같은 셀 내 수식을 사용합니다.
=sumif(D1:D2,"Topic",B1:B2) * _topic + sumif(D1:D2,"Participant",B1:B2) * _participants
_topic = 5이고 _participant = 10인 경우 개인 1의 총계는 5.5가 됩니다 (1 * 5 + 0.05 * 10).
제 경우에는 행과 승수가 많고 작업을 프로젝트 단계에 할당하는 또 다른 열도 있으므로 셀 내 콘텐츠를 읽기 쉽게 유지하고 항목을 더 쉽게 추가할 수 있도록 VBA 함수를 작성했습니다. 미래. 우아하지는 않지만 필요한 기능을 수행합니다.
Function SUM_MEMBER_VALUES(phase As String, data_range As Range, phase_range As Range, mult_range As Range)
p = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Project")
mc = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Market_collections") * _
Range("_markets_collections").Value
mr = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Market_calls") * _
Range("_markets_calls").Value
c = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Caller") * _
Range("_callers").Value
b = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand") * _
Range("_brands").Value
bmc = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Market_collections") * _
Range("_brand_markets_collections").Value
bmr = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Market_calls") * _
Range("_brand_markets_calls").Value
bmpc = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Market-Product_collections") * _
Range("_brand_market_products_collections").Value
bmpr = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Market-Product_calls") * _
Range("_brand_market_products_calls").Value
bp = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Product") * _
Range("_brand_products").Value
uc = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Unsuccessful Calls") * _
Range("_calls_unsuccessful").Value
br = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Responses") * _
Range("_brand_responses").Value
pr = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Product-Responses") * _
Range("_product_responses").Value
SUM_MEMBER_VALUES = p + mc + mr + c + b + bmc + bmr + bmpc + bmpr + bp + uc + br + pr
End Function
이 함수는 다음과 같은 테이블에서 in-cell이라고 합니다.
| 단계 [cellA1] | 사람 1 | 사람 2 | 총 |
|---|---|---|---|
| 킥오프 | =SUM_MEMBER_VALUES($A2,[person1의 범위],[작업 단계 범위],[작업 승수 범위]) | =SUM_MEMBER_VALUES($A2,[person2의 범위],[작업 단계 범위],[작업 승수 범위]) | =합(B2:B3) |
이 기능은 작동합니다. 내가 가진 문제는 명명된 변수의 값을 변경할 때 자동으로 업데이트되지 않는다는 것입니다. 따라서 _topic이 5에서 10으로 변경되면 결과는 SUM_MEMBER_VALUES변경되지 않습니다.
계산 옵션이 자동 F9, SHIFT+F9로 설정되어 있고 다시 계산하기 위한 리본 버튼이 업데이트를 트리거하지 않습니다. 파일이 매크로를 활성화했으며 파일을 닫거나 다시 열고 컴퓨터를 재부팅해 보았습니다.
reslts를 업데이트할 수 있었던 유일한 방법은 호출하는 각 셀에 대해 수동으로 입력하고 Enter 키를 누르는 것입니다 SUM_MEMBER_VALUES. 이를 무시하는 것은 번거롭고 셀 업데이트를 놓치기 쉽기 때문에 위험하기도 합니다(특히 나 이외의 누군가가 의도한 모델을 사용하는 경우).
내가 생각할 수 있는 것은 값이 함수에 전달되거나 참조되는 방식으로 인해 값이 변경되었음을 인식하지 못할 수도 있다는 것입니다(예: 값을 Range로 전달했는데 Range가 변경되지 않았더라도 Range의 내용이 그랬습니다). 그래도 해결 방법이 생각나지 않습니다.
질문:입력 값이 변경되면 VBA 함수가 자동으로 업데이트되도록 하는 방법이 있습니까?
업데이트
SHIFT+CTRL+ALT+F9종속성 트리를 다시 작성하고 전체 재계산을 수행합니다. 또한 Application.Volatile함수 맨 위에 추가하면 워크시트가 변경될 때마다 강제로 다시 계산되어야 합니다. 그러나 일부 기여자는 업데이트가 필요하지 않은 경우에도 업데이트를 강제하므로 이에 대해 경고했습니다.1
이것은 내 즉각적인 요구 사항을 해결하지만 솔루션 사이에 해결책이 있는 사람이 있는지 궁금합니다(수동 재계산이 필요하지 않고 글꼴이 변경될 때마다 다시 계산하지 않음).
답변1
가능한 절충안은 테이블의 "이전" 값을 포함하는 함수에 정적 변수를 추가하고 변경 사항이 없으면 코드의 나머지 부분을 건너뛰는 것입니다. 진흙처럼 깨끗합니까?
ListObject 이름이 Tbl1이고 ListColumn 이름이 열 헤더 이름(Task, Person 1, Person 2, Multiplier)이라고 가정해 보겠습니다.
Static sTaskOld$, sP1Old$, sP2Old$, sMultOld$
Dim sTask$, sP1$, sP2$, sMult$
Application.Volatile
sTask = Join(Application.Transpose([Tbl1[Task]]),"")
sP1 = Join(Application.Transpose([Tbl1[Person 1]]),"")
sP2 = Join(Application.Transpose([Tbl1[Person 2]]),"")
sMult = Join(Application.Transpose([Tbl1[Multiplier]]),"")
'first recalc, static variables will be null
if sTask<>sTaskOld or sP1<>sP1Old or sP2<>sP2Old or sMult<>sMultOld then
...
end if
'save existing values to static variables
sTaskOld = sTask
sP1Old = sP1
sP2Old = sP2
sMultOld = sMult
물론 개별 변수 대신 배열을 사용할 수도 있지만 이 예는 개념적입니다.
함수는 계속 실행되지만 최소한 이전 값과 현재 값을 비교하는 데 많은 처리 시간을 소비하지는 않습니다.