Re: How to simplify long syntax
- From: "Larry Serflaten" <serflaten@xxxxxxxxxxxxxx>
- Date: Fri, 18 May 2007 11:05:14 -0500
"Ridzuan" <Ridzuan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote
I have wrote long syntax, is it posible to shorten my syntax? The syntax are
just like below
Yes it is possible, but you'll have to puzzle it out. For a quick example, this
whole Select/Case section :
Select Case Mid(TxtSheetNo, 1, 1) 'A - Z
Case "A", "F", "L", "Q", "V"
Case "B", "G", "M", "R", "W"
Case "C", "H", "N", "S", "X"
Case "D", "J", "O", "T", "Y"
Case "K", "P", "U", "Z"
Case "E"
End Select
Could be replaced by something like this:
idx = InStr("E----AFLQVBGMRWHNSX-DJOTYKPUZ-", Mid(TxtSheetNo, 1, 1)) \ 6 - 1
Xmin = idx * 500000
Xmax = Xmin + 500000
Other sections could use a similar technique...
LFS
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
.
- References:
- How to simplify long syntax
- From: Ridzuan
- How to simplify long syntax
- Prev by Date: Re: Runtime Error 0 - What?
- Next by Date: Re: String manipulation
- Previous by thread: How to simplify long syntax
- Next by thread: Re: How to simplify long syntax
- Index(es):
