asked on

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

Randy Downs

Try this - http://vbadud.blogspot.com/2008/11/how-to-return-multiple-values-from-vba.html

SiddharthRout

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

alsam

ASKER

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

SiddharthRout

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

alsam

ASKER

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

SiddharthRout

May I see your file?

Sid

alsam

ASKER

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

ASKER CERTIFIED SOLUTION

SiddharthRout

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial