ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Compile error: Procedure too large (https://www.excelbanter.com/excel-programming/329811-re-compile-error-procedure-too-large.html)

susan hayes
Compile error: Procedure too large
 
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
Compile error: Procedure too large
 
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" wrote in message ...
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
Compile error: Procedure too large
 
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" wrote in message
...
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, . . . .


All times are GMT +1. The time now is 05:00 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com