View Full Version : Solved: array function help
benong
09-26-2010, 06:20 PM
hi,
I have a user form with many combo boxes.
All my combo box list items are extracted from another workbooks.
Eg. for combo box 1:
Workbooks.Open (.....)
Set DB = ActiveWorkbook
RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
DB_RANGE = "A2:A" & RowCount
With Me.ComboBox1
For Each listMaterialType In Worksheets("Sheet1").Range(DB_RANGE)
.AddItem listMaterialType.Value
.List(.ListCount - 1, 1) = listMaterialType.Offset(0, 1).Value
Next listMaterialType
End With
DB.Close False
This work fine. But when i want to do it for many combo boxes, I find that there are many repeat codes. Can someone show me how to make this code into a function to return array and use it in combo box 1, combo box2 ...etc? Many thanks.
Hi there,
A bit of a stab, as I'm not sure I understand. If I do, and you have the combo boxes named as shown, maybe:
Dim i As Long
For i = 1 To 20 '<---however many comboboxes
'Workbooks.Open (.....)
Set Db = ActiveWorkbook
RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
DB_RANGE = "A2:A" & RowCount
With Me.Controls("ComboBox" & i)
For Each listMaterialType In Worksheets("Sheet1").Range(DB_RANGE)
.AddItem listMaterialType.Value
.List(.ListCount - 1, 1) = listMaterialType.Offset(0, 1).Value
Next listMaterialType
End With
Db.Close False
Next
Hope that helps,
Mark
benong
09-26-2010, 09:59 PM
Thanks GTO,
but this is what i have in mind:
sub a()
wbk=1.xls
arrayA = call function(wbk)
using loop to assign arrayA to combo box 1 items
wbk=2.xls
arrayB = call function(wbk)
using loop to assign arrayB to combo box 2 items
wbk=3.xls
arrayC = call function(wbk)
using loop to assign arrayC to combo box 3 items
...
....
end sub
function(wbk)
Workbooks.Open (wbk)
Set DB = ActiveWorkbook
RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
DB_RANGE = "A2:A" & RowCount
For Each listMaterialType In Worksheets("Sheet1").Range(DB_RANGE)
assign values to array
End With
DB.Close False
end function
Do you know how to do it this way? I tried but failed.
It appears to me that you have a two-column combo. You should not need to loop in the values, especially given that they are coming from an array.
Not sure if you are hard-coding in the wb names/paths, but see if this example helps.
In the Userform's Module:
Option Explicit
Private Sub UserForm_Initialize()
Const PATH As String = "D:\2010\_Tmp\2010-09-17\tmp\"
With Me.ComboBox1
.List = RetArray(PATH & "New Microsoft Excel Worksheet.xls")
End With
With Me.ComboBox2
.List = RetArray(PATH & "New Microsoft Excel Worksheet (2).xls")
End With
End Sub
Private Function RetArray(strFullPath As String) As Variant()
Dim wks As Worksheet
Set wks = Workbooks.Open(strFullPath, , True).Worksheets("Sheet1")
RetArray = wks.Range(wks.Range("A2"), _
wks.Cells(wks.Rows.Count, "A").End(xlUp) _
).Resize(, 2).Value
wks.Parent.Close False
End Function
benong
09-27-2010, 05:18 PM
Dear GTO,
Do you know how to use 2-dimension array to solve this?
benong
09-27-2010, 06:12 PM
I tried to use pre-defined array in my code for testing:
comboBoxList = Array("James", "Mary", "Tom", "Beth", "Bob", "Chris", "Daniel", "Lari", "Al", "Teresa")
On the "VBA editor - watches", it display
MyArray(0) : "James"
MyArray(1) : "Mary"
...
...
Then i tried to extract data from worksheet to form an array:
comboBoxList = Worksheets(1).Range("A:A").Value
On the "VBA editor - watches", it display
MyArray(1)
MyArray(1,1) : "James"
MyArray(2)
MyArray(2,1) : "Mary"
...
...
Is there a difference between the 2 arrays as i can't perform array sorting.
Dear GTO,
Do you know how to use 2-dimension array to solve this?
Private Function RetArray(strFullPath As String) As Variant()
Dim wks As Worksheet
Set wks = Workbooks.Open(strFullPath, , True).Worksheets("Sheet1")
RetArray = wks.Range(wks.Range("A2"), _
wks.Cells(wks.Rows.Count, "A").End(xlUp) _
).Resize(, 2).Value
wks.Parent.Close False
End Function
RetArray returns a two-dimiensional array. Did you try the code?
In the case of RetArray, as we resized from column A and grabbed Column B, the array contains however many rows of data in Col A, by two columns.
Your second array is also a two-dimensional array, in this case, only one column. So that is, it is a however many rows, by 1 to 1 columns. This is why under watches or the locals window, it shows MyArray(1,1) James. The first one is the row, the second is the column.
benong
09-28-2010, 01:36 AM
Dear GTO,
Sorry, I'm not comfortable with array, but finally, i get this to work.
Private Sub UserForm_Initialize()
With Me.ComboBox1
.List = remoteComboBoxList("1.xlsx", "B")
End With
With Me.ComboBox2
.List = remoteComboBoxList("2.xlsx", "A")
End With
With Me.ComboBox3
.List = remoteComboBoxList("3.xlsx", "G")
End With
End Sub
Function remoteComboBoxList(DB_Workbook As String, columnField As String) As Variant()
Dim wbk As Workbook
Dim RowCount As Integer
Dim DB_RANGE As String
Application.ScreenUpdating = False 'hide source book from user
Workbooks.Open DB_Path & DB_Workbook
Set wbk = ActiveWorkbook
RowCount = Worksheets("Sheet1").Range("A1").CurrentRegion.Rows.Count
DB_RANGE = columnField & "2:" & columnField & RowCount
wbk.Worksheets("Sheet1").Range("A1").Sort _
Key1:=Worksheets("Sheet1").Columns(columnField), _
Header:=xlGuess
remoteComboBoxList = wbk.Worksheets("Sheet1").Range(DB_RANGE).Value
wbk.Close False
Application.ScreenUpdating = True
End Function
It does fullfil some of my objective, but i need it to further enhance it to allow 2 column values. So far i've not succeed.
benong
09-28-2010, 05:44 PM
Dear GTO,
many thanks for your guidance and patience.
You code is working great :)
Dear GTO,
many thanks for your guidance and patience.
You code is working great :)
Happy to help and glad its working:friends:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.