PDA

View Full Version : [SOLVED] Duplicate declaration in current scope



jimmyyy
12-02-2006, 06:51 PM
I just compiled this together it was working fine till i added more ElsIf's ;)

Error says:
"Compile error: Duplicate declaration in current scope"

Code (pardon it a bit long):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE2 As String = "B216,B419,B622,B825,B1028,B1231,B1434,B1637,B1840,B2043,B2246,B2449,B2652,B 2855,B3058,B3261,B3464,B3667,B3870,B4073,B4276,B4479,B4682,B4885,B5088"
Const WS_RANGE1 As String = "B15,B421,B624,B827,B1030,B1233,B1436,B1639,B1842,B2045,B2248,B2451,B2654,B2 857,B3060,B3263,B3466,B3669,B3872,B4075,B4278,B4481,B4684,B4887,B5090"
Const WS_RANGE2 As String = "B17,B220,B626,B829,B1032,B1235,B1438,B1641,B1844,B2047,B2250,B2453,B2656,B2 859,B3062,B3265,B3468,B3671,B3874,B4077,B4280,B4483,B4686,B4889,B5092"
Const WS_RANGE3 As String = "B19,B222,B425,B831,B1034,B1237,B1440,B1643,B1846,B2049,B2252,B2455,B2658,B2 861,B3064,B3267,B3470,B3673,B3876,B4079,B4282,B4485,B4688,B4891,B5094"
Const WS_RANGE3 As String = "B21,B224,B427,B630,B1036,B1239,B1442,B1645,B1848,B2051,B2254,B2457,B2660,B2 863,B3066,B3269,B3472,B3675,B3878,B4081,B4284,B4487,B4690,B4893,B5096"
Const WS_RANGE3 As String = "B23,B226,B429,B632,B835,B1241,B1444,B1647,B1850,B2053,B2256,B2459,B2662,B28 65,B3068,B3271,B3474,B3677,B3880,B4083,B4286,B4489,B4692,B4895,B5098"
Const WS_RANGE3 As String = "B25,B228,B431,B634,B837,B1040,B1446,B1649,B1852,B2055,B2258,B2461,B2664,B28 67,B3070,B3273,B3476,B3679,B3882,B4085,B4288,B4491,B4694,B4897,B5100"
Const WS_RANGE3 As String = "B27,B230,B433,B636,B839,B1042,B1245,B1651,B1854,B2057,B2260,B2463,B2666,B28 69,B3072,B3275,B3478,B3681,B3884,B4087,B4290,B4493,B4696,B4899,B5102"
Const WS_RANGE3 As String = "B29,B232,B435,B638,B841,B1044,B1247,B1450,B1856,B2059,B2262,B2465,B2668,B28 71,B3074,B3277,B3480,B3683,B3886,B4089,B4292,B4495,B4698,B4901,B5104"
Const WS_RANGE3 As String = "B31,B234,B437,B640,B843,B1046,B1249,B1452,B1655,B2061,B2264,B2467,B2670,B28 73,B3076,B3279,B3482,B3685,B3888,B4091,B4294,B4497,B4700,B4903,B5106"
Const WS_RANGE3 As String = "B33,B236,B439,B642,B845,B1048,B1251,B1454,B1657,B1860,B2266,B2469,B2672,B28 75,B3078,B3281,B3484,B3687,B3890,B4093,B4296,B4499,B4702,B4905,B5108"
Const WS_RANGE3 As String = "B35,B238,B441,B644,B847,B1050,B1253,B1456,B1659,B1862,B2065,B2471,B2674,B28 77,B3080,B3283,B3486,B3689,B3892,B4095,B4298,B4501,B4704,B4907,B5110"
Const WS_RANGE3 As String = "B37,B240,B443,B646,B849,B1052,B1255,B1458,B1661,B1864,B2067,B2270,B2676,B28 79,B3082,B3285,B3488,B3691,B3894,B4097,B4300,B4503,B4706,B4909,B5112"
Const WS_RANGE3 As String = "B39,B242,B445,B648,B851,B1054,B1257,B1460,B1663,B1866,B2069,B2272,B2475,B28 81,B3084,B3287,B3490,B3693,B3896,B4099,B4302,B4505,B4708,B4911,B5114"
Const WS_RANGE3 As String = "B41,B244,B447,B650,B853,B1056,B1259,B1462,B1665,B1868,B2071,B2274,B2477,B26 80,B3086,B3289,B3492,B3695,B3898,B4101,B4304,B4507,B4710,B4913,B5116"
Const WS_RANGE3 As String = "B43,B246,B449,B652,B855,B1058,B1261,B1464,B1667,B1870,B2073,B2276,B2479,B26 82,B2885,B3291,B3494,B3697,B3900,B4103,B4306,B4509,B4712,B4915,B5118"
Const WS_RANGE3 As String = "B45,B248,B451,B654,B857,B1060,B1263,B1466,B1669,B1872,B2075,B2278,B2481,B26 84,B2887,B3090,B3496,B3699,B3902,B4105,B4308,B4511,B4714,B4917,B5120"
Const WS_RANGE3 As String = "B47,B250,B453,B656,B859,B1062,B1265,B1468,B1671,B1874,B2077,B2280,B2483,B26 86,B2889,B3092,B3295,B3701,B3904,B4107,B4310,B4513,B4716,B4919,B5122"
Const WS_RANGE3 As String = "B49,B252,B455,B658,B861,B1064,B1267,B1470,B1673,B1876,B2079,B2282,B2485,B26 88,B2891,B3094,B3297,B3500,B3906,B4109,B4312,B4515,B4718,B4921,B5124"
Const WS_RANGE3 As String = "B51,B254,B457,B660,B863,B1066,B1269,B1472,B1675,B1878,B2081,B2284,B2487,B26 90,B2893,B3096,B3299,B3502,B3705,B4111,B4314,B4517,B4720,B4923,B5126"
Const WS_RANGE3 As String = "B53,B256,B459,B662,B865,B1068,B1271,B1474,B1677,B1880,B2083,B2286,B2489,B26 92,B2895,B3098,B3301,B3504,B3707,B3910,B4316,B4519,B4722,B4925,B5128"
Const WS_RANGE3 As String = "B55,B258,B461,B664,B867,B1070,B1273,B1476,B1679,B1882,B2085,B2288,B2491,B26 94,B2897,B3100,B3303,B3506,B3709,B3912,B4115,B4521,B4724,B4927,B5130"
Const WS_RANGE3 As String = "B57,B260,B463,B666,B869,B1072,B1275,B1478,B1681,B1884,B2087,B2290,B2493,B26 96,B2899,B3102,B3305,B3508,B3711,B3914,B4117,B4320,B4726,B4929,B5132"
Const WS_RANGE3 As String = "B59,B262,B465,B668,B871,B1074,B1277,B1480,B1683,B1886,B2089,B2292,B2495,B26 98,B2901,B3104,B3307,B3510,B3713,B3916,B4119,B4322,B4525,B4931,B5134"
Const WS_RANGE3 As String = "B61,B264,B467,B670,B873,B1076,B1279,B1482,B1685,B1888,B2091,B2294,B2497,B27 00,B2903,B3106,B3309,B3512,B3715,B3918,B4121,B4324,B4527,B4730,B5136"
Const WS_RANGE3 As String = "B63,B266,B469,B672,B875,B1078,B1281,B1484,B1687,B1890,B2093,B2296,B2499,B27 02,B2905,B3108,B3311,B3514,B3717,B3920,B4123,B4326,B4529,B4732,B4935"
If Not Intersect(Target, Me.Range(WS_RANGE1)) Is Nothing Then
With Target
Range("B13").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B218").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B628").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B833").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B1038").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B1243").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B1448").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B1653").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B1858").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B2063").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B2268").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B2473").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B2678").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B2883").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B3088").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B3293").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B3498").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B3703").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B3908").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B4113").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B4318").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B4523").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B4728").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B4933").Select
End With
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
With Target
Range("B5138").Select
End With
End If
End Sub



THE VBA help log says:

The specified name is already used at this level of scope (http://javascript%3Cb%3E%3C/b%3E:hhobj_3.Click%28%29). For example, two variables (http://javascript%3Cb%3E%3C/b%3E:hhobj_4.Click%28%29) can have the same name if they are defined in different procedures (http://javascript%3Cb%3E%3C/b%3E:hhobj_5.Click%28%29), but not if they are defined within the same procedure. This error has the following causes and solutions:

A new variable or procedure has the same name as an existing variable or procedure. For example: Sub MySub()
Dim A As Integer
Dim A As Variant
. . . ' Other declarations or procedure code here.
End Sub
Check the current procedure, module (http://javascript%3Cb%3E%3C/b%3E:hhobj_6.Click%28%29), or project (http://javascript%3Cb%3E%3C/b%3E:hhobj_7.Click%28%29) and remove any duplicate declarations.
A Const statement uses the same name as an existing variable or procedure. Remove or rename the constant (http://javascript%3Cb%3E%3C/b%3E:hhobj_8.Click%28%29) in question.
You declared a fixed array (http://javascript%3Cb%3E%3C/b%3E:hhobj_9.Click%28%29) more than once. Remove or rename one of the arrays.

Search for the duplicate name. When specifying the name to search for, omit any type-declaration character (http://javascript%3Cb%3E%3C/b%3E:hhobj_10.Click%28%29) because a conflict occurs if the names are the same and the type-declaration characters are different.
Note that a module-level (http://javascript%3Cb%3E%3C/b%3E:hhobj_11.Click%28%29) variable can have the same name as a variable declared in a procedure, but when you want to refer to the module-level variable within the procedure, you must qualify it with the module name. Module names and the names of referenced projects (http://javascript%3Cb%3E%3C/b%3E:hhobj_12.Click%28%29) can be reused as variable names within procedures and can also be qualified.
For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

I was Referd to this type of code:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Goto errH
Application.EnableEvents = False
Select Case Target.Address(0, 0)
Case "B15"
Range("B218").Select
Case "B17"
Call Range("B219").Select '<== macro2(target)
Case Else
Goto errH
End Select
errH:
Application.EnableEvents = True
End Sub

but couldnt get it to work

ps. thanks for looking, any ideas love to here um.

mdmackillop
12-02-2006, 07:20 PM
WS_RANGE3 is the name of a Constant. These type of names can only be used once to refer to a String as you are doing.

Wth regard to the "Referred to" code, I posted an alternative.

jimmyyy
12-02-2006, 07:34 PM
thanks MD i was worried about that.

Im sure theres anotherway i can doit. Is it possible to float objects on an excel spreadsheet so when you scroll the object doesn't move w/ the sheet? if not ill have to make a toolbar which i dont prefer.

lucas
12-02-2006, 08:29 PM
As far as your error....it starts in the first 3 lines. If you run the first 3 lines you will get the error you said you didn't get until you added the ifs, etc.

Sub a()
Const WS_RANGE2 As String = "B216,B419,....."
Const WS_RANGE1 As String = "B15,B421,B624,......"
Const WS_RANGE2 As String = "B17,B220,B626,B829,......"
End Sub
In this case your giving Const WS_RANGE2 two different constant(as Malcolm mentioned)values

mdmackillop
12-03-2006, 03:15 AM
Is it possible to float objects on an excel spreadsheet so when you scroll the object doesn't move w/ the sheet? if not ill have to make a toolbar which i dont prefer.
I'm not clear what you mean. You can freeze the top rows and place buttons etc. there.

Bob Phillips
12-03-2006, 10:09 AM
IU would do it this way


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B216,B419,B622,B825,B1028,B1231,B1434,B1637,B1840,B2043,B2246,B2449,B2652, B2855,B3058,B3261,B3464,B3667,B3870,B4073,B4276,B4479,B4682,B4885,B5088")) Is Nothing Then
Range("B13").Select
ElseIf Not Intersect(Target, Range("B15,B421,B624,B827,B1030,B1233,B1436,B1639,B1842,B2045,B2248,B2451,B2654, _
B2857,B3060,B3263,B3466,B3669,B3872,B4075,B4278,B4481,B4684,B4887,B5090")) Is Nothing Then
Range("B218").Select
ElseIf Not Intersect(Target, Range("B17,B220,B626,B829,B1032,B1235,B1438,B1641,B1844,B2047,B2250,B2453,B2656, _
B2859,B3062,B3265,B3468,B3671,B3874,B4077,B4280,B4483,B4686,B4889,B5092")) Is Nothing Then
Range("B628").Select
ElseIf Not Intersect(Target, Range("B19,B222,B425,B831,B1034,B1237,B1440,B1643,B1846,B2049,B2252,B2455,B2658,B 2861,B3064,B3267,B3470,B3673,B3876,B4079,B4282,B4485,B4688,B4891,B5094")) Is Nothing Then
Range("B833").Select
ElseIf Not Intersect(Target, Range("B21,B224,B427,B630,B1036,B1239,B1442,B1645,B1848,B2051,B2254,B2457,B2660,B 2863,B3066,B3269,B3472,B3675,B3878,B4081,B4284,B4487,B4690,B4893,B5096")) Is Nothing Then
Range("B1038").Select
ElseIf Not Intersect(Target, Range("B23,B226,B429,B632,B835,B1241,B1444,B1647,B1850,B2053,B2256,B2459,B2662,B2 865,B3068,B3271,B3474,B3677,B3880,B4083,B4286,B4489,B4692,B4895,B5098")) Is Nothing Then
Range("B1243").Select
ElseIf Not Intersect(Target, Range("B25,B228,B431,B634,B837,B1040,B1446,B1649,B1852,B2055,B2258,B2461,B2664,B2 867,B3070,B3273,B3476,B3679,B3882,B4085,B4288,B4491,B4694,B4897,B5100")) Is Nothing Then
Range("B1448").Select
ElseIf Not Intersect(Target, Range("B27,B230,B433,B636,B839,B1042,B1245,B1651,B1854,B2057,B2260,B2463,B2666,B2 869,B3072,B3275,B3478,B3681,B3884,B4087,B4290,B4493,B4696,B4899,B5102")) Is Nothing Then
Range("B1653").Select
ElseIf Not Intersect(Target, Range("B29,B232,B435,B638,B841,B1044,B1247,B1450,B1856,B2059,B2262,B2465,B2668,B2 871,B3074,B3277,B3480,B3683,B3886,B4089,B4292,B4495,B4698,B4901,B5104")) Is Nothing Then
Range("B1858").Select
ElseIf Not Intersect(Target, Range("B31,B234,B437,B640,B843,B1046,B1249,B1452,B1655,B2061,B2264,B2467,B2670,B2 873,B3076,B3279,B3482,B3685,B3888,B4091,B4294,B4497,B4700,B4903,B5106")) Is Nothing Then
Range("B2063").Select
ElseIf Not Intersect(Target, Range("B33,B236,B439,B642,B845,B1048,B1251,B1454,B1657,B1860,B2266,B2469,B2672,B2 875,B3078,B3281,B3484,B3687,B3890,B4093,B4296,B4499,B4702,B4905,B5108")) Is Nothing Then
Range("B2268").Select
ElseIf Not Intersect(Target, Range("B35,B238,B441,B644,B847,B1050,B1253,B1456,B1659,B1862,B2065,B2471,B2674,B2 877,B3080,B3283,B3486,B3689,B3892,B4095,B4298,B4501,B4704,B4907,B5110")) Is Nothing Then
Range("B2473").Select
ElseIf Not Intersect(Target, Range("B37,B240,B443,B646,B849,B1052,B1255,B1458,B1661,B1864,B2067,B2270,B2676,B2 879,B3082,B3285,B3488,B3691,B3894,B4097,B4300,B4503,B4706,B4909,B5112")) Is Nothing Then
Range("B2678").Select
ElseIf Not Intersect(Target, Range("B39,B242,B445,B648,B851,B1054,B1257,B1460,B1663,B1866,B2069,B2272,B2475,B2 881,B3084,B3287,B3490,B3693,B3896,B4099,B4302,B4505,B4708,B4911,B5114")) Is Nothing Then
Range("B2883").Select
ElseIf Not Intersect(Target, Range("B41,B244,B447,B650,B853,B1056,B1259,B1462,B1665,B1868,B2071,B2274,B2477,B2 680,B3086,B3289,B3492,B3695,B3898,B4101,B4304,B4507,B4710,B4913,B5116")) Is Nothing Then
Range("B3088").Select
ElseIf Not Intersect(Target, Me.Range(WS_RANGE2)) Is Nothing Then
Range("B3293").Select
ElseIf Not Intersect(Target, Range("B43,B246,B449,B652,B855,B1058,B1261,B1464,B1667,B1870,B2073,B2276,B2479,B2 682,B2885,B3291,B3494,B3697,B3900,B4103,B4306,B4509,B4712,B4915,B5118")) Is Nothing Then
Range("B3498").Select
ElseIf Not Intersect(Target, Range("B45,B248,B451,B654,B857,B1060,B1263,B1466,B1669,B1872,B2075,B2278,B2481,B2 684,B2887,B3090,B3496,B3699,B3902,B4105,B4308,B4511,B4714,B4917,B5120")) Is Nothing Then
Range("B3703").Select
ElseIf Not Intersect(Target, Range("B47,B250,B453,B656,B859,B1062,B1265,B1468,B1671,B1874,B2077,B2280,B2483,B2 686,B2889,B3092,B3295,B3701,B3904,B4107,B4310,B4513,B4716,B4919,B5122")) Is Nothing Then
Range("B3908").Select
ElseIf Not Intersect(Target, Range("B49,B252,B455,B658,B861,B1064,B1267,B1470,B1673,B1876,B2079,B2282,B2485,B2 688,B2891,B3094,B3297,B3500,B3906,B4109,B4312,B4515,B4718,B4921,B5124")) Is Nothing Then
Range("B4113").Select
ElseIf Not Intersect(Target, Range("B51,B254,B457,B660,B863,B1066,B1269,B1472,B1675,B1878,B2081,B2284,B2487,B2 690,B2893,B3096,B3299,B3502,B3705,B4111,B4314,B4517,B4720,B4923,B5126")) Is Nothing Then
Range("B4318").Select
ElseIf Not Intersect(Target, Range("B53,B256,B459,B662,B865,B1068,B1271,B1474,B1677,B1880,B2083,B2286,B2489,B2 692,B2895,B3098,B3301,B3504,B3707,B3910,B4316,B4519,B4722,B4925,B5128")) Is Nothing Then
Range("B4523").Select
ElseIf Not Intersect(Target, Range("B55,B258,B461,B664,B867,B1070,B1273,B1476,B1679,B1882,B2085,B2288,B2491,B2 694,B2897,B3100,B3303,B3506,B3709,B3912,B4115,B4521,B4724,B4927,B5130")) Is Nothing Then
Range("B4728").Select
ElseIf Not Intersect(Target, Range("B57,B260,B463,B666,B869,B1072,B1275,B1478,B1681,B1884,B2087,B2290,B2493,B2 696,B2899,B3102,B3305,B3508,B3711,B3914,B4117,B4320,B4726,B4929,B5132")) Is Nothing Then
Range("B4933").Select
ElseIf Not Intersect(Target, Range("B59,B262,B465,B668,B871,B1074,B1277,B1480,B1683,B1886,B2089,B2292,B2495,B2 698,B2901,B3104,B3307,B3510,B3713,B3916,B4119,B4322,B4525,B4931,B5134")) Is Nothing Then
Range("B5138").Select
ElseIf Not Intersect(Target, Range("B61,B264,B467,B670,B873,B1076,B1279,B1482,B1685,B1888,B2091,B2294,B2497,B2 700,B2903,B3106,B3309,B3512,B3715,B3918,B4121,B4324,B4527,B4730,B5136")) Is Nothing Then
'do what
ElseIf Not Intersect(Target, Range("B63,B266,B469,B672,B875,B1078,B1281,B1484,B1687,B1890,B2093,B2296,B2499,B2 702,B2905,B3108,B3311,B3514,B3717,B3920,B4123,B4326,B4529,B4732,B4935")) Is Nothing Then
'do what
End If
End Sub

johnske
12-03-2006, 03:09 PM
Hey jimmyyy,

Please note there is a bug in the VBA tags that introduces an unwanted space when the length of the line reaches a certain size. As a result of this bug, you'll find spaces that will introduce errors, this starts at B2652 (which you'll see has become B 2654) in the code xld's given you and carries on at each line directly below this.

So when you paste this code to the code pane you'll then have to manually go through the code and delete all those spaces - it should then work fine for you :)

Sorry for this inconvenience, but the board coder has been notified about the bug.