excel VBA - returning the elements for an array from a function

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
So I have a need to return arrays based on a line item. So I use a loop to determine the variables to be assigned based on the line item. One of the variables is an array. the elements of the array are determined by the line item.

I had the code as:

Code:
28      For i = 1 To 50
29          strFN = ""
30          Select Case i
                Case 1
32                  strFS = "BS"
33                  strLINE = "BS 1A1 Fund Balance"
34                  arrLINE = Array("101000", "109000")
35                  arrLINEe = Array("101000", "109000")
36                  arrLINEb = Array("")
37                  strCALC = "E"
38                  strFN = "F"
39                  strSUM = "No"
40              Case 2
41                  strFS = "BS"
42                  strLINE = "BS 1A3 Accts Receivable"
43                  strCALC = "E"
44                  arrLINE = Array("131000", "132000", "133000", "133500", _
                                "134000", "136000")
45                  arrLINEe = Array("131000", "132000", "133000", "133500", _
                                "134000", "136000")
46                  arrLINEb = Array("")
47                  strFN = "F"
48                  strSUM = "No"
49              Case 3
50                  strFS = "BS"
51                  strLINE = "BS 1A4 Other Assets"
52                  strCALC = "E"
53                  arrLINE = Array("141000", "199000")
54                  arrLINEe = Array("141000", "199000")
55                  arrLINEb = Array("")
56                  strFN = "F"
57                  strSUM = "No"
58              Case 4
59                  strFS = "BS"
60                  strLINE = "BS 1C Accounts Receivable"
61                  strCALC = "E"
62                  arrLINE = Array("131000", "131900", "132000", "134000", _
                                "134700", "136000", "136700", "137000", _
                                "137700")
63                  arrLINEe = Array("131000", "131900", "132000", "134000", _
                                "134700", "136000", "136700", "137000", _
                                "137700")
64                  arrLINEb = Array("")
65                  strFN = "N"
66                  strSUM = "No"
67              Case 5
68                  strFS = "BS"
69                  strLINE = "BS 1F General PPE"
70                  strCALC = "E"
71                  arrLINE = Array("171100", "171200", "171900", "172000", _
                                "173000", "173900", "174000", "174900", _
                                "175000", "175900", "181000", "181900", _
                                "182000", "182900", "183000", "183200", _
                                "183900", "184000", "184900", "189000", _
                                "189900")
72                  arrLINEe = Array("171100", "171200", "171900", "172000", _
                                "173000", "173900", "174000", "174900", _
                                "175000", "175900", "181000", "181900", _
                                "182000", "182900", "183000", "183200", _
                                "183900", "184000", "184900", "189000", _
                                "189900")
73                  arrLINEb = Array("")
74                  strFN = "N"
75                  strSUM = "No"

'*******************************************************************************
'*****  SELECT CASE which sets various string variable and arrays based on the
'*****      value of i (the interation amount)
'*******************************************************************************
76              Case 6
77                  strFS = "BS"
78                  strLINE = "BS 1H Other Assets"
79                  strCALC = "E"
80                  arrLINE = Array("141000", "199000", "199500")
81                  arrLINEe = Array("141000", "199000", "199500")
82                  arrLINEb = Array("")
83                  strFN = "N"
84                  strSUM = "Yes"
85              Case 7
86                  strFS = "BS"
87                  strLINE = "BS 4A1 Accounts Payable"
88                  strCALC = "E"
89                  arrLINE = Array("211000", "212000", "214000", "215000", _
                                "215500", "217000", "296000")
90                  arrLINEe = Array("211000", "212000", "214000", "215000", _
                                "215500", "217000", "296000")
91                  arrLINEb = Array("")
92                  strFN = "F"
93                  strSUM = "No"
94              Case 8
95                  strFS = "BS"
96                  strLINE = "BS 4A3 Other Liabilities"
97                  strCALC = "E"
98                  arrLINE = Array("213000", "219000", "221300", "221500", _
                                "222500", "229000", "231000", "241000", _
                                "297000", "298500", "299000")
99                  arrLINEe = Array("213000", "219000", "221300", "221500", _
                                "222500", "229000", "231000", "241000", _
                                "297000", "298500", "299000")
100                 arrLINEb = Array("")
101                 strFN = "F"
102                 strSUM = "No"
103             Case 9
104                 strFS = "BS"
105                 strLINE = "BS 4B Accounts Payable"
106                 strCALC = "E"
107                 arrLINE = Array("211000", "212000", "214000", "296000")
108                 arrLINEe = Array("211000", "212000", "214000", "296000")
109                 arrLINEb = Array("")
110                 strFN = "N"
111                 strSUM = "No"
112             Case 10
113                 strFS = "BS"
114                 strLINE = "BS 4F Other Liabilities"
115                 strCALC = "E"
116                 arrLINE = Array("213000", "216000", "219000", "221000", _
                                "221100", "221300", "222000", "229000", _
                                "231000", "232000", "240000", "241000", _
                                "291000", "292000", "294000", "299000")
117                 arrLINEe = Array("213000", "216000", "219000", "221000", _
                                "221100", "221300", "222000", "229000", _
                                "231000", "232000", "240000", "241000", _
                                "291000", "292000", "294000", "299000")
118                 arrLINEb = Array("")
119                 strFN = "N"
120                 strSUM = "No"

'*******************************************************************************
'*****  SELECT CASE which sets various string variable and arrays based on the
'*****      value of i (the interation amount)
'*******************************************************************************
121             Case 11
122                 strFS = "BS"
123                 strLINE = "BS 7B Unexp Appro"
124                 strCODE = "Yes"
125                 strCALC = "E, B"
126                 arrLINE = Array("310000", "310100", "310200", "310300", _
                                    "310500", "310600", "310700", "310800", _
                                    "310900")
127                 arrLINEe = Array("310100", "310200", "310300", "310500", _
                                    "310600", "310700", "310800", "310900")
128                 arrLINEb = Array("310000")
129                 strFN = ""
130                 strSUM = "No"
131             Case 12
132                 strFS = "BS"
133                 strLINE = "BS 7D Cum Results of Ops"
134                 strCODE = "Yes"
135                 strCALC = "E, B"
136                 arrLINE = Array("331000", "510000", "510900", "520000", _
                                    "520900", "531000", "531100", "531200", _
                                    "531300", "531700", "531800", "531900", _
                                    "532000", "532400", "532500", "532900", _
                                    "540000", "540900", "560000", "560900", _
                                    "561000", "561900", "564000", "564900", _
                                    "565000", "565900", "570000", "570500", _
                                    "570800", "570900", "572000", "573000", _
                                    "574000", "574500", "575000", "575500", _
                                    "575600", "576000", "576500", "576600", _
                                    "577500", "577600", "578000", "579000", _
                                    "579100", "579200", "580000", "590000", _
                                    "590900", "591900", "599300", "599400", _
                                    "599700", "610000", "619000", "619900", _
                                    "631000", "632000", "633000", "634000", _
                                    "640000", "650000", "660000", "661000", _
                                    "671000", "672000", "673000", "679000", _
                                    "680000", "685000", "690000", "711000", _
                                    "711100", "711200", "718000", "719000", _
                                    "721000", "721100", "721200", "728000", _
                                    "729000", "729200", "730000", "740000", _
                                    "740100", "740500", "760000", "850000", _
                                    "880100", "880200", "880300", "880400")
137                 arrLINEe = Array("510000", "510900", "520000", "520900", _
                                    "531000", "531100", "531200", "531300", _
                                    "531700", "531800", "531900", "532000", _
                                    "532400", "532500", "532900", "540000", _
                                    "540900", "560000", "560900", "561000", _
                                    "561900", "564000", "564900", "565000", _
                                    "565900", "570000", "570500", "570800", _
                                    "570900", "572000", "573000", "574000", _
                                    "574500", "575000", "575500", "575600", _
                                    "576000", "576500", "576600", "577500", _
                                    "577600", "578000", "579000", "579100", _
                                    "579200", "580000", "590000", "590900", _
                                    "591900", "599300", "599400", "599700", _
                                    "610000", "619000", "619900", "631000", _
                                    "632000", "633000", "634000", "640000", _
                                    "650000", "660000", "661000", "671000", _
                                    "672000", "673000", "679000", "680000", _
                                    "685000", "690000", "711000", "711100", _
                                    "711200", "718000", "719000", "721000", _
                                    "721100", "721200", "728000", "729000", _
                                    "729200", "730000", "740000", "740100", _
                                    "740500", "760000", "850000", "880100", _
                                    "880200", "880300", "880400")
138                 arrLINEb = Array("331000")
139                 strFN = ""
140                 strSUM = "No"
141             Case 13
142                 If Not strNEW = "NEW" Then
143                     strFS = "SBR"
144                     strLINE = "SBR 1000 Unobligatred Balance"
145                     strCALC = "B"
146                     arrLINE = 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")
                                        arrLINEe = Array("")
147                     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")
148                     strFN = ""
149                     strSUM = "Yes"
150                 End If
151             Case 14
152                 If Not strNEW = "NEW" Then
153                     strFS = "SBR"
154                     strLINE = "SBR 1021 Rec of Unpaid PY Obs"
155                     strCALC = "E"
156                     arrLINE = Array("412600", "412700", "413600", "413700", _
                                        "413900", "414900", "487100", "497100")
157                     arrLINEe = Array("412600", "412700", "413600", "413700", _
                                        "413900", "414900", "487100", "497100")
158                     arrLINEb = Array("")
159                     strFN = ""
160                     strSUM = "No"
161                 End If
162             Case 15
163                 If Not strNEW = "NEW" Then
164                     strFS = "SBR"
165                     strLINE = "SBR 1043 Other Chg in Unob Bal"
166                     strCALC = "E"
167                     arrLINE = Array("404800", "413400", "414400", "414700", _
                                        "415200", "417600", "418000", "419000", _
                                        "419100", "419200", "419600", "419700", _
                                        "419900", "431000", "432000", "435000", _
                                        "435100", "435500", "435600", "435700", _
                                        "439600", "439900", "487200", "497200")
168                     arrLINEe = Array("404800", "413400", "414400", "414700", _
                                        "415200", "417600", "418000", "419000", _
                                        "419100", "419200", "419600", "419700", _
                                        "419900", "431000", "432000", "435000", _
                                        "435100", "435500", "435600", "435700", _
                                        "439600", "439900", "487200", "497200")
169                     arrLINEb = Array("")
170                     strFN = ""
171                     strSUM = "No"
172                 End If
'*******************************************************************************
'*****  SELECT CASE which sets various string variable and arrays based on the
'*****      value of i (the interation amount)
'*******************************************************************************
173             Case 16
174                 strFS = "SBR"
175                 strLINE = "SBR 1290 Appropriations"
176                 strCODE = "Yes"
177                 strCALC = "E-B, E"
178                 arrLINE = Array("412600", "412700", "413600", "416600", _
                                    "417100", "417200", "438400", "439400", _
                                    "439700", "404700", "411100", "411200", _
                                    "411300", "411400", "411500", "411700", _
                                    "411800", "411900", "412000", "412100", _
                                    "412300", "412400", "412500", "412800", _
                                    "412900", "413000", "413500", "413800", _
                                    "414000", "414600", "415000", "415700", _
                                    "416000", "416500", "416700", "416800", _
                                    "417000", "417300", "417500", "418300", _
                                    "419300", "438200", "438300", "438700", _
                                    "438800", "439000", "439100", "439200", _
                                    "439300", "439500")
179                 arrLINEe = Array("412600", "412700", "413600", "416600", _
                                    "417100", "417200", "438400", "439400", _
                                    "439700", "404700", "411100", "411200", _
                                    "411300", "411400", "411500", "411700", _
                                    "411800", "411900", "412000", "412100", _
                                    "412300", "412400", "412500", "412800", _
                                    "412900", "413000", "413500", "413800", _
                                    "414000", "414600", "415000", "415700", _
                                    "416000", "416500", "416700", "416800", _
                                    "417000", "417300", "417500", "418300", _
                                    "419300", "438200", "438300", "438700", _
                                    "438800", "439000", "439100", "439200", _
                                    "439300", "439500")
180                 arrLINEb = Array("412600", "412700", "413600", "416600", _
                                    "417100", "417200", "438400", "439400", _
                                    "439700")
181                 strFN = ""
182                 strSUM = "No"
183             Case 17
184                 strFS = "SBR"
185                 strLINE = "SBR 1890 Spending Auth from Offsetting Colls"
186                 strCODE = "Yes"
187                 strCALC = "E-B, E"
188                 arrLINE = Array("417100", "422100", "422200", "422500", _
                                    "425100", "428300", "428500", "428700", _
                                    "438400", "439800", "406000", "407000", _
                                    "413200", "413500", "414000", "414600", _
                                    "415100", "415800", "417000", "417300", _
                                    "417500", "421000", "421200", "421500", _
                                    "425200", "425300", "425500", "426000", _
                                    "426100", "426200", "426300", "426400", _
                                    "426500", "426600", "426700", "427100", _
                                    "427300", "427500", "427600", "427700", _
                                    "438200", "438300", "439200", "439500")
189                 arrLINEe = Array("417100", "422100", "422200", "422500", _
                                    "425100", "428300", "428500", "428700", _
                                    "438400", "439800", "406000", "407000", _
                                    "413200", "413500", "414000", "414600", _
                                    "415100", "415800", "417000", "417300", _
                                    "417500", "421000", "421200", "421500", _
                                    "425200", "425300", "425500", "426000", _
                                    "426100", "426200", "426300", "426400", _
                                    "426500", "426600", "426700", "427100", _
                                    "427300", "427500", "427600", "427700", _
                                    "438200", "438300", "439200", "439500")
190                 arrLINEb = Array("417100", "422100", "422200", "422500", _
                                    "425100", "428300", "428500", "428700", _
                                    "438400", "439800")
191                 strSUM = "Yes"
192             Case 18
193                 strFS = "SBR"
194                 strLINE = "SBR 2190 New Obs and Upward Adjs"
195                 strCODE = "Yes"
196                 strCALC = "E-B, E"
197                 arrLINE = Array("480100", "480200", "488100", "488200", _
                                    "490100", "490200", "498100", "498200")
198                 arrLINEe = Array("480100", "480200", "488100", "488200", _
                                    "490100", "490200", "498100", "498200")
199                 arrLINEb = Array("480100", "480200", "490100")
200                 strSUM = "Yes"
201             Case 19
202                 strFS = "SBR"
203                 strLINE = "SBR 2204 Apportioned Unexpired"
204                 strCALC = "E"
205                 arrLINE = Array("451000", "459000", "461000", "470000")
206                 arrLINEe = Array("451000", "459000", "461000", "470000")
207                 arrLINEb = Array("")
208                 strSUM = "No"
209             Case 20
210                 strFS = "SBR"
211                 strLINE = "SBR 2404 Unapportioned Unexpired"
212                 strCALC = "E"
213                 arrLINE = Array("406000", "407000", "421000", "421500", _
                                    "431000", "442000", "443000", "445000", _
                                    "463000")
214                 arrLINEe = Array("406000", "407000", "421000", "421500", _
                                    "431000", "442000", "443000", "445000", _
                                    "463000")
215                 arrLINEb = Array("")
216                 strSUM = "No"

'*******************************************************************************
'*****  SELECT CASE which sets various string variable and arrays based on the
'*****      value of i (the interation amount)
'*******************************************************************************
217             Case 21
218                 strFS = "SBR"
219                 strLINE = "SBR 2413 Expired Unob Bal"
220                 strCALC = "E"
221                 arrLINE = Array("465000")
222                 arrLINEe = Array("465000")
223                 arrLINEb = Array("")
224                 strSUM = "No"
225             Case 22
226                 If Not strNEW = "NEW" Then
227                     strFS = "SBR"
228                     strLINE = "SBR 3000 Unpaid Obligations"
229                     strCALC = "B"
230                     arrLINE = Array("480100", "490100")
231                     arrLINEe = Array("")
232                     arrLINEb = Array("480100", "490100")
233                     strSUM = "No"
234                 End If
235             Case 23
236                 If Not strNEW = "NEW" Then
237                     strFS = "SBR"
238                     strLINE = "SBR 3012 New Obs"
239                     strCODE = "Yes"
240                     strCALC = "E-B, E"
241                     arrLINE = Array("480100", "480200", "488100", "488200", _
                                        "490100", "490200", "498100", "498200")
242                     arrLINEe = Array("480100", "480200", "488100", "488200", _
                                        "490100", "490200", "498100", "498200")
243                     arrLINEb = Array("480100", "480200", "490100")
244                     strSUM = "Yes"
245                 End If
246             Case 24
247                 If Not strNEW = "NEW" Then
248                     strFS = "SBR"
249                     strLINE = "SBR 3020 Outlays"
250                     strCODE = "Yes"
251                     strCALC = "E-B, E"
252                     arrLINE = Array("480200", "488200", "490200", "498200")
253                     arrLINEe = Array("480200", "488200", "490200", "498200")
254                     arrLINEb = Array("480200")
255                     strSUM = "Yes"
256                 End If
257             Case 25
258                 If Not strNEW = "NEW" Then
259                     strFS = "SBR"
260                     strLINE = "SBR 3042 Rec of PY Unpaid Obs"
261                     strCALC = "E"
262                     arrLINE = Array("487100", "497100")
263                     arrLINEe = Array("487100", "497100")
264                     arrLINEb = Array("")
265                     strSUM = "No"
266                 End If

'*******************************************************************************
'*****  SELECT CASE which sets various string variable and arrays based on the
'*****      value of i (the interation amount)
'*******************************************************************************
267             Case 26
268                 If Not strNEW = "NEW" Then
269                     strFS = "SBR"
270                     strLINE = "SBR 3060 Uncollected Payments Fed Sources"
271                     strCALC = "B"
272                     arrLINE = Array("422100", "422500", "425100", "428300", _
                                        "428500", "428700")
273                     arrLINEe = Array("")
274                     arrLINEb = Array("422100", "422500", "425100", "428300", _
                                        "428500", "428700")
275                     strSUM = "No"
276                 End If
277             Case 27
278                 If Not strNEW = "NEW" Then
279                     strFS = "SBR"
280                     strLINE = "SBR 3072 Change in Uncollected Payments"
281                     strCODE = "Yes"
282                     strCALC = "E-B"
283                     arrLINE = Array("422100", "422500", "425100", "428300", _
                                        "428500", "428700")
284                     arrLINEe = Array("422100", "422500", "425100", "428300", _
                                        "428500", "428700")
285                     arrLINEb = Array("422100", "422500", "425100", "428300", _
                                        "428500", "428700")
286                     strSUM = "Yes"
287                 End If
288             Case 28
289                 If Not strNEW = "NEW" Then
290                     strFS = "SBR"
291                     strLINE = "SBR 3090 Uncollected payments"
292                     strCALC = "E"
293                     arrLINE = Array("419900", "422100", "422500", "423000", _
                                        "423200", "423300", "423400", "425100", _
                                        "428300", "428500", "428700")
294                     arrLINEe = Array("419900", "422100", "422500", "423000", _
                                        "423200", "423300", "423400", "425100", _
                                        "428300", "428500", "428700")
295                     arrLINEb = Array("")
296                     strSUM = "No"
297                 End If
298             Case 29
299                 If Not strNEW = "NEW" Then
300                     strFS = "SBR"
301                     strLINE = "SBR 4176 Actual offsetting Colls"
302                     strCODE = "Yes"
303                     strCALC = "E-B, E"
304                     arrLINE = Array("421200", "422200", "425200", "425300", _
                                        "425500", "426000", "426100", "426200", _
                                        "426300", "426400", "426500", "426600", _
                                        "426700", "427100", "427300", "427500", _
                                        "427600", "427700", "487200", "497200")
305                     arrLINEe = Array("421200", "422200", "425200", "425300", _
                                        "425500", "426000", "426100", "426200", _
                                        "426300", "426400", "426500", "426600", _
                                        "426700", "427100", "427300", "427500", _
                                        "427600", "427700", "487200", "497200")
306                     arrLINEb = Array("422200")
307                     strSUM = "Yes"
308                 End If
309             Case 30
310                 If Not strNEW = "NEW" Then
311                     strFS = "SBR"
312                     strLINE = "SBR 4177 Change in Uncollected pymts"
313                     strCODE = "Yes"
314                     strCALC = "E-B, E"
315                     arrLINE = Array("422100", "422500", "425100", "428300", _
                                        "428500", "428700")
316                     arrLINEe = Array("422100", "422500", "425100", "428300", _
                                        "428500", "428700")
317                     arrLINEb = Array("422100", "422500", "425100", "428300", _
                                        "428500", "428700")
318                     strSUM = "Yes"
319                 End If

'*******************************************************************************
'*****  SELECT CASE which sets various string variable and arrays based on the
'*****      value of i (the interation amount)
'*******************************************************************************
320             Case 31
321                 If Not strNEW = "NEW" Then
322                     strFS = "SBR"
323                     strLINE = "SBR 4178 Recoveries of PY Paid Obs"
324                     strCALC = "E"
325                     arrLINE = Array("487200", "497200")
326                     arrLINEe = Array("487200", "497200")
327                     arrLINEb = Array("")
328                     strSUM = "No"
329                 End If
330             Case 32
331                 If Not strNEW = "NEW" Then
332                     strFS = "SBR"
333                     strLINE = "SBR 4179 Anticipated Offsetting Collections"
334                     strCALC = "E"
335                     arrLINE = Array("406000", "407000", "421000", "421500")
336                     arrLINEe = Array("406000", "407000", "421000", "421500")
337                     arrLINEb = Array("")
338                     strSUM = "No"
339                 End If
340             Case 33
341                 If Not strNEW = "NEW" Then
342                     strFS = "SBR"
343                     strLINE = "SBR 4185 Outlays Gross"
344                     strCODE = "Yes"
345                     strCALC = "E-B, E"
346                     arrLINE = Array("480200", "488200", "490200", "498200")
347                     arrLINEe = Array("480200", "488200", "490200", "498200")
348                     arrLINEb = Array("480200")
349                     strSUM = "Yes"
350                 End If
351             Case 34
352                 If Not strNEW = "NEW" Then
353                     strFS = "SBR"
354                     strLINE = "SBR 4187 Actual Offsetting Colls"
355                     strCODE = "Yes"
356                     strCALC = "E-B, E"
357                     arrLINE = Array("421200", "422200", "425200", "425300", _
                                        "425500", "426000", "426100", "426200", _
                                        "426300", "426400", "426500", "426600", _
                                        "426700", "427100", "427300", "427500", _
                                        "427600", "427700", "487200", " 497200")
358                     arrLINEe = Array("421200", "422200", "425200", "425300", _
                                        "425500", "426000", "426100", "426200", _
                                        "426300", "426400", "426500", "426600", _
                                        "426700", "427100", "427300", "427500", _
                                        "427600", "427700", "487200", " 497200")
359                     arrLINEb = Array("422200")
360                     strSUM = "Yes"
361                 End If
362             Case 35
363                 strFS = "SNC"
364                 strLINE = "SNC 1A Gross Costs"
365                 strCALC = "E"
366                 arrLINE = Array("610000", "619000", "619900", "631000", _
                                    "632000", "633000", "634000", "640000", _
                                    "650000", "660000", "661000", "671000", _
                                    "672000", "673000", "679000", "680000", _
                                    "685000", "690000", "721000", "721100", _
                                    "721200", "728000", "729000", "729200", _
                                    "730000", "760000", "850000", "880100", _
                                    "880200", "880300", "880400")
367                 arrLINEe = Array("610000", "619000", "619900", "631000", _
                                    "632000", "633000", "634000", "640000", _
                                    "650000", "660000", "661000", "671000", _
                                    "672000", "673000", "679000", "680000", _
                                    "685000", "690000", "721000", "721100", _
                                    "721200", "728000", "729000", "729200", _
                                    "730000", "760000", "850000", "880100", _
                                    "880200", "880300", "880400")
368                 arrLINEb = Array("")
369                 strSUM = "No"

'*******************************************************************************
'*****  SELECT CASE which sets various string variable and arrays based on the
'*****      value of i (the interation amount)
'*******************************************************************************
370             Case 36
371                 strFS = "SNC"
372                 strLINE = "SNC 1B Earned Rev"
373                 strCALC = "E"
374                 arrLINE = Array("510000", "510900", "520000", "520900", _
                                    "531000", "531100", "531200", "531300", _
                                    "531700", "531800", "531900", "532500", _
                                    "532900", "540000", "540900", "590000", _
                                    "590900", "711000", "711100", "711200", _
                                    "718000", "719000")
375                 arrLINEe = Array("510000", "510900", "520000", "520900", _
                                    "531000", "531100", "531200", "531300", _
                                    "531700", "531800", "531900", "532500", _
                                    "532900", "540000", "540900", "590000", _
                                    "590900", "711000", "711100", "711200", _
                                    "718000", "719000")
376                 arrLINEb = Array("")
377                 strSUM = "No"
378             Case 37
379                 strFS = "SNP"
380                 strLINE = "SNP 1 Beginning Balances"
381                 strCALC = "B"
382                 If strNEW = "NEW" Then
383                     arrLINE = Array("310000")
384                     arrLINEe = Array("")
385                     arrLINEb = Array("310000")
386                 Else
387                     arrLINE = Array("331000")
388                     arrLINEe = Array("")
389                     arrLINEb = Array("331000")
390                 End If
391                 strSUM = "No"
392             Case 38
393                 strFS = "SNP"
394                 strLINE = "SNP 4A"
395                 strCALC = "E"
396                 If strNEW = "NEW" Then
397                     arrLINE = Array("310100")
398                     arrLINEe = Array("310100")
399                     arrLINEb = Array("")
400                 Else
401                     arrLINE = Array("579000", "591900")
402                     arrLINEe = Array("579000", "591900")
403                     arrLINEb = Array("")
404                     strSUM = "No"
405                 End If
406             Case 39
407                 If strNEW = "NEW" Then
408                     arrLINE = Array("")
409                     arrLINEe = Array("")
410                     arrLINEb = Array("")
411                 Else
412                     strFS = "SNP"
413                     strLINE = "SNP 4B Approp Used"
414                     strCALC = "E"
415                     arrLINE = Array("570000")
416                     arrLINEe = Array("570000")
417                     arrLINEb = Array("")
418                     strSUM = "No"
419                 End If
420             Case 40
421                 strFS = "SNP"
422                 strLINE = "SNP 4C Non-Exch Rev"
423                 strCALC = "E"
424                 If strNEW = "NEW" Then
425                     arrLINE = Array("310600")
426                     arrLINEe = Array("310600")
427                     arrLINEb = Array("")
428                 Else
429                     arrLINE = Array("531000", "531100", "531200", "531700", _
                                        "531800", "531900", "532000", "532400", _
                                        "540000", "540900", "580000", "580400", _
                                        "590000", "590900")
430                     arrLINEe = Array("531000", "531100", "531200", "531700", _
                                        "531800", "531900", "532000", "532400", _
                                        "540000", "540900", "580000", "580400", _
                                        "590000", "590900")
431                     arrLINEb = Array("")
432                     strSUM = "No"
433                 End If
'*******************************************************************************
'*****  SELECT CASE which sets various string variable and arrays based on the
'*****      value of i (the interation amount)
'*******************************************************************************
434             Case 41
435                 If Not strNEW = "NEW" Then
436                     strFS = "SNP"
437                     strLINE = "SNP 5C Imp Fin from Costs"
438                     arrLINE = Array("578000")
439                     arrLINEe = Array("578000")
440                     strCALC = "E"
441                     arrLINEb = Array("")
442                     strSUM = "No"
443                 End If
444             Case 42
445                 If Not strNEW = "NEW" Then
446                     strFS = "SNP"
447                     strLINE = "SNP 5D Other"
448                     arrLINE = Array("579100", "590000", "590900", "599300", _
                                        "599400", "711000", "718000", "719000", _
                                        "721000", "728000", "729000", "729200")
449                     arrLINEe = Array("579100", "590000", "590900", "599300", _
                                        "599400", "711000", "718000", "719000", _
                                        "721000", "728000,", "729000", "729200")
450                     arrLINEb = Array("")
451                     strCALC = "E"
452                     strSUM = "No"
453                 End If
454             Case 43
455                 strFS = "SNP"
456                 If strNEW = "NEW" Then
457                     arrLINE = Array("331000")
458                     arrLINEe = Array("")
459                     arrLINEb = Array("331000")
460                     strLINE = "SNP 7 Beginning Balances"
461                     strCALC = "B"
462                 Else
463                     strLINE = "SNP 7 Net Cost of Operations"
464                     arrLINE = Array("510000", "510900", "520000", "520900", _
                                        "531000", "531100", "531200", "531300", _
                                        "531700", "531800", "531900", "532500", _
                                        "532900", "540000", "540900", "590000", _
                                        "590900", "610000", "619000", "619900", _
                                        "631000", "632000", "633000", "634000", _
                                        "640000", "650000", "660000", "661000", _
                                        "671000", "672000", "673000", "679000", _
                                        "680000", "685000", "690000", "711000", _
                                        "711100", "711200", "718000", "719000", _
                                        "721000", "721100", "721200", "728000", _
                                        "729000", "729200", "730000", "760000", _
                                        "850000", "880100", "880200", "880300", _
                                        "880400")
465                     arrLINEe = Array("510000", "510900", "520000", "520900", _
                                        "531000", "531100", "531200", "531300", _
                                        "531700", "531800", "531900", "532500", _
                                        "532900", "540000", "540900", "590000", _
                                        "590900", "610000", "619000", "619900", _
                                        "631000", "632000", "633000", "634000", _
                                        "640000", "650000", "660000", "661000", _
                                        "671000", "672000", "673000", "679000", _
                                        "680000", "685000", "690000", "711000", _
                                        "711100", "711200", "718000", "719000", _
                                        "721000", "721100", "721200", "728000", _
                                        "729000", "729200", "730000", "760000", _
                                        "850000", "880100", "880200", "880300", _
                                        "880400")
466                     strCALC = "E"
467                 End If
468                 arrLINEb = Array("")
469                 strSUM = "No"
470             Case 44
471                 If Not strNEW = "NEW" Then
472                     strFS = "SNP"
473                     strLINE = "SNP 10 Beg Bal"
474                     arrLINE = Array("310000")
475                     arrLINEe = Array("")
476                     arrLINEb = Array("310000")
477                     strCALC = "B"
478                     strSUM = "No"
479                 End If
480             Case 45
481                 If Not strNEW = "NEW" Then
482                     strFS = "SNP"
483                     strLINE = "SNP 13A Approp Recd"
484                     arrLINE = Array("310100")
485                     arrLINEe = Array("310100")
486                     arrLINEb = Array("")
487                     strCALC = "E"
488                     strSUM = "No"
489                 End If
'*******************************************************************************
'*****  SELECT CASE which sets various string variable and arrays based on the
'*****      value of i (the interation amount)
'*******************************************************************************
490             Case 46
491                 If Not strNEW = "NEW" Then
492                     strFS = "SNP"
493                     strLINE = "SNP 13B Approp Trans"
494                     arrLINE = Array("310200", "310300")
495                     arrLINEe = Array("310200", "310300")
496                     arrLINEb = Array("")
497                     strCALC = "E"
498                     strSUM = "No"
499                 End If
500             Case 47
501                 If Not strNEW = "NEW" Then
502                     strFS = "SNP"
503                     strLINE = "SNP 13C"
504                     arrLINE = Array("310600")
505                     arrLINEe = Array("310600")
506                     arrLINEb = Array("")
507                     strCALC = "E"
508                     strSUM = "No"
509                 End If
510             Case 48
511                 If Not strNEW = "NEW" Then
512                     strFS = "SNP"
513                     strLINE = "SNP 13D Appro Used"
514                     arrLINE = Array("310700")
515                     arrLINEe = Array("310700")
516                     arrLINEb = Array("")
517                     strCALC = "E"
518                     strSUM = "No"
519                 End If
520             Case 49
521                 If strNEW = "NEW" Then
522                     strFS = "SBR"
523                     strLINE = "SBR 1051 Unobligated balance from prior year budget authority, net"
524                     strCALC = "E-B"
525                     arrLINE = Array("420100", "422100", "422200", "425100", "435000", _
                                        "480100", "480200", "487100", "490100", "497100", "497200")
526                     arrLINEe = Array("422100", "422200", "425100", "435000", "480100", _
                                        "480200", "487100", "490100", "497100", "497200")
527                     arrLINEb = Array("420100", "422100", "422200", "425100", "480100", _
                                        "480200", "490100")
528                     strFN = ""
529                     strSUM = "Yes"
530                 End If
531             Case 50
532                 If strNEW = "NEW" Then
533                     strFS = "SBR"
534                     strLINE = "SBR 4190 Outlays, net"
535                     strCALC = "E-B"
536                     arrLINE = Array("422200", "425200", "480200", "490200", "497200", "498200")
537                     arrLINEe = Array("422200", "425200", "480200", "490200", "497200", "498200")
538                     arrLINEb = Array("422200", "480200")
539                     strFN = ""
540                     strSUM = "Yes"
541                 End If
542             Case 51
543                 If strNEW = "NEW" Then
544                     strFS = "SNP"
545                     strLINE = "SNP 10A Other Adjustments"
546                     arrLINE = Array("591900")
547                     arrLINEe = Array("591900")
548                     arrLINEb = Array("")
549                     strCALC = "E"
550                     strSUM = "No"
551                 End If
552             Case 52
553                 If strNEW = "NEW" Then
554                     strFS = "SNP"
555                     strLINE = "SNP 10B Appropriations Used"
556                     arrLINE = Array("570000")
557                     arrLINEe = Array("570000")
558                     arrLINEb = Array("")
559                     strCALC = "E"
560                     strSUM = "No"
561                 End If
562             Case 53
563                 If strNEW = "NEW" Then
564                     strFS = "SNP"
565                     strLINE = "SNP 11C Imputed Financing"
566                     arrLINE = Array("578000")
567                     arrLINEe = Array("578000")
568                     arrLINEb = Array("")
569                     strCALC = "E"
570                     strSUM = "No"
571                 End If
572             Case 54
573                 If strNEW = "NEW" Then
574                     strFS = "SNP"
575                     strLINE = "SNP 11D Other"
576                     arrLINE = Array("719000", "729000")
577                     arrLINEe = Array("719000", "729000")
578                     arrLINEb = Array("")
579                     strCALC = "E"
580                     strSUM = "No"
581                 End If
582         End Select

as we can see that section of the code is 500 lines long due to the need to assign the variables based on 54 separate line items. I am trying to shorten that sub by breaking the sub up into functions that will provide the variables. However, when I try this with the arrays (which worked perfectly fine in the select case) I get a "Sub or function not defined" compile error.

Here is one of the functions I wrote to return the array variables this throws the compile error.

Code:
Function a_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
    a_arrlineb = Array(arrLINEb)
End Function

I am certain this is something I am messing up.

Any suggestions?

Thanks,

Rich
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
Hi dave,

I cant believe that I did not try that.

Thanks,
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,112,768
Messages
5,542,427
Members
410,550
Latest member
ganeshsamant
Top