EXCEL VBA - Using functions to create arrays

R

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
Hello all you excel gurus.

So I am trying create a function that will return an array. The reason for the function is that the code has to iterate a number of times and the array will be used in its own loop later in the code process.

I tried the following:


i = the iteration number

the function is called with:

Code: 
arrLINEb() As Variant
arrLINEb = array_arrlineb(i)

the function is:
Code: 
Function array_arrlineb(i As Integer) As Variant
Dim arrLINEb() As Variant

Select Case i
    Case 11
        arrLINEb = Array("310000")
    Case 12
        arrLINEb = Array("331000")
    Case 13
        arrLINEb = Array("412600", "412700", "413600", "413700", _
                        "413900", "414900", "416600", "417100", _
                        "417200", "420100", "422100", "422200", _
                        "422500", "425100", "428300", "428500", _
                        "428700", "438400", "439400", "439700", _
                        "439800", "480100", "480200", " 490100")
    Case 16
        arrLINEb = Array("412600", "412700", "413600", "416600", _
                                    "417100", "417200", "438400", _
                                    "439400", "439700")
    Case 17
         arrLINEb = Array("417100", "422100", "422200", "422500", _
                        "425100", "428300", "428500", "428700", _
                        "438400", "439800")
    Case 18
        arrLINEb = Array("480100", "480200", "490100")
    Case 22
        arrLINEb = Array("480100", "490100")
    Case 23
        arrLINEb = Array("480100", "480200", "490100")
    Case 24
        arrLINEb = Array("480200")
    Case 26
        arrLINEb = Array("422100", "422500", "425100", "428300", _
                        "428500", "428700")
    Case 27
        arrLINEb = Array("422100", "422500", "425100", "428300", _
                        "428500", "428700")
    Case 29
        arrLINEb = Array("422200")
    Case 30
        arrLINEb = Array("422100", "422500", "425100", "428300", _
                        "428500", "428700")
    Case 33
        arrLINEb = Array("480200")
    Case 34
        arrLINEb = Array("422200")
    Case 37
        arrLINEb = Array("310000")
    Case 43
        arrLINEb = Array("331000")
    Case 44
        arrLINEb = Array("310000")
    Case 49
        arrLINEb = Array("420100", "422100", "422200", "425100", _
                        "480100", "480200", "490100")
    Case 50
        arrLINEb = Array("422200", "480200")
    Case 55
        arrLINEb = Array("331000")
    Case Else
        arrLINEb = Array("")
End Select
    array_arrlineb = Array(arrLINEb)
End Function

So I need to understand where I am messing up so that in the future I wont have to bother you nice people.

Any ideas?

Thanks,

Rich
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Rich

You should be using the function name not the variable to return the required array.
Code: 
Function array_array_arrlineb(i As Integer) As Variant


    Select Case i
        Case 11
            array_arrlineb = Array("310000")
        Case 12
            array_arrlineb = Array("331000")
        Case 13
            array_arrlineb = Array("412600", "412700", "413600", "413700", _
                            "413900", "414900", "416600", "417100", _
                            "417200", "420100", "422100", "422200", _
                            "422500", "425100", "428300", "428500", _
                            "428700", "438400", "439400", "439700", _
                            "439800", "480100", "480200", " 490100")
        Case 16
            array_arrlineb = Array("412600", "412700", "413600", "416600", _
                                        "417100", "417200", "438400", _
                                        "439400", "439700")
        Case 17
             array_arrlineb = Array("417100", "422100", "422200", "422500", _
                            "425100", "428300", "428500", "428700", _
                            "438400", "439800")
        Case 18
            array_arrlineb = Array("480100", "480200", "490100")
        Case 22
            array_arrlineb = Array("480100", "490100")
        Case 23
            array_arrlineb = Array("480100", "480200", "490100")
        Case 24
            array_arrlineb = Array("480200")
        Case 26
            array_arrlineb = Array("422100", "422500", "425100", "428300", _
                            "428500", "428700")
        Case 27
            array_arrlineb = Array("422100", "422500", "425100", "428300", _
                            "428500", "428700")
        Case 29
            array_arrlineb = Array("422200")
        Case 30
            array_arrlineb = Array("422100", "422500", "425100", "428300", _
                            "428500", "428700")
        Case 33
            array_arrlineb = Array("480200")
        Case 34
            array_arrlineb = Array("422200")
        Case 37
            array_arrlineb = Array("310000")
        Case 43
            array_arrlineb = Array("331000")
        Case 44
            array_arrlineb = Array("310000")
        Case 49
            array_arrlineb = Array("420100", "422100", "422200", "425100", _
                            "480100", "480200", "490100")
        Case 50
            array_arrlineb = Array("422200", "480200")
        Case 55
            array_arrlineb = Array("331000")
        Case Else
            array_arrlineb = Array("")
    End Select

End Function
 
Upvote 0
just ensure that I understand you mean I should be using

array_arrlineb = Array("480100", "490100")

instead of finishing the select case and then assigning it

array_arrlineb = Array(arrLINEb)


I also found on line that you cant pass an array in variant form? Is that correct or am I understanding this incorrectly.
 
Upvote 0
You are already assigning the required array to the function in the Select Case.

If you use Array again then you'll end up with an array with one item, and that item will be the array you've assigned to the function in the Select Case.

As for not being able to pass an array in variant form, I'm a little confused - all you are passing to the function is an integer.

Note, there was a typo in the code I posted caused by a find/replace.
Code: 
Function array_arrlineb(I As Long) As Variant

    Select Case I
        Case 11
            array_arrlineb = Array("310000")
        Case 12
            array_arrlineb = Array("331000")
        Case 13
            array_arrlineb = Array("412600", "412700", "413600", "413700", _
                            "413900", "414900", "416600", "417100", _
                            "417200", "420100", "422100", "422200", _
                            "422500", "425100", "428300", "428500", _
                            "428700", "438400", "439400", "439700", _
                            "439800", "480100", "480200", " 490100")
        Case 16
            array_arrlineb = Array("412600", "412700", "413600", "416600", _
                                        "417100", "417200", "438400", _
                                        "439400", "439700")
        Case 17
             array_arrlineb = Array("417100", "422100", "422200", "422500", _
                            "425100", "428300", "428500", "428700", _
                            "438400", "439800")
        Case 18
            array_arrlineb = Array("480100", "480200", "490100")
        Case 22
            array_arrlineb = Array("480100", "490100")
        Case 23
            array_arrlineb = Array("480100", "480200", "490100")
        Case 24
            array_arrlineb = Array("480200")
        Case 26
            array_arrlineb = Array("422100", "422500", "425100", "428300", _
                            "428500", "428700")
        Case 27
            array_arrlineb = Array("422100", "422500", "425100", "428300", _
                            "428500", "428700")
        Case 29
            array_arrlineb = Array("422200")
        Case 30
            array_arrlineb = Array("422100", "422500", "425100", "428300", _
                            "428500", "428700")
        Case 33
            array_arrlineb = Array("480200")
        Case 34
            array_arrlineb = Array("422200")
        Case 37
            array_arrlineb = Array("310000")
        Case 43
            array_arrlineb = Array("331000")
        Case 44
            array_arrlineb = Array("310000")
        Case 49
            array_arrlineb = Array("420100", "422100", "422200", "425100", _
                            "480100", "480200", "490100")
        Case 50
            array_arrlineb = Array("422200", "480200")
        Case 55
            array_arrlineb = Array("331000")
        Case Else
            array_arrlineb = Array("")
    End Select

End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
excel VBA - returning the elements for an array from a function
Replies
3
Views
1K
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,214,875
Messages
6,122,047
Members
449,064
Latest member
scottdog129

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top