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:
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.
I am certain this is something I am messing up.
Any suggestions?
Thanks,
Rich
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 Selectas 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 FunctionI am certain this is something I am messing up.
Any suggestions?
Thanks,
Rich
