How to simplify long syntax

From: Ridzuan <Ridzuan@xxxxxxxxxxxxxxxxxxxxxxxxx>
Date: Fri, 18 May 2007 07:06:00 -0700

I have wrote long syntax, is it posible to shorten my syntax? The syntax are
just like below

Private Sub GetCoordinate()

Dim Xmin As Double
Dim Ymin As Double
Dim Xmax As Double
Dim Ymax As Double

Xmin = 0
Ymin = 0
Xmax = 2500000
Ymax = 2500000
LblXMinCoor = Xmin
LblYMinCoor = Ymin
LblXMaxCoor = Xmax
LblYMaxCoor = Ymax

TxtSheetNo = UCase(TxtSheetNo)

LblSheetNoCC = ""
LblSheetNoUL = ""
LblSheetNoUC = ""
LblSheetNoUR = ""
LblSheetNoCL = ""
LblSheetNoCC = ""
LblSheetNoCR = ""
LblSheetNoLL = ""
LblSheetNoLC = ""
LblSheetNoLR = ""

Select Case Mid(TxtSheetNo, 1, 1) 'A - Z

'Case 1 char Xmin & max

Case "A", "F", "L", "Q", "V"
Xmin = 0
Xmax = 500000

Case "B", "G", "M", "R", "W"
Xmin = 500000
Xmax = 1000000

Case "C", "H", "N", "S", "X"
Xmin = 1000000
Xmax = 1500000

Case "D", "J", "O", "T", "Y"
Xmin = 1500000
Xmax = 2000000

Case "K", "P", "U", "Z"
Xmin = 2000000
Xmax = 2500000

Case "E"
Xmin = -500000
Xmax = 0

End Select

Select Case Mid(TxtSheetNo, 1, 1) 'A - Z

'Case 1 char Y min & max

Case "A", "B", "C", "D", "E"
Ymin = 0
Ymax = 500000

Case "F", "G", "H", "J", "K"
Ymin = 500000
Ymax = 1000000

Case "L", "M", "N", "O", "P"
Ymin = 1000000
Ymax = 1500000

Case "Q", "R", "S", "T", "U"
Ymin = 1500000
Ymax = 2000000

Case "V", "W", "X", "Y", "Z"
Ymin = 2000000
Ymax = 2500000

End Select

Select Case Mid(TxtSheetNo, 2, 1) 'AA - ZZ

'Case 2 char Xmin & max

Case "A", "F", "L", "Q", "V"
Xmin = Xmin
Xmax = Xmin + 100000

Case "B", "G", "M", "R", "W"
Xmin = Xmin + 100000
Xmax = Xmin + 100000

Case "C", "H", "N", "S", "X"
Xmin = Xmin + 200000
Xmax = Xmin + 100000

Case "D", "J", "O", "T", "Y"
Xmin = Xmin + 300000
Xmax = Xmin + 100000

Case "E", "K", "P", "U", "Z"
Xmin = Xmin + 400000
Xmax = Xmin + 100000

End Select

Select Case Mid(TxtSheetNo, 2, 1) 'AA - ZZ

'Case 2 char Y min & max

Case "A", "B", "C", "D", "E"
Ymin = Ymin
Ymax = Ymin + 100000

Case "F", "G", "H", "J", "K"
Ymin = Ymin + 100000
Ymax = Ymin + 100000

Case "L", "M", "N", "O", "P"
Ymin = Ymin + 200000
Ymax = Ymin + 100000

Case "Q", "R", "S", "T", "U"
Ymin = Ymin + 300000
Ymax = Ymin + 100000

Case "V", "W", "X", "Y", "Z"
Ymin = Ymin + 400000
Ymax = Ymin + 100000

End Select

Select Case Mid(TxtSheetNo, 3, 1) 'AA1 - ZZ4

'Case 3 char X min & max

Case "1", "3"
Xmin = Xmin
Xmax = Xmin + 50000

Case "2", "4"
Xmin = Xmin + 50000
Xmax = Xmin + 50000

End Select

Select Case Mid(TxtSheetNo, 3, 1) 'AA1 - ZZ4

'Case 3 char Y min & max

Case "1", "2"
Ymin = Ymin
Ymax = Ymin + 50000

Case "3", "4"
Ymin = Ymin + 50000
Ymax = Ymin + 50000

End Select

Select Case Mid(TxtSheetNo, 4, 1) 'AA11 - ZZ44

'Case 4 char X min & max

Case "1", "3"
Xmin = Xmin
Xmax = Xmin + 25000

Case "2", "4"
Xmin = Xmin + 25000
Xmax = Xmin + 25000

End Select

Select Case Mid(TxtSheetNo, 4, 1) 'AA11 - ZZ44

'Case 4 char Y min & max

Case "1", "2"
Ymin = Ymin
Ymax = Ymin + 25000

Case "3", "4"
Ymin = Ymin + 25000
Ymax = Ymin + 25000

End Select

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Case 1 : 25 000

Select Case Mid(TxtSheetNo, 5, 1) 'AA11A - ZZ44D

'Case 5 char X min & max

Case "A", "C"
Xmin = Xmin
Xmax = Xmin + 12500

Case "B", "D"
Xmin = Xmin + 12500
Xmax = Xmin + 12500

End Select

Select Case Mid(TxtSheetNo, 5, 1) 'AA11A - ZZ44D

'Case 5 char Y min & max

Case "A", "B"
Ymin = Ymin
Ymax = Ymin + 12500

Case "C", "D"
Ymin = Ymin + 12500
Ymax = Ymin + 12500

End Select
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Case 1 : 10 000

Select Case Mid(TxtSheetNo, 5, 2) 'AA1100 - ZZ4424

'Case 5&6 char Xmin & max

Case "00", "05", "10", "15", "20"
Xmin = Xmin
Xmax = Xmin + 5000

Case "01", "06", "11", "16", "21"
Xmin = Xmin + 5000
Xmax = Xmin + 5000

Case "02", "07", "12", "17", "22"
Xmin = Xmin + 10000
Xmax = Xmin + 5000

Case "03", "08", "13", "18", "23"
Xmin = Xmin + 15000
Xmax = Xmin + 5000

Case "04", "09", "14", "19", "24"
Xmin = Xmin + 20000
Xmax = Xmin + 5000

End Select

Select Case Mid(TxtSheetNo, 5, 2) 'AA1100 - ZZ4424

'Case 5&6 char Y min & max

Case "00", "01", "02", "03", "04"
Ymin = Ymin
Ymax = Ymin + 5000

Case "05", "06", "07", "08", "09"
Ymin = Ymin + 5000
Ymax = Ymin + 5000

Case "10", "11", "12", "13", "14"
Ymin = Ymin + 10000
Ymax = Ymin + 5000

Case "15", "16", "17", "18", "19"
Ymin = Ymin + 15000
Ymax = Ymin + 5000

Case "20", "21", "22", "23", "24"
Ymin = Ymin + 20000
Ymax = Ymin + 5000

End Select

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Case 1 : 5000

Select Case Mid(TxtSheetNo, 7, 1) 'AA11001 - ZZ44244

'Case 7 char X min & max

Case "1", "3"
Xmin = Xmin
Xmax = Xmin + 2500

Case "2", "4"
Xmin = Xmin + 2500
Xmax = Xmin + 2500

End Select

Select Case Mid(TxtSheetNo, 7, 1) 'AA11001 - ZZ44244

'Case 7 char Y min & max

Case "1", "2"
Ymin = Ymin
Ymax = Ymin + 2500

Case "3", "4"
Ymin = Ymin + 2500
Ymax = Ymin + 2500

End Select

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Case 1 : 1000

Select Case Mid(TxtSheetNo, 7, 2) 'AA110000 - ZZ442499

'Case 7&8 char Xmin & max

Case "00", "10", "20", "30", "40", "50", "60", "70", "80", "90"
Xmin = Xmin
Xmax = Xmin + 500

Case "01", "11", "21", "31", "41", "51", "61", "71", "81", "91"
Xmin = Xmin + 500
Xmax = Xmin + 500

Case "02", "12", "22", "32", "42", "52", "62", "72", "82", "92"
Xmin = Xmin + 1000
Xmax = Xmin + 500

Case "03", "13", "23", "33", "43", "53", "63", "73", "83", "93"
Xmin = Xmin + 1500
Xmax = Xmin + 500

Case "04", "14", "24", "34", "44", "54", "64", "74", "84", "94"
Xmin = Xmin + 2000
Xmax = Xmin + 500

Case "05", "15", "25", "35", "45", "55", "65", "75", "85", "95"
Xmin = Xmin + 2500
Xmax = Xmin + 500

Case "06", "16", "26", "36", "46", "56", "66", "76", "86", "96"
Xmin = Xmin + 3000
Xmax = Xmin + 500

Case "07", "17", "27", "37", "47", "57", "67", "77", "87", "97"
Xmin = Xmin + 3500
Xmax = Xmin + 500

Case "08", "18", "28", "38", "48", "58", "68", "78", "88", "98"
Xmin = Xmin + 4000
Xmax = Xmin + 500

Case "09", "19", "29", "39", "49", "59", "69", "79", "89", "99"
Xmin = Xmin + 4500
Xmax = Xmin + 500

End Select

Select Case Mid(TxtSheetNo, 7, 2) 'AA110000 - ZZ442499

'Case 7&8 char Y min & max

Case "00", "01", "02", "03", "04", "05", "06", "07", "08", "09"
Ymin = Ymin
Ymax = Ymin + 500

Case "10", "11", "12", "13", "14", "15", "16", "17", "18", "19"
Ymin = Ymin + 500
Ymax = Ymin + 500

Case "20", "21", "22", "23", "24", "25", "26", "27", "28", "29"
Ymin = Ymin + 1000
Ymax = Ymin + 500

Case "30", "31", "32", "33", "34", "35", "36", "37", "38", "39"
Ymin = Ymin + 1500
Ymax = Ymin + 500

Case "40", "41", "42", "43", "44", "45", "46", "47", "48", "49"
Ymin = Ymin + 2000
Ymax = Ymin + 500

Case "50", "51", "52", "53", "54", "55", "56", "57", "58", "59"
Ymin = Ymin + 2500
Ymax = Ymin + 500

Case "60", "61", "62", "63", "64", "65", "66", "67", "68", "69"
Ymin = Ymin + 3000
Ymax = Ymin + 500

Case "70", "71", "72", "73", "74", "75", "76", "77", "78", "79"
Ymin = Ymin + 3500
Ymax = Ymin + 500

Case "80", "81", "82", "83", "84", "85", "86", "87", "88", "89"
Ymin = Ymin + 4000
Ymax = Ymin + 500

Case "90", "91", "92", "93", "94", "95", "96", "97", "98", "99"
Ymin = Ymin + 4500
Ymax = Ymin + 500

End Select
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Select Case Mid(TxtSheetNo, 9, 1) 'AA1100001 - ZZ4424994

'Case 9 char X min & max

Case "1", "3"
Xmin = Xmin
Xmax = Xmin + 250

Case "2", "4"
Xmin = Xmin + 250
Xmax = Xmin + 250

End Select

Select Case Mid(TxtSheetNo, 9, 1) 'AA1100001 - ZZ4424994

'Case 9 char Y min & max

Case "1", "2"
Ymin = Ymin
Ymax = Ymin + 250

Case "3", "4"
Ymin = Ymin + 250
Ymax = Ymin + 250

End Select

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

LblXMinCoor = Xmin
LblYMinCoor = Ymin
LblXMaxCoor = Xmax
LblYMaxCoor = Ymax

Call OtherSheetName
End Sub
.

Follow-Ups:
- Re: How to simplify long syntax
  - From: J French
- Re: How to simplify long syntax
  - From: Rick Rothstein \(MVP - VB\)
- Re: How to simplify long syntax
  - From: Larry Serflaten

Prev by Date: List Box Problem
Next by Date: Font-combo-list program
Previous by thread: List Box Problem
Next by thread: Re: How to simplify long syntax
Index(es):
- Date
- Thread