Excel VBA

I am a Leader at vbCity where I answer questions mostly in the VBA forum. I created this page so I can keep track of my own solutions that I come up with. That way I won't have to ask myself, "Where is that piece of code??" If you have a question about any code on this page, please go to vbCity, become a member and post your question.

---

Exporting & Importing Code Modules

This procedure will export all of the worksheet and stand alone VBA modules from an open project called Currmonth_gw.xls, and import the exported modules into the Active Project. Note that for this to work, you must set (in the Excel window, not the VBA IDE), Tools, Macro, Security, Trusted Publishers tab, check Trust access to Visual Basic Project.

vbCity thread: Making two Excel workbooks into one in VBA

 
'Exports certain modules from an Excel VBA Project and imports them into the Active Project.
Sub ExportImportModules()

Dim intCounter As Integer
Dim intModCount As Integer
Dim lngType As Long

intModCount = Application.VBE.VBProjects("Currmonth_gw.xls").VBComponents.Count

'This will export all of the worksheet and stand alone VBA modules from an open project called Currmonth_gw.xls,
'and import the exported modules into the Active Project.
For intCounter = 1 To intModCount
    lngType = Application.VBE.VBProjects("Currmonth_gw.xls").VBComponents(intCounter).Type
    '1 or 2 or 100.
    If lngType = vbext_ct_StdModule Or lngType = vbext_ct_ClassModule Or lngType = vbext_ct_Document Then
        Application.VBE.ActiveVBProject.VBComponents(intCounter).Export _
            ("C:\" & Application.VBE.VBProjects("Currmonth_gw.xls").VBComponents(intCounter).Name & ".bas")
        'Now import the module into this project.
        Application.VBE.ActiveVBProject.VBComponents.Import _
            "C:\" & Application.VBE.VBProjects("Currmonth_gw.xls").VBComponents(intCounter).Name & ".bas"
        'You may have to do some module name maintenance, especially if both projects have the same module names.
    End If
Next intCounter

End Sub
				

---

Reading in a text file

This solution is in here because I can never remember the exact syntax for the "Input" statement. I can remember the "Open" syntax but if I press F1 (Help) over "Open", the Example does not refer to the "Input" statement. Furthermore, the "See Also" link in Help does not refer to "Input" either. vbCity thread: import txt file in LISTBOX. The example below reads in a text file a loads the contents into a UserForm listbox.

Private Sub UserForm_Initialize()

    Dim intCounter As Integer
    Dim strLine As String
    Dim strList() As String
    
    ListBox1.ColumnCount = 2
    intCounter = 0
    
    Open "C:\Documents and Settings\My Documents\testinput.txt" For Input As #1
    
    Do While Not EOF(1)
    Input #1, strLine
    strList = Split(strLine, " ", -1)
    ListBox1.AddItem strList(0)
    ListBox1.List(intCounter, 1) = strList(1)
    intCounter = intCounter + 1
    Loop
    
    Close #1
End Sub
			

---

Setting the Rowsource/ListFillRange of a Listbox or Combobox on a Worksheet

Setting the Rowsource of a Listbox or Combobox in a UserForm is straightforward. If the list is static:

Private Sub UserForm_Initialize()
    Me.ListBox1.RowSource = "Sheet1!A1:A4"
End Sub
			

If the list is dynamic:

Private Sub UserForm_Initialize()
    Me.ListBox1.RowSource = "Sheet1!A1:A" & Sheet1.Range("A1").End(xlDown).Row
End Sub
			

However if the listbox (from the Controls Toolbox) is in a worksheet, setting the Rowsource is trickier. Actually a Listbox on a worksheet doesn't have a Rowsource property - it has a ListFillRange property. Because the Shape object can contain multiple types of objects, it uses a special object to hold the specific object instance. This is the OleFormat() property of the Shape object. To get a reference to the underlying object use the following code. Thanks to vbCity member Lankymart for this solution vbCity thread: Combobox listfillrange on a worksheet

Dim cmbList1 As Object 
Set cmbList1 = ThisWorkbook.Worksheets("Sheet1").Shapes("Combobox1").OleFormat.Object
'Now manipulate various properties of the control using the cmbList1 reference.
cmbList1.ListFillRange = "Lists!H2:H4082"
			 

---