How to create function to retrieve multiple values in VBA excel

Hi,
I use code below to populate my combobox on my excel worksheet...
Now I want to put the same into the separate class as function and call the same every time I want to load my combobox. Furthermore, reason for doing this is to also be able to use same function to load other comboboxes with same items embeded on different worksheets or userforms as well in workbook.
My issue here is how to return multiple values form the function and I would appreciate your help in order to guide me in order to mofify my VBA code in order to make this working....

Thanks


Me.ComboBox1.Clear
Me.ComboBox1.Text = "Select date..."
rst.Open ("SELECT DISTINCT Date as ph_3 FROM BI_A_CMIV_SEG where date>= '2011-01-01' order by date desc"), cnn
x = rst.RecordCount
For i = -1 To x
While rst.EOF = False
user = rst!ph_3
Me.ComboBox1.AddItem user, 0
rst.MoveNext
Wend
Next i
rst.Close

Open in new window

LVL 1
alsamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SiddharthRoutCommented:
Place this in a module. Not that I am not closing the recordset in the SUB.

Public Sub PopulateCombo(rs As Recordset, cmb As ComboBox)
    cmb.Clear
    cmb.Text = "Select date..."
    rs.Open ("SELECT DISTINCT Date as ph_3 FROM BI_A_CMIV_SEG where date>= '2011-01-01' order by date desc"), cnn
    x = rs.RecordCount
    For i = -1 To x
        While rs.EOF = False
            user = rs!ph_3
            cmb.AddItem user, 0
            rs.MoveNext
        Wend
    Next i
End Sub

Open in new window


Sid
0
alsamAuthor Commented:
Hi Sid,
then what would you pass as arguments for rs and cbo when I call this routine in module from worksheet event...
Plase help
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

SiddharthRoutCommented:
alsam:

See this example

Sub Sample
dim rs as adodb.recordset
rest of code
.
.
.
PopulateCombo rs,combo1
rs.close
end sub

Open in new window


Sid
0
alsamAuthor Commented:
Still can not make working...
Please tell me what did i wrong in my routine...

For Combobox1 it pass a combo value instead of object name?

thanks
Private Sub Worksheet_Activate()
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

cnn.Open "Provider=SQLOLEDB;Data Source=SUVADA-LAPTOP\MYSQLEXPRESS;Initial Catalog=Controlling;Integrated Security=SSPI"


Call Module1.PopulateCombo(rs, ComboBox1)


rs.Close


End Sub

Open in new window

0
SiddharthRoutCommented:
May I see your file?

Sid
0
alsamAuthor Commented:
Ok
Attached please  find the file...
You will get an error on workbook open but at this time just ignore since you dont have Access database im reffering to in order to populate treeview...
You should focus on sheet25
 Reports.xls
0
SiddharthRoutCommented:
Ah I just realized that 'Cnn' is undefined in the module. Please try this

In the module

Public Sub PopulateCombo(cn As Connection, rs As Recordset, cmb As ComboBox)
    cmb.Clear
    cmb.Text = "Select date..."
    rs.Open ("SELECT DISTINCT Date as ph_3 FROM BI_A_CMIV_SEG where date>= " & _
    "'2011-01-01' order by date desc"), cn
    x = rs.RecordCount
    For i = -1 To x
        While rs.EOF = False
            user = rs!ph_3
            cmb.AddItem user, 0
            rs.MoveNext
        Wend
    Next i
End Sub

Open in new window


and Call it like

Call Module1.PopulateCombo(cnn, rs, ComboBox1)

From the Worksheet_Activate()

Sid
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.