StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I am going to show you <strong>how to run a macro in another instance of excel</strong> <em>,which in your case will display a</em> <strong><code>UserForm1</code></strong><br> <br> <br> 1) Create <strong>a new workbook</strong><br> 2) Open the <strong>VBE</strong> (<em>Visual Basic Editor</em>) - <code>ALT + F11</code><br> 3) Insert new <code>UserForm</code> and <code>Module</code> (<em>right click in the</em> <strong><em>project explorer</em></strong> <em>then <code>Insert</code></em>). Your screen should look similar to the below picture:<br><br> <img src="https://i.stack.imgur.com/ArwXa.png" alt="step 3 overview"> <br><br> 4) <a href="https://stackoverflow.com/questions/9879825/how-to-add-a-reference-programmatically-vba-excel">Add References for the <code>Microsoft Visual Basic for Applications Extensibility 5.3</code></a> <br> note: I have this already in my code, but you have to make sure you have properly attached it<br></p> <p>5) In the newly created <strong><code>Module1</code></strong> insert the code<br></p> <pre><code>Sub Main() AddReferences AddComponent "UserForm1", "UserForm1.frm" End Sub Private Sub AddReferences() ' Name: VBIDE ' Description: Microsoft Visual Basic for Applications Extensibility 5.3 ' GUID: {0002E157-0000-0000-C000-000000000046} ' Major: 5 ' Minor: 3 ' FullPath: C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB On Error Resume Next ThisWorkbook.VBProject.References.AddFromGuid GUID:="{0002E157-0000-0000-C000-000000000046}", _ Major:=5, Minor:=3 End Sub Sub AddComponent(theComponent$, fileName$) ' export Application.VBE.ActiveVBProject.VBComponents(theComponent).Export ThisWorkbook.Path & "\" & fileName Dim xApp As Excel.Application Set xApp = New Excel.Application xApp.Visible = True Dim wb As Excel.Workbook Set wb = xApp.Workbooks.Add wb.VBProject.VBComponents.Import ThisWorkbook.Path & "\" & fileName CreateAModule wb xApp.Run "MacroToExecute" xApp.DisplayAlerts = False wb.Save wb.Close Set wb = Nothing xApp.Quit Set xApp = Nothing Application.DisplayAlerts = True End Sub Sub CreateAModule(ByRef wb As Workbook) Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.vbComponent Dim CodeMod As VBIDE.CodeModule Set VBProj = wb.VBProject Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule) Set CodeMod = VBComp.CodeModule With CodeMod .DeleteLines 1, .CountOfLines .InsertLines 1, "Public Sub MacroToExecute()" .InsertLines 2, " UserForm1.Show" .InsertLines 3, "End Sub" End With End Sub </code></pre> <p><br> 6) Now, run the <strong><code>Main</code></strong> Macro and you will be shown the <strong><code>Userform1</code></strong> <br> <br></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload