方法有二種參考(在XP+OFFICE2000下調試成功)第一 把當前文件的程式復制到新文件'請設定引用專案Microsoft Visual Basic for Application Extensibility x.x Sub CopyAndShowUserForm() Dim oNewBk As Workbook Dim oVBC As VBIDE.VBComponent
'新增一個新的工作簿 Set oNewBk = Workbooks.Add '匯出Userform到硬碟 ThisWorkbook.VBProject.VBComponents("UserForm1").Export "c:/temp.frm"
'從硬碟將Userform模組匯入到新的工作簿 Set oVBC = oNewBk.VBProject.VBComponents.Import("c:/temp.frm")
'重新命名Userform oVBC.Name = "MyForm"
'在新的工作簿專案中新增一個一般模組 Set oVBC = oNewBk.VBProject.VBComponents.Add(vbext_ct_StdModule) '在該一般模組中寫入顯示Userform的程式碼 oVBC.CodeModule.AddFromString "Sub ShowMyForm()" & vbCrLf & _ " MyForm.Show" & vbCrLf & "End Sub" & vbCrLf '刪除匯出Userform的檔案 Kill "c:/temp.frm" '執行新工作簿中顯示Userform的程序 Application.OnTime Now, oNewBk.Name & "!ShowMyForm" '關閉本工作簿 ThisWorkbook.Close False End Sub'UserForm模組 Private Sub UserForm_Click() MsgBox "This is a Test!" End Sub方法二 向工作表復制程序碼Sub CopySub() Dim VBComp As VBComponent Dim wbk As Workbook
'寫入程式碼 With ActiveWorkbook.VBProject. _ VBComponents(VBComp.Name).CodeModule NextLine = .CountOfLines + 1 .InsertLines NextLine, Code End With
'立刻執行 Msgbox Application.Run wbk.FullName & "!" & "testb" End Sub Sub CopyLineInSubs() Dim myBook As Workbook Dim wnk As Workbook Dim VBCode As String Set myBook = ThisWorkbook '找出Msgbox在第幾行 K = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule.CountOfLines For i = 1 To K VBCode = myBook.VBProject.VBComponents("Module1").CodeModule.Lines(i, 1) If Left(Trim(VBCode), 6) = "MsgBox" Then L = i: GoTo 1 End If Next i '新增Book(也可以用開啟舊檔) 1 Set wbk = Workbooks.Add
'寫入程式碼 K = 1 wbk.VBProject.VBComponents(VBComp.Name).CodeModule.InsertLines K, "Sub testB()" For i = L To L + 3 K = K + 1 VBCode = myBook.VBProject.VBComponents("Module1").CodeModule.Lines(i, 1) wbk.VBProject.VBComponents(VBComp.Name).CodeModule.InsertLines K, VBCode Next i wbk.VBProject.VBComponents(VBComp.Name).CodeModule.InsertLines K + 1, "End Sub"
'立刻執行 Msgbox Application.Run wbk.FullName & "!" & "testB" End Sub
把當前文件的程式復制到新文件'請設定引用專案Microsoft Visual Basic for Application Extensibility x.x
Sub CopyAndShowUserForm()
Dim oNewBk As Workbook
Dim oVBC As VBIDE.VBComponent
'新增一個新的工作簿
Set oNewBk = Workbooks.Add
'匯出Userform到硬碟
ThisWorkbook.VBProject.VBComponents("UserForm1").Export "c:/temp.frm"
'從硬碟將Userform模組匯入到新的工作簿
Set oVBC = oNewBk.VBProject.VBComponents.Import("c:/temp.frm")
'重新命名Userform
oVBC.Name = "MyForm"
'在新的工作簿專案中新增一個一般模組
Set oVBC = oNewBk.VBProject.VBComponents.Add(vbext_ct_StdModule) '在該一般模組中寫入顯示Userform的程式碼
oVBC.CodeModule.AddFromString "Sub ShowMyForm()" & vbCrLf & _
" MyForm.Show" & vbCrLf & "End Sub" & vbCrLf '刪除匯出Userform的檔案
Kill "c:/temp.frm" '執行新工作簿中顯示Userform的程序
Application.OnTime Now, oNewBk.Name & "!ShowMyForm"
'關閉本工作簿
ThisWorkbook.Close False
End Sub'UserForm模組
Private Sub UserForm_Click()
MsgBox "This is a Test!"
End Sub方法二
向工作表復制程序碼Sub CopySub()
Dim VBComp As VBComponent
Dim wbk As Workbook
'編寫程式碼
Code = ""
Code = Code & "Sub testb()" & vbCrLf
Code = Code & "Msgbox(""hi""), , ActiveWorkbook.Name" & vbCrLf
Code = Code & "End Sub" & vbCrLf
'新增Book(也可以用開啟舊檔)
Set wbk = Workbooks.Add
'新增Modlule,並命名
Set VBComp = wbk.VBProject.VBComponents.Add(vbext_ct_StdModule) '新增Module1
VBComp.Name = "NewModule"
Application.Visible = True
'寫入程式碼
With ActiveWorkbook.VBProject. _
VBComponents(VBComp.Name).CodeModule
NextLine = .CountOfLines + 1
.InsertLines NextLine, Code
End With
'立刻執行 Msgbox
Application.Run wbk.FullName & "!" & "testb"
End Sub
Sub CopyLineInSubs()
Dim myBook As Workbook
Dim wnk As Workbook
Dim VBCode As String
Set myBook = ThisWorkbook '找出Msgbox在第幾行
K = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule.CountOfLines
For i = 1 To K
VBCode = myBook.VBProject.VBComponents("Module1").CodeModule.Lines(i, 1)
If Left(Trim(VBCode), 6) = "MsgBox" Then
L = i: GoTo 1
End If
Next i '新增Book(也可以用開啟舊檔)
1 Set wbk = Workbooks.Add
'新增Modlule,並命名
Set VBComp = wbk.VBProject.VBComponents.Add(vbext_ct_StdModule) '新增Module1
VBComp.Name = "NewModule"
Application.Visible = True
'寫入程式碼
K = 1
wbk.VBProject.VBComponents(VBComp.Name).CodeModule.InsertLines K, "Sub testB()"
For i = L To L + 3
K = K + 1
VBCode = myBook.VBProject.VBComponents("Module1").CodeModule.Lines(i, 1)
wbk.VBProject.VBComponents(VBComp.Name).CodeModule.InsertLines K, VBCode
Next i
wbk.VBProject.VBComponents(VBComp.Name).CodeModule.InsertLines K + 1, "End Sub"
'立刻執行 Msgbox
Application.Run wbk.FullName & "!" & "testB"
End Sub