Excel VBA: Procedure too large - doomed?

Y

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,881
Office Version
  1. 2010
Platform
  1. Windows
I received a "procedure too large" error which, according to web search result, can be solved by splitting codes into smaller subroutines. In my case, I don't know how to do it.

My error occurs in "select case". I have more than 200 cases to process. Any idea how to split cases into smaller unit?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Maybe you need to look at why you have 200 cases to select from !
There may be a better way to process the data.

Having said that you may need to consider doing a Select case on say 20 - 50 criteria, with a Case else to call another macro with the next block of select Case
 
Upvote 0
Michael M said:
Maybe you need to look at why you have 200 cases to select from !
There may be a better way to process the data.

Having said that you may need to consider doing a Select case on say 20 - 50 criteria, with a Case else to call another macro with the next block of select Case
Click to expand...
Good thought. I'll try that if the problem comes up again.

I export cashflow data from Quicken. The data contains categories such as meat, processed meat, seafood, processed seafood, canned meat, vegetable, oil, etc. In the report, there are only few categories like food, beverage, non-food, etc. That's why I use case to group things together.
 
Upvote 0
Well, as Denis suggested, and I alluded to looking for better ways to process the data.....A vlookup will almost certainly be worth looking at !!
 
Upvote 0
Norie said:
Can you post the code?
Click to expand...
My first trial is like the following, which gave the "procedure too large" error.

Code: 
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]case "baking  supply"[/TD]
[/TR]
[TR]
[TD]If  wb_source.Sheets("test").Cells(count, accountColumn).Value = USD  Then[/TD]
[/TR]
[TR]
[TD]if cells(PURCHASED_FOODrowno,  dayColumnNo+daycolumnoffset).value<>0 then[/TD]
[/TR]
[TR]
[TD]cells(PURCHASED_FOODrowno,  dayColumnNo+daycolumnoffset).formula="=(" &  trim(str(cellvalue)) * exchangerate & "+" &  str(cells(PURCHASED_FOODrowno,dayColumnNo+daycolumnoffset).value)  & "*1000)/1000"[/TD]
[/TR]
[TR]
[TD]else[/TD]
[/TR]
[TR]
[TD]cells(PURCHASED_FOODrowno,  dayColumnNo+daycolumnoffset).formula="=(" &  TRIM(str(cellvalue))* exchangerate & ")/1000"[/TD]
[/TR]
[TR]
[TD]end if[/TD]
[/TR]
[TR]
[TD]else[/TD]
[/TR]
[TR]
[TD]if cells(PURCHASED_FOODrowno,  dayColumnNo+daycolumnoffset).value<>0 then[/TD]
[/TR]
[TR]
[TD]cells(PURCHASED_FOODrowno,  dayColumnNo+daycolumnoffset).formula="=(" &  trim(str(cellvalue)) & "+" &  str(cells(PURCHASED_FOODrowno,dayColumnNo+daycolumnoffset).value) &  "*1000)/1000"[/TD]
[/TR]
[TR]
[TD]Else[/TD]
[/TR]
[TR]
[TD]cells(PURCHASED_FOODrowno,  dayColumnNo+daycolumnoffset).formula="=(" &  trim(str(cellvalue)) & ")/1000"[/TD]
[/TR]
[TR]
[TD]End If[/TD]
[/TR]
[TR]
[TD]end if[/TD]
[/TR]
</tbody>[/TABLE]

I then tried the following code, which still gave me the same error message.

Code: 
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]case "baking  supply"[/TD]
[/TR]
[TR]
[TD]if  wb_source.sheets("test").cells(count,accountColumn).value=usd and  cells(PURCHASED_FOODrowno, dayColumnNo+daycolumnoffset).value<>0 then[/TD]
[/TR]
[TR]
[TD]cells(PURCHASED_FOODrowno,  dayColumnNo+daycolumnoffset).formula="=(" &  trim(str(cellvalue)) * exchangerate & "+" &  str(cells(PURCHASED_FOODrowno,dayColumnNo+daycolumnoffset).value)  & "*1000)/1000"[/TD]
[/TR]
[TR]
[TD]elseif  wb_source.sheets("test").cells(count,accountColumn).value=usd and  cells(PURCHASED_FOODrowno, dayColumnNo+daycolumnoffset).value=0 then[/TD]
[/TR]
[TR]
[TD]cells(PURCHASED_FOODrowno,  dayColumnNo+daycolumnoffset).formula="=(" &  TRIM(str(cellvalue))* exchangerate & ")/1000"[/TD]
[/TR]
[TR]
[TD]elseif cells(PURCHASED_FOODrowno,  dayColumnNo+daycolumnoffset).value<>0 then[/TD]
[/TR]
[TR]
[TD]cells(PURCHASED_FOODrowno,  dayColumnNo+daycolumnoffset).formula="=(" &  trim(str(cellvalue)) & "+" &  str(cells(PURCHASED_FOODrowno,dayColumnNo+daycolumnoffset).value) &  "*1000)/1000"[/TD]
[/TR]
[TR]
[TD]Else[/TD]
[/TR]
[TR]
[TD]cells(PURCHASED_FOODrowno,  dayColumnNo+daycolumnoffset).formula="=(" &  trim(str(cellvalue)) & ")/1000"[/TD]
[/TR]
[TR]
[TD]End If[/TD]
[/TR]
</tbody>[/TABLE]

Finally, I moved the USD out of the case by setting the iexchangerate before selecting case. This got rid of the error message. But I haven't processed all the 200+ cases yet. Only about two third have been processed. Will the problem come back if all the 200+ cases have been processed? I keep my fingers crossed.

Code: 
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]case "baking  supply"[/TD]
[/TR]
[TR]
[TD]if cells(PURCHASE_FOODrowno,  dayColumnNo+daycolumnoffset).value<>0 then[/TD]
[/TR]
[TR]
[TD]cells(PURCHASE_FOODrowno,  dayColumnNo+daycolumnoffset).formula="=(" &  trim(str(cellvalue)) * iexchangerate & "+" &  str(cells(PURCHASE_FOODrowno,dayColumnNo+daycolumnoffset).value)  & "*1000)/1000"[/TD]
[/TR]
[TR]
[TD]else[/TD]
[/TR]
[TR]
[TD]cells(PURCHASE_FOODrowno,  dayColumnNo+daycolumnoffset).formula="=(" &  TRIM(str(cellvalue))* iexchangerate & ")/1000"[/TD]
[/TR]
[TR]
[TD]End If[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
SydneyGeek said:
Any chance of building a table in the workbook and using a VLOOKUP to get the data?

Denis
Click to expand...
Thank you for the suggestion. Everybody says VLOOKUP is very useful. I have never used it. I need to learn how to use it.

What I am doing is something like this:

Case "seafood"
add to Food 'add seafood expense to the cell that contains the food expense
Case "vegetable"
add to food
Case "chopsticks"
add to non-food
Case "coke"
add to beverage
...

With the little knowledge I have on VLOOKUP, I don't know how it can help.
 
Upvote 0
If it's coming from Quicken, I'd imagine there would be a Parent group and then a Sub - Group
Maybe the VLOOKUP table should be based on the parent group
 
Upvote 0
To give a bit of a primer on VLOOKUP:
Here's a lookup table -- animals with a classification in the second column
Excel 2010
MN
AnimalClassification
CatMammal
DogMammal
AxolotlAmphibian
SparrowBird
CanaryBird
SkinkReptile
LlamaMammal
CoyoteMammal
BudgieBird
EmuBird
Bull SharkFish
TelapiaFish

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

</tbody>
Sheet1​



And here's a main data table (only 1 column) that uses VLOOKUP to pull in the correct classification.
Excel 2010
AB
AnimalClassification
CatMammal
DogMammal
AxolotlAmphibian
SparrowBird
CanaryBird
SkinkReptile
LlamaMammal
CoyoteMammal
BudgieBird
EmuBird
Bull SharkFish
TelapiaFish

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=VLOOKUP(A2,$M$2:$N$13,2,FALSE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

A2 is the value that you want to look up. M2:N13 (absolute ref) is the lookup table. 2 is the column of the table (must always be to the right of the lookup column), and FALSE means that you want an exact match.

In your code you can use WorksheetFunction.VLOOKUP to call the function, then use the above syntax. That should get you down to a handful of Case statements. Something like:
Code: 
Select Case WorksheetFunction.VLOOKUP(Cells(Rw,2),Sheets("Lookup").Range("A2:B250"),2,FALSE)
  Case "Food"

  Case "Beverage"

  Case "Non-Food"

End Select

Denis
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

excelbytes
  • Solved
Looping Procedure In VBA Code
2 3
Replies
24
Views
420
excelbytes
excelbytes
W
  • Question Question
Excel VBA - Check For Duplicates and Return the Count on a Large Data Set
Replies
4
Views
262
Kaper
Kaper
S
  • Question Question
Linear Programming
Replies
1
Views
207
igold
igold
B
  • Question Question
Can' Exit Design Mode Because Control 'xxx' can not be created
Replies
3
Views
629
Dan_W
D
E
  • Solved
Convert Subroutine to Function?
Replies
8
Views
373
EssKayKay
E

Forum statistics

Threads
1,223,246
Messages
6,170,987
Members
452,373
Latest member
TimReeks

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