Excel 2018 Mac版手动编写VBA UserForm遇‘未定义类型’错误求助

Hey there! Let's break down why you're hitting that "type used not defined" error and fix it, especially since you're working with Excel 2018 for macOS—this version has some key quirks compared to Windows Excel when it comes to VBA UserForms.

Why the Error Happens #

That error almost always pops up because:

1. Your code references a specific object type (like UserForm, CommandButton, or TextBox) that your Mac VBA environment doesn’t recognize yet.
2. Unlike Windows Excel, Mac Excel 2018 doesn’t let you visually insert UserForms by default, so you have to manually set up the necessary object library references or use alternative binding methods.

Step-by-Step Fixes #

1. Load the Required Object Library

First, let’s make sure your VBA editor can recognize UserForm and control types:

• Open the VBA editor (press Option + F11).
• Go to Tools > References in the top menu.
• In the references window, find and check Microsoft Forms 2.0 Object Library (it might just be labeled "Microsoft Forms" on some Mac builds).
• Click OK to save the reference.

This tells VBA what types like CommandButton or UserForm are, which should resolve the "undefined type" error for most cases.

2. If the Library Isn’t Available: Use Late Binding

If you can’t find the Microsoft Forms library (sometimes Mac hides it), switch to late binding—this lets you create objects dynamically at runtime without pre-referencing libraries.

Replace code like this:

Dim myForm As UserForm
Dim myButton As CommandButton

With this:

Dim myForm As Object
Dim myButton As Object

Set myForm = CreateObject("UserForm")
Set myButton = CreateObject("Forms.CommandButton.1")

Late binding skips the need for pre-loaded libraries, so VBA won’t throw a "type undefined" error.

3. Full Working Example for Mac Excel 2018

Here’s a complete code snippet to create a functional UserForm manually, including a button that responds to clicks:

First, Set Up an Event Handler Class

• Go to Insert > Class Module in the VBA editor.
• Rename the class module UserFormEventHandler (use the Properties window on the right).
• Paste this code into the class:

Public WithEvents submitBtn As MSForms.CommandButton
Public parentForm As Object

Private Sub submitBtn_Click()
    MsgBox "You entered: " & parentForm.Controls("inputBox").Value
    parentForm.Hide
End Sub

Then, Create the UserForm

Paste this into a regular module:

Sub BuildCustomUserForm()
    Dim myForm As Object
    Dim inputBox As Object
    Dim submitBtn As MSForms.CommandButton
    Dim eventHandler As New UserFormEventHandler
    
    ' Create the main form
    Set myForm = CreateObject("UserForm")
    myForm.Caption = "Mac UserForm Test"
    myForm.Width = 320
    myForm.Height = 180
    
    ' Add text input box
    Set inputBox = CreateObject("Forms.TextBox.1")
    inputBox.Name = "inputBox"
    inputBox.Top = 30
    inputBox.Left = 30
    inputBox.Width = 250
    inputBox.Value = "Type something here..."
    myForm.Controls.Add(inputBox)
    
    ' Add submit button
    Set submitBtn = CreateObject("Forms.CommandButton.1")
    submitBtn.Name = "submitBtn"
    submitBtn.Caption = "Submit"
    submitBtn.Top = 80
    submitBtn.Left = 110
    submitBtn.Width = 100
    myForm.Controls.Add(submitBtn)
    
    ' Link the button to its event handler
    Set eventHandler.submitBtn = submitBtn
    Set eventHandler.parentForm = myForm
    
    ' Show the form (modal)
    myForm.Show
    
    ' Clean up objects
    Set eventHandler = Nothing
    Set submitBtn = Nothing
    Set inputBox = Nothing
    Set myForm = Nothing
End Sub

Run this macro, and you’ll get a working UserForm with a functional submit button—no "undefined type" errors (just make sure you loaded the Microsoft Forms library first!).

Quick Notes #

• If the Microsoft Forms library still doesn’t show up, try restarting Excel or the VBA editor—sometimes Mac’s reference list loads incompletely.
• Excel 2018 for Mac is a bit outdated; if you can upgrade to a newer version, you’ll get better VBA UserForm support (including visual design tools).

内容的提问来源于stack exchange，提问作者user1773603