Excel

Mac: Import / Export VBE Components

Ease of Use

Easy

Version tested with

Submitted by:

BlueCactus

Description:

Enables Mac users to import / export VBE components (class modules, userforms, standard modules) to / from open WorkBooks. Not designed for Windows (although it might work). 

Discussion:

For some unknown reason Microsoft failed to include Import / Export commands in the VBE on Mac Office. Microsoft suggests that you use Excel 2003 (Windows) to export components if you need to! The good news is that the Import / Export commands can still be accessed through VBA. This KB provides a front end to those commands. ACHTUNG! It would be a good idea to use COPIES of your WorkBooks with this code, as mistakes may or may not be reversed easily. 

Code:

instructions for use

			

' /////////////////////////////////////// ' In a standard module: ' /////////////////////////////////////// Option Explicit Sub moveCode() ' Everything is in the form! ImpExpForm.Show End Sub ' /////////////////////////////////////// ' In a UserForm named ImpExpForm ' /////////////////////////////////////// Option Explicit Private Sub ComboWB_Change() Dim i As Integer ' Find all components for selected WorkBook, and identify their Types ListVBE.Clear With Workbooks(ComboWB.ListIndex + 1).VBProject.VBComponents For i = 1 To Workbooks(ComboWB.ListIndex + 1).VBProject.VBComponents.Count ListVBE.AddItem .Item(i).Name If .Item(i).Type = 2 Then ListVBE.List(ListVBE.ListCount - 1, 1) = "class module" ElseIf .Item(i).Type = 100 Then ListVBE.List(ListVBE.ListCount - 1, 1) = "sheet / document / file" ElseIf .Item(i).Type = 3 Then ListVBE.List(ListVBE.ListCount - 1, 1) = "userform" ElseIf .Item(i).Type = 1 Then ListVBE.List(ListVBE.ListCount - 1, 1) = "standard module" Else ListVBE.List(ListVBE.ListCount - 1, 1) = "unknown" End If Next i End With End Sub Private Sub CommandExport_Click() Dim fileName As Variant fileName = Application.GetSaveAsFilename("vbcomponent") If fileName <> False Then On Error GoTo ExpError Workbooks(ComboWB.ListIndex + 1).VBProject.VBComponents(ListVBE.ListIndex + 1).Export (fileName) On Error GoTo 0 MsgBox "VBE Component '" & ComboWB.List(ComboWB.ListIndex) & "' exported to:" & Chr(13) & fileName End If Exit Sub ExpError: MsgBox "Could not export '" & ComboWB.List(ComboWB.ListIndex) & "' to:" & Chr(13) & fileName On Error GoTo 0 Exit Sub End Sub Private Sub CommandImport_Click() Dim fileName As Variant fileName = Application.GetOpenFilename() If fileName <> False Then On Error GoTo ImpError Workbooks(ComboWB.ListIndex + 1).VBProject.VBComponents.Import (fileName) On Error GoTo 0 MsgBox "VBE Component:" & Chr(13) & "'" & fileName & "' imported to:" & Chr(13) & ComboWB.List(ComboWB.ListIndex) End If Exit Sub ImpError: MsgBox "Could not import " & Chr(13) & "'" & fileName & "' to:" & Chr(13) & ComboWB.List(ComboWB.ListIndex) On Error GoTo 0 Exit Sub End Sub Private Sub CommandOK_Click() Unload Me End Sub Private Sub ListVBE_Click() With ListVBE If .List(.ListIndex, 1) = "standard module" Or .List(.ListIndex, 1) = "userform" Or .List(.ListIndex, 1) = "class module" Then CommandExport.Enabled = True Else CommandExport.Enabled = False End If End With End Sub Private Sub UserForm_Initialize() Dim i As Integer ' Get all WorkBook names For i = 1 To Workbooks.Count ComboWB.AddItem Workbooks(i).Name Next i ComboWB.ListIndex = 0 ' Form size adjustments Me.Zoom = 120 Me.Width = 1.2 * Me.Width Me.Height = 1.2 * Me.Height End Sub

How to use:

  1. Download the sample file and open it in Excel.
  2. The code is designed to remain in, and be used from this stand-alone sample file.
  3. Note: Due to a lack of Mac users, this code is being published without the usual rigorous testing. If you should encounter any bugs, please contact the author by PM or post a question in the Mac forum
  4. mdmackillop VP-Knowledge Base
 

Test the code:

  1. Open the sample file.
  2. Open all WorkBooks that you wish to export from / import to.
  3. Hit the button in the sample file.
  4. Select a WorkBook from the ComboBox. If you are exporting, select a VBE component from the ListBox.
  5. Hit the Export or the Import button. Do not worry about file extensions when exporting.
 

Sample File:

MacVBEImpExp.xls.zip 12.32KB 

Approved by mdmackillop


This entry has been viewed 129 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express