Re: Compile error: Procedure too large

Susan Hayes · 05-20-2005, 12:06 PM

Hello, in writing the following code I came across the following message:

Compile error: Procedure too large

I don't know why this message came up?

Here is a sample of the 'select case' I wrote for one country (this is repeated for 19 other countries in similar
fashion.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim country As String
country = Cells(6, "B").Value
Select Case country
Case "United Kingdom":
Cells(60, "B").Value = "LN"
Cells(61, "B").Value = "British Pound"
Cells(63, "B").Value = "GBp"
Cells(2, "B").Value = "London Stock Exchange Hours: 8:00 am to 4:30 pm Monday to Friday"
Cells(3, "B").Value = "Settlement: T + 5"
Cells(67, "B").Value = Cells(13, "S").Value * Cells(9, "B").Value / 100
Cells(66, "B").Value = Cells(13, "U").Value * Cells(9, "B").Value / 100
Cells(3, "D").Value = "TD Waterhouse UK"
Cells(3, "F").Value = Cells(78, "D").Value
Cells(65, "B").Value = Cells(13, "O").Value
If Cells(62, "B").Value = "USD" Then
Cells(16, "B").Value = Cells(13, "U").Value
Cells(17, "B").Value = Cells(13, "U").Value * Cells(15, "B").Value / 100
ElseIf Cells(62, "B").Value = "CAD" Then
Cells(16, "B").Value = Cells(13, "S").Value
Cells(17, "B").Value = Cells(13, "S").Value * Cells(15, "B").Value / 100
End If
'holiday
Range("j2").Value = Range("ab54").Value
Range("j3").Value = Range("ab55").Value
Range("j4").Value = Range("ab56").Value
Range("j5").Value = Range("ab57").Value
Range("j6").Value = Range("ab58").Value
Range("j7").Value = Range("ab59").Value
Range("j8").Value = Range("ab60").Value
Range("j9").Value = Range("ab61").Value
Range("j10").Value = Range("ab62").Value
Range("j11").Value = Range("ab63").Value
Range("j12").Value = Range("ab64").Value
Range("j13").Value = Range("ab65").Value
Range("j14").Value = Range("ab66").Value
'date
Range("k2").Value = Range("ac54").Value
Range("k3").Value = Range("ac55").Value
Range("k4").Value = Range("ac56").Value
Range("k5").Value = Range("ac57").Value
Range("k6").Value = Range("ac58").Value
Range("k7").Value = Range("ac59").Value
Range("k8").Value = Range("ac60").Value
Range("k9").Value = Range("ac61").Value
Range("k10").Value = Range("ac62").Value
Range("k11").Value = Range("ac63").Value
Range("k12").Value = Range("ac64").Value
Range("k13").Value = Range("ac65").Value
Range("k14").Value = Range("ac66").Value

Case "Hong Kong":
Cells(60, "B").Value = "HK"
Cells(61, "B").Value = "Hong Kong Dollar"
Cells(63, "B").Value = "HKD"
" " " "
" " " "
" "
End Select

'repeated again for 19 other countries Hong Kong, Austria, . . . .

'I then continue on with the following code, again for 19 other countries.

Select Case Range("K17").Value
Case "United Kingdom":
Cells(18, "L").Value = "GBp"
If Range("K16").Value = "USD" Then
Range("B75").Value = Range("U13").Value
ElseIf Range("K16").Value = "CAD" Then
Range("B75").Value = Range("S13").Value
End If

Case "Hong Kong":
Cells(18, "L").Value = "HKD"
" " "
" " "
" "
End Select

'repeated again for 19 other countries Hong Kong, Austria, . . . .

Ron de Bruin · 05-20-2005, 12:06 PM

Hi susan

You can use this to make it shorter

Range("j2:j14").Value = Range("ab54:ab66").Value

and

Range("k2:k14").Value = Range("ac54:ac66").Value

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Susan Hayes" <[email protected]> wrote in message news:[email protected]...
> Hello, in writing the following code I came across the following message:
>
> Compile error: Procedure too large
>
> I don't know why this message came up?
>
> Here is a sample of the 'select case' I wrote for one country (this is repeated for 19 other countries in similar
> fashion.
>
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Dim country As String
> country = Cells(6, "B").Value
> Select Case country
> Case "United Kingdom":
> Cells(60, "B").Value = "LN"
> Cells(61, "B").Value = "British Pound"
> Cells(63, "B").Value = "GBp"
> Cells(2, "B").Value = "London Stock Exchange Hours: 8:00 am to 4:30 pm Monday to Friday"
> Cells(3, "B").Value = "Settlement: T + 5"
> Cells(67, "B").Value = Cells(13, "S").Value * Cells(9, "B").Value / 100
> Cells(66, "B").Value = Cells(13, "U").Value * Cells(9, "B").Value / 100
> Cells(3, "D").Value = "TD Waterhouse UK"
> Cells(3, "F").Value = Cells(78, "D").Value
> Cells(65, "B").Value = Cells(13, "O").Value
> If Cells(62, "B").Value = "USD" Then
> Cells(16, "B").Value = Cells(13, "U").Value
> Cells(17, "B").Value = Cells(13, "U").Value * Cells(15, "B").Value / 100
> ElseIf Cells(62, "B").Value = "CAD" Then
> Cells(16, "B").Value = Cells(13, "S").Value
> Cells(17, "B").Value = Cells(13, "S").Value * Cells(15, "B").Value / 100
> End If
> 'holiday
> Range("j2").Value = Range("ab54").Value
> Range("j3").Value = Range("ab55").Value
> Range("j4").Value = Range("ab56").Value
> Range("j5").Value = Range("ab57").Value
> Range("j6").Value = Range("ab58").Value
> Range("j7").Value = Range("ab59").Value
> Range("j8").Value = Range("ab60").Value
> Range("j9").Value = Range("ab61").Value
> Range("j10").Value = Range("ab62").Value
> Range("j11").Value = Range("ab63").Value
> Range("j12").Value = Range("ab64").Value
> Range("j13").Value = Range("ab65").Value
> Range("j14").Value = Range("ab66").Value
> 'date
> Range("k2").Value = Range("ac54").Value
> Range("k3").Value = Range("ac55").Value
> Range("k4").Value = Range("ac56").Value
> Range("k5").Value = Range("ac57").Value
> Range("k6").Value = Range("ac58").Value
> Range("k7").Value = Range("ac59").Value
> Range("k8").Value = Range("ac60").Value
> Range("k9").Value = Range("ac61").Value
> Range("k10").Value = Range("ac62").Value
> Range("k11").Value = Range("ac63").Value
> Range("k12").Value = Range("ac64").Value
> Range("k13").Value = Range("ac65").Value
> Range("k14").Value = Range("ac66").Value
>
> Case "Hong Kong":
> Cells(60, "B").Value = "HK"
> Cells(61, "B").Value = "Hong Kong Dollar"
> Cells(63, "B").Value = "HKD"
> " " " "
> " " " "
> " "
> End Select
>
> 'repeated again for 19 other countries Hong Kong, Austria, . . . .
>
> 'I then continue on with the following code, again for 19 other countries.
>
> Select Case Range("K17").Value
> Case "United Kingdom":
> Cells(18, "L").Value = "GBp"
> If Range("K16").Value = "USD" Then
> Range("B75").Value = Range("U13").Value
> ElseIf Range("K16").Value = "CAD" Then
> Range("B75").Value = Range("S13").Value
> End If
>
> Case "Hong Kong":
> Cells(18, "L").Value = "HKD"
> " " "
> " " "
> " "
> End Select
>
> 'repeated again for 19 other countries Hong Kong, Austria, . . . .

Tom Ogilvy · 05-20-2005, 12:06 PM

Hard coding values like this in the code itself is generally not the best
way to go. It seems you could put this information in another sheet with
one row per country, then pick up the information from there (including cell
addresses).

--
Regards,
Tom Ogilvy

"Susan Hayes" <[email protected]> wrote in message
news:[email protected]...
> Hello, in writing the following code I came across the following message:
>
> Compile error: Procedure too large
>
> I don't know why this message came up?
>
> Here is a sample of the 'select case' I wrote for one country (this is
repeated for 19 other countries in similar
> fashion.
>
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Dim country As String
> country = Cells(6, "B").Value
> Select Case country
> Case "United Kingdom":
> Cells(60, "B").Value = "LN"
> Cells(61, "B").Value = "British Pound"
> Cells(63, "B").Value = "GBp"
> Cells(2, "B").Value = "London Stock Exchange Hours: 8:00 am to 4:30 pm
Monday to Friday"
> Cells(3, "B").Value = "Settlement: T + 5"
> Cells(67, "B").Value = Cells(13, "S").Value * Cells(9, "B").Value / 100
> Cells(66, "B").Value = Cells(13, "U").Value * Cells(9, "B").Value / 100
> Cells(3, "D").Value = "TD Waterhouse UK"
> Cells(3, "F").Value = Cells(78, "D").Value
> Cells(65, "B").Value = Cells(13, "O").Value
> If Cells(62, "B").Value = "USD" Then
> Cells(16, "B").Value = Cells(13, "U").Value
> Cells(17, "B").Value = Cells(13, "U").Value * Cells(15, "B").Value /
100
> ElseIf Cells(62, "B").Value = "CAD" Then
> Cells(16, "B").Value = Cells(13, "S").Value
> Cells(17, "B").Value = Cells(13, "S").Value * Cells(15, "B").Value /
100
> End If
> 'holiday
> Range("j2").Value = Range("ab54").Value
> Range("j3").Value = Range("ab55").Value
> Range("j4").Value = Range("ab56").Value
> Range("j5").Value = Range("ab57").Value
> Range("j6").Value = Range("ab58").Value
> Range("j7").Value = Range("ab59").Value
> Range("j8").Value = Range("ab60").Value
> Range("j9").Value = Range("ab61").Value
> Range("j10").Value = Range("ab62").Value
> Range("j11").Value = Range("ab63").Value
> Range("j12").Value = Range("ab64").Value
> Range("j13").Value = Range("ab65").Value
> Range("j14").Value = Range("ab66").Value
> 'date
> Range("k2").Value = Range("ac54").Value
> Range("k3").Value = Range("ac55").Value
> Range("k4").Value = Range("ac56").Value
> Range("k5").Value = Range("ac57").Value
> Range("k6").Value = Range("ac58").Value
> Range("k7").Value = Range("ac59").Value
> Range("k8").Value = Range("ac60").Value
> Range("k9").Value = Range("ac61").Value
> Range("k10").Value = Range("ac62").Value
> Range("k11").Value = Range("ac63").Value
> Range("k12").Value = Range("ac64").Value
> Range("k13").Value = Range("ac65").Value
> Range("k14").Value = Range("ac66").Value
>
> Case "Hong Kong":
> Cells(60, "B").Value = "HK"
> Cells(61, "B").Value = "Hong Kong Dollar"
> Cells(63, "B").Value = "HKD"
> " " " "
> " " " "
> " "
> End Select
>
> 'repeated again for 19 other countries Hong Kong, Austria, . . . .
>
> 'I then continue on with the following code, again for 19 other countries.
>
> Select Case Range("K17").Value
> Case "United Kingdom":
> Cells(18, "L").Value = "GBp"
> If Range("K16").Value = "USD" Then
> Range("B75").Value = Range("U13").Value
> ElseIf Range("K16").Value = "CAD" Then
> Range("B75").Value = Range("S13").Value
> End If
>
> Case "Hong Kong":
> Cells(18, "L").Value = "HKD"
> " " "
> " " "
> " "
> End Select
>
> 'repeated again for 19 other countries Hong Kong, Austria, . . . .