Mac용 Excel의 Visual Basic Editor에서 발생한 “변수가 지원되지 않는 자동화 형식을 사용합니다.” 오류

마지막 업데이트: 2019년 4월 10일

문제

Mac용 Excel에서 2017년 10월에 새로운 Visual Basic Editor가 도입되었습니다. 이 새로운 VB Editor를 사용할 때 코드 창의 맨 위에 있는 드롭다운 메뉴에서 선택하여 개체 선언을 작성하면 문제가 발생합니다.

왼쪽의 드롭다운을 클릭하고 목록에서 항목을 선택하면 오른쪽의 드롭다운 목록에 선택한 이벤트에 대한 코드가 표시됩니다. 오른쪽의 드롭다운 목록에서 항목을 추가로 선택하여 선택한 이벤트를 처리하는 코드를 추가로 작성할 수 있습니다.

대신 코드가 작성되지 않았다는 오류가 발생합니다.

Microsoft Visual Basic 오류: 변수 사용 및 자동화 형식이 Visual Basic에서 지원되지 않음

상태: 해결 방법

이 문제에 대해 다음 해결 방법 중 하나를 선택합니다.

해결 방법 1

Windows용 Excel을 실행하는 컴퓨터에서 비슷한 단계를 수행한 다음 코드를 Mac용 Excel로 복사할 수 있습니다.

해결 방법 2

MSDN으로 이동하여 사용하려는 이벤트의 구문을 찾고 VBA 프로젝트에 코드를 수동으로 입력할 수 있습니다. 이 문서에서는 Microsoft Excel "workbook" 이벤트에 대해 설명합니다(Workbook Events(Workbook 이벤트)).

해결 방법 3

아래 샘플에서 적절한 이벤트 코드를 복사하여 VBA 프로젝트에 붙여넣습니다. 각 이벤트에 대해 "Private Sub..."에서 "End Sub"(이 두 항목 포함)까지 복사해야 합니다.

ThisWorkbook Worksheet

Private Sub Workbook_Activate() End Sub

Private Sub Workbook_AddinInstall() End Sub

Private Sub Workbook_AddinUninstall() End Sub

Private Sub Workbook_AfterRemoteChange() End Sub

Private Sub Workbook_AfterSave(ByVal Success As Boolean) End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean) End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean) End Sub

Private Sub Workbook_BeforeRemoteChange() End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) End Sub

Private Sub Workbook_Deactivate() End Sub

Private Sub Workbook_NewChart(ByVal Ch As Chart) End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object) End Sub

Private Sub Workbook_Open() End Sub

Private Sub Workbook_PivotTableCloseConnection(ByVal Target As PivotTable) End Sub

Private Sub Workbook_PivotTableOpenConnection(ByVal Target As PivotTable) End Sub

Private Sub Workbook_RowsetComplete(ByVal Description As String, ByVal Sheet As String, ByVal Success As Boolean) End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object) End Sub

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object) End Sub

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object) End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) End Sub

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) End Sub

Private Sub Workbook_SheetPivotTableAfterValueChange(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal TargetRange As Range) End Sub

Private Sub Workbook_SheetPivotTableBeforeAllocateChanges(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean) End Sub

Private Sub Workbook_SheetPivotTableBeforeCommitChanges(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean) End Sub

Private Sub Workbook_SheetPivotTableBeforeDiscardChanges(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long) End Sub

Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable) End Sub

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable) End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) End Sub

Private Sub Workbook_SheetTableUpdate(ByVal Sh As Object, ByVal Target As TableObject) End Sub

Private Sub Workbook_Sync(ByVal SyncEventType As Office.MsoSyncEventType) End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window) End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window) End Sub

Private Sub Workbook_WindowResize(ByVal Wn As Window) End Sub

Private Sub Worksheet_Activate()

End Sub

Private Sub Worksheet_BeforeDelete()

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

End Sub

Private Sub Worksheet_Calculate()

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Private Sub Worksheet_Deactivate()

End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

End Sub

Private Sub Worksheet_LensGalleryRenderComplete()

End Sub

Private Sub Worksheet_PivotTableAfterValueChange(ByVal TargetPivotTable As PivotTable, ByVal TargetRange As Range)

End Sub

Private Sub Worksheet_PivotTableBeforeAllocateChanges(ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean)

End Sub

Private Sub Worksheet_PivotTableBeforeCommitChanges(ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean)

End Sub

Private Sub Worksheet_PivotTableBeforeDiscardChanges(ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long)

End Sub

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)

End Sub

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Private Sub Worksheet_TableUpdate(ByVal Target As TableObject)

End Sub