Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

populate data in listbox across sheets

0

Hello,

I'm not sure if it's possible to modifying code to make better when run the form .

the code will populate data  in listbox across sheets  on form excluding row contains OPENING word in column C and row contains TOTAL word in column A .

seem slight slow with simple data and with big data will be too slow .

so if there is alternitave code or adjusting original code to make better I truly appreciate for any help guys.

Private Sub UserForm_Initialize()

Call IMPORTDATA


End Sub

Sub IMPORTDATA()

    Dim s$(1), x, e
    For Each e In Array("Mussala", "mssau", "mjhgsg")
        s(0) = s(0) & IIf(s(0) = "", "", "Union All (") & "Select " & _
        "Format(`DATE`,'dd/mm/yyyy'), `INVOICE NO`, `TYPE`, `DEBIT`, `CREDIT`, " & _
        "`BALANCE` From `" & e & "$` Where `TYPE` <> 'OPENING' And `TYPE` Is Not Null " & IIf(s(0) = "", "", ") ")
    Next
    s(1) = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
         ";Extended Properties='Excel 12.0;HDR=Yes';"
    With CreateObject("ADODB.Recordset")
        .Open s(0), s(1), 3, 3, 1
        x = .GetRows
    End With
    With Me.ListBox1
        .ColumnCount = UBound(x, 1) + 1
        .Column = x
        For i = 0 To .ListCount - 1
     .List(i, 3) = Format(.List(i, 3), "#,##0.00")
     .List(i, 4) = Format(.List(i, 4), "#,##0.00")
     Next i
    End With

End Sub
excel
MS Office Version: 0ffice 2019
posted by: Mussala (rep: 14) on Wed Jan 8, 2025 at 4:23 am
Answer
Discuss

Discussion

Hi Mussala,

Sorry, but I don't have much experience with listboxes, so I can't help with your code.
There is one thing that caught my eye and that is that you haven't defined your variables. You have declared them ( Dim s$(1), x , e ) but you haven't defined them specifically (ie: worksheet, range, long, string, etc) so VBA treats all of them as type variant. This can cause code to run slower.
WillieD24 (rep: 657) Jan 8, '25 at 11:04 pm
@Willie - actually Mussala (or whoever wrote the code) has used a VBA Identifier Type when declaring "s", the trailing $ means it is a String (array) but the others will be Variant, as you say (and potentailly waste memory). I'll take a look at this when I get chance, today hopefully.
John_Ru (rep: 6607) Jan 9, '25 at 8:51 am
Add to Discussion

Answers

0
Selected Answer

Hi again Mussala

In the attached revised file, I made the changes in bold below(with comments) to:

  • format the number columns in the array, rather than in the ListBox List- this should be much quicker
  • declare the counter variable i as Long (using an Identifier Type character rather than "... as Long", following the comments by WillieD24 in the Discussion under your question):
Sub IMPORTDATA()

    ' declare i as Long (using the VBA Identifier Type character "&")
    Dim s$(1), x, e, i&

    For Each e In Array("Mussala", "mssau", "mjhgsg")
        s(0) = s(0) & IIf(s(0) = "", "", "Union All (") & "Select " & _
        "Format(`DATE`,'dd/mm/yyyy'), `INVOICE NO`, `TYPE`, `DEBIT`, `CREDIT`, " & _
        "`BALANCE` From `" & e & "$` Where `TYPE` <> 'OPENING' And `TYPE` Is Not Null " & IIf(s(0) = "", "", ") ")
    Next e

    s(1) = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
         ";Extended Properties='Excel 12.0;HDR=Yes';"
    With CreateObject("ADODB.Recordset")
        .Open s(0), s(1), 3, 3, 1
        x = .GetRows
        ' format the records in the 2-D array, not the Listbox List
        For i = 0 To UBound(x, 2)
            x(3, i) = Format(x(3, i), "#,##0.00")
            x(4, i) = Format(x(4, i), "#,##0.00")
            x(5, i) = Format(x(5, i), "#,##0.00")
        Next i
    End With
    With Me.ListBox1
        .ColumnCount = UBound(x, 1) + 1
        .Column = x
'        For i = 0 To .ListCount - 1
'            .List(i, 3) = Format(.List(i, 3), "#,##0.00")
'            .List(i, 4) = Format(.List(i, 4), "#,##0.00")
'        Next i
    End With

End Sub

I tried this with 8 times more data in one of your sheets and the execution time didn't really change, still about a third faster than your original (on my old, slow PC).

Hope you find this faster- if so, please remember to mark this Answer as Selected.

p.s. I suspect there's a more recent OLE DB driver than 12.0 for (your) Excel 2019 but I leave you to research that.

posted by: John_Ru (rep: 6607) on Thu Jan 9, 2025 at 12:02 pm
Discuss

Discussion

thanks willie for your comment.
Mussala (rep: 14) Jan 9, '25 at 12:26 pm
@John  this is really better .
now I'm at hoem and I have laptop is really slow it's too old and use  2008 ago ,. although  that the original code gives me 26.25 and your improving gives 2.23. this is big deifference buddy. by the way the code desighn by member in another forum and this is really difficult to me to understand it . I wanted known way and simple with my few knowing vba.
thank you so much John.
Mussala (rep: 14) Jan 9, '25 at 12:32 pm
Glad that helped and hope it's even faster on your work computer. Thanks for selecting my Answer, Mussala.

Incidentally Excel 2019 can work with a more recent OLE DB driver (than 12.0 in the author's code) but I doubt that would speed things up (and I don't have time to help on that matter).
John_Ru (rep: 6607) Jan 9, '25 at 1:07 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login