Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Syntax
Hi Minitman,
For one possible approach, try something like: '========== Public Sub Demo() Dim rRng As Range Dim i As Long For i = 1 To 3 Set rRng = myRange(i) MsgBox rRng.Address(0, 0) Next i End Sub '-------------- Public Function myRange(iArg As Long) As Range Dim Rng1 As Range Dim Rng2 As Range Dim Rng3 As Range Dim arr As Variant Dim rRng As Range Dim i As Long Set Rng1 = Range("A1:A10") Set Rng2 = Range("A20:A25") Set Rng3 = Range("A30:A40") arr = Array(Rng1, Rng2, Rng3) Set myRange = arr(iArg - 1) End Function '<<========== As an alternative to the array, you could replace the function with the following version: '========== Public Function myRange(iArg As Long) As Range Dim Rng1 As Range Dim Rng2 As Range Dim Rng3 As Range Dim myColl As Collection Dim rRng As Range Dim i As Long Set Rng1 = Range("A1:A10") Set Rng2 = Range("A20:A25") Set Rng3 = Range("A30:A40") Set myColl = New Collection myColl.Add Rng1, Rng1.Address myColl.Add Rng2, Rng2.Address myColl.Add Rng3, Rng3.Address Set myRange = myColl(iArg) End Function '<<========== --- Regards. Norman "Minitman" wrote in message ... Greeting, I am confused over how to make a variable to be variable. I am running Office 2003 on an XP Pro machine I have a set of 21 variables called Rng1, Rng2, ... Rng21. these variable are equal to named ranges on a sheet. So far so good. I am trying to eliminate the use of select case in this procedure. It appears that I have the syntax wrong. Here is one of the case statements: Sub DoThis(iArg as Integer) Select Case iArg ... Case 6 Set rRng = Rng6 Set cmBox = TB6 ... I tried: Set rRng = Range("Rng" & iArg) And got the following error on this line: Run-time error '1004': Method 'Range' of object '_Global' failed. The Rng's are dimmed as Range. Anyone have any idea what I did wring? Or on how to fix it? Any help is appreciated. -Minitman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Syntax
Hey Norman,
Thanks for the reply. I am not sure a function is what I need. Here is my code for this procedure (shortened to 3 items): '========== Option Explicit Dim lDup As Long Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, rng As Range, Dim ListRange As Range Dim vSwap1 As Variant, vSwap2 As Variant, item As Variant __________________________________________________ _________ Sub DefaultSet() On Error Resume Next Set wks = ThisWorkbook.Sheets("DataSheet") Set Rng1 = wks.Range("NamedRange1 ") Set Rng2 = wks.Range("NamedRange2") Set Rng3 = wks.Range("NamedRange3") On Error GoTo 0 End Sub __________________________________________________ _________ Sub NonDuplicatesList(lPass As Long) Dim rRng As Range Dim cmBox As ComboBox Dim i As Integer Dim l As Long, j As Long, lRow As Long Dim iItem As Integer, iTBIndex As Integer Dim NoDupes As Collection Set NoDupes = New Collection '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''' 'This is the code I tried to replace the following code with ' Set rRng = Range("Rng" & lPass) ' Set cmBox = Me.Controls("TB" & lPass) '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''' Select Case lPass Case 1 Set rRng = Rng1 Set cmBox = TB1 Case 2 Set rRng = Rng2 Set cmBox = TB2 Case 3 Set rRng = Rng3 Set cmBox = TB3 End Select '\\ Load the NoDupes Collection On Error Resume Next For lRow = 1 To rRng.Rows.Count With rRng(lRow) If Not .Value = vbNullString Then NoDupes.Add .Value, CStr(.Value) End If End With Next lRow On Error GoTo 0 '\\ Sort the collection (optional) j = 1 l = 1 For l = 1 To NoDupes.Count - 1 For j = l + 1 To NoDupes.Count If NoDupes(l) NoDupes(j) Then vSwap1 = NoDupes(l) vSwap2 = NoDupes(j) NoDupes.Add vSwap1, befo=j NoDupes.Add vSwap2, befo=l NoDupes.Remove l + 1 NoDupes.Remove j + 1 End If Next j Next l l = 1 j = 1 For Each item In NoDupes cmBox.AddItem item Next item i = 1 For i = 1 To NoDupes.Count NoDupes.Remove 1 'Removes 1st item every cycle until empty Next i i = 1 ' clear memory Set NoDupes = Nothing End Sub __________________________________________________ _________ Private Sub UserForm_Initialize() DefaultSet For lDup = 1 To 3 Call NonDuplicatesList(lDup) Next lDup End Sub '<<========== This all worked until I tried to replace the select case above as noted (I am trying to shorted the code. There are quite a few of the rRng variables). Is there anyway to replace that select case? Please let me know, thanks. _Minitman On Mon, 26 May 2008 11:57:33 +0100, "Norman Jones" wrote: Hi Minitman, For one possible approach, try something like: '========== Public Sub Demo() Dim rRng As Range Dim i As Long For i = 1 To 3 Set rRng = myRange(i) MsgBox rRng.Address(0, 0) Next i End Sub '-------------- Public Function myRange(iArg As Long) As Range Dim Rng1 As Range Dim Rng2 As Range Dim Rng3 As Range Dim arr As Variant Dim rRng As Range Dim i As Long Set Rng1 = Range("A1:A10") Set Rng2 = Range("A20:A25") Set Rng3 = Range("A30:A40") arr = Array(Rng1, Rng2, Rng3) Set myRange = arr(iArg - 1) End Function '<<========== As an alternative to the array, you could replace the function with the following version: '========== Public Function myRange(iArg As Long) As Range Dim Rng1 As Range Dim Rng2 As Range Dim Rng3 As Range Dim myColl As Collection Dim rRng As Range Dim i As Long Set Rng1 = Range("A1:A10") Set Rng2 = Range("A20:A25") Set Rng3 = Range("A30:A40") Set myColl = New Collection myColl.Add Rng1, Rng1.Address myColl.Add Rng2, Rng2.Address myColl.Add Rng3, Rng3.Address Set myRange = myColl(iArg) End Function '<<========== --- Regards. Norman "Minitman" wrote in message .. . Greeting, I am confused over how to make a variable to be variable. I am running Office 2003 on an XP Pro machine I have a set of 21 variables called Rng1, Rng2, ... Rng21. these variable are equal to named ranges on a sheet. So far so good. I am trying to eliminate the use of select case in this procedure. It appears that I have the syntax wrong. Here is one of the case statements: Sub DoThis(iArg as Integer) Select Case iArg ... Case 6 Set rRng = Rng6 Set cmBox = TB6 ... I tried: Set rRng = Range("Rng" & iArg) And got the following error on this line: Run-time error '1004': Method 'Range' of object '_Global' failed. The Rng's are dimmed as Range. Anyone have any idea what I did wring? Or on how to fix it? Any help is appreciated. -Minitman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Syntax
Hi Minitman,
I am not sure a function is what I need. The function was intended to sdemonstrate the concept. The following works for me: '========== Option Explicit Dim lDup As Long Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, rng As Range Dim arr As Variant Dim ListRange As Range Dim vSwap1 As Variant, vSwap2 As Variant, item As Variant Private Sub Defaultset() Dim wks As Worksheet On Error Resume Next Set wks = ThisWorkbook.Sheets("DataSheet") Set Rng1 = wks.Range("One") Set Rng2 = wks.Range("Two") Set Rng3 = wks.Range("Three") On Error GoTo 0 arr = VBA.Array(Rng1, Rng2, Rng3) End Sub Sub NonDuplicatesList(lPass As Long) Dim rRng As Range Dim cmBox As ComboBox Dim i As Integer Dim l As Long, j As Long, lRow As Long Dim iItem As Integer, iTBIndex As Integer Dim NoDupes As Collection Set NoDupes = New Collection Set rRng = arr(lPass - 1) Set cmBox = Me.Controls("TB" & lPass) '\\ Load the NoDupes Collection On Error Resume Next For lRow = 1 To rRng.Rows.Count With rRng(lRow) If Not .Value = vbNullString Then NoDupes.Add .Value, CStr(.Value) End If End With Next lRow On Error GoTo 0 '\\ Sort the collection (optional) j = 1 l = 1 For l = 1 To NoDupes.Count - 1 For j = l + 1 To NoDupes.Count If NoDupes(l) NoDupes(j) Then vSwap1 = NoDupes(l) vSwap2 = NoDupes(j) NoDupes.Add vSwap1, befo=j NoDupes.Add vSwap2, befo=l NoDupes.Remove l + 1 NoDupes.Remove j + 1 End If Next j Next l l = 1 j = 1 For Each item In NoDupes cmBox.AddItem item Next item i = 1 For i = 1 To NoDupes.Count NoDupes.Remove 1 'Removes 1st item every cycle until empty Next i i = 1 ' clear memory Set NoDupes = Nothing End Sub Private Sub UserForm_Initialize() Call Defaultset For lDup = 1 To 3 Call NonDuplicatesList(lDup) Next lDup End Sub '<<========== Incidentally, I found the naming of the ComboBoxes as TB1, TB2 ... TBn, confusing! :-) --- Regards. Norman "Minitman" wrote in message ... Hey Norman, Thanks for the reply. I am not sure a function is what I need. Here is my code for this procedure (shortened to 3 items): '========== Option Explicit Dim lDup As Long Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, rng As Range, Dim ListRange As Range Dim vSwap1 As Variant, vSwap2 As Variant, item As Variant __________________________________________________ _________ Sub DefaultSet() On Error Resume Next Set wks = ThisWorkbook.Sheets("DataSheet") Set Rng1 = wks.Range("NamedRange1 ") Set Rng2 = wks.Range("NamedRange2") Set Rng3 = wks.Range("NamedRange3") On Error GoTo 0 End Sub __________________________________________________ _________ Sub NonDuplicatesList(lPass As Long) Dim rRng As Range Dim cmBox As ComboBox Dim i As Integer Dim l As Long, j As Long, lRow As Long Dim iItem As Integer, iTBIndex As Integer Dim NoDupes As Collection Set NoDupes = New Collection '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''' 'This is the code I tried to replace the following code with ' Set rRng = Range("Rng" & lPass) ' Set cmBox = Me.Controls("TB" & lPass) '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''' Select Case lPass Case 1 Set rRng = Rng1 Set cmBox = TB1 Case 2 Set rRng = Rng2 Set cmBox = TB2 Case 3 Set rRng = Rng3 Set cmBox = TB3 End Select '\\ Load the NoDupes Collection On Error Resume Next For lRow = 1 To rRng.Rows.Count With rRng(lRow) If Not .Value = vbNullString Then NoDupes.Add .Value, CStr(.Value) End If End With Next lRow On Error GoTo 0 '\\ Sort the collection (optional) j = 1 l = 1 For l = 1 To NoDupes.Count - 1 For j = l + 1 To NoDupes.Count If NoDupes(l) NoDupes(j) Then vSwap1 = NoDupes(l) vSwap2 = NoDupes(j) NoDupes.Add vSwap1, befo=j NoDupes.Add vSwap2, befo=l NoDupes.Remove l + 1 NoDupes.Remove j + 1 End If Next j Next l l = 1 j = 1 For Each item In NoDupes cmBox.AddItem item Next item i = 1 For i = 1 To NoDupes.Count NoDupes.Remove 1 'Removes 1st item every cycle until empty Next i i = 1 ' clear memory Set NoDupes = Nothing End Sub __________________________________________________ _________ Private Sub UserForm_Initialize() DefaultSet For lDup = 1 To 3 Call NonDuplicatesList(lDup) Next lDup End Sub '<<========== This all worked until I tried to replace the select case above as noted (I am trying to shorted the code. There are quite a few of the rRng variables). Is there anyway to replace that select case? Please let me know, thanks. _Minitman On Mon, 26 May 2008 11:57:33 +0100, "Norman Jones" wrote: Hi Minitman, For one possible approach, try something like: '========== Public Sub Demo() Dim rRng As Range Dim i As Long For i = 1 To 3 Set rRng = myRange(i) MsgBox rRng.Address(0, 0) Next i End Sub '-------------- Public Function myRange(iArg As Long) As Range Dim Rng1 As Range Dim Rng2 As Range Dim Rng3 As Range Dim arr As Variant Dim rRng As Range Dim i As Long Set Rng1 = Range("A1:A10") Set Rng2 = Range("A20:A25") Set Rng3 = Range("A30:A40") arr = Array(Rng1, Rng2, Rng3) Set myRange = arr(iArg - 1) End Function '<<========== As an alternative to the array, you could replace the function with the following version: '========== Public Function myRange(iArg As Long) As Range Dim Rng1 As Range Dim Rng2 As Range Dim Rng3 As Range Dim myColl As Collection Dim rRng As Range Dim i As Long Set Rng1 = Range("A1:A10") Set Rng2 = Range("A20:A25") Set Rng3 = Range("A30:A40") Set myColl = New Collection myColl.Add Rng1, Rng1.Address myColl.Add Rng2, Rng2.Address myColl.Add Rng3, Rng3.Address Set myRange = myColl(iArg) End Function '<<========== --- Regards. Norman "Minitman" wrote in message . .. Greeting, I am confused over how to make a variable to be variable. I am running Office 2003 on an XP Pro machine I have a set of 21 variables called Rng1, Rng2, ... Rng21. these variable are equal to named ranges on a sheet. So far so good. I am trying to eliminate the use of select case in this procedure. It appears that I have the syntax wrong. Here is one of the case statements: Sub DoThis(iArg as Integer) Select Case iArg ... Case 6 Set rRng = Rng6 Set cmBox = TB6 ... I tried: Set rRng = Range("Rng" & iArg) And got the following error on this line: Run-time error '1004': Method 'Range' of object '_Global' failed. The Rng's are dimmed as Range. Anyone have any idea what I did wring? Or on how to fix it? Any help is appreciated. -Minitman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Syntax
Hey Norman,
Thanks again for the reply. I am having a hard time understanding what the purpose of the array (arr) is? How does it load the RowSource of the ComboBoxes? I forgot to mention that the named ranges are dynamic and not related to each other. This is why I am wondering if an array is even feasible? I am asking because I don't understand arrays or how they work and have not worked with any arrays as yet. If I understand what is going on (and I don't think I do), it seems like the array (arr) has all of the entries from all of named ranges. I don't see anyway for the code to limit which column in the array is being added to which ComboBox's RowSource. Could you please explain how this works? I really need to know. I am looking forward to hearing from you. -Minitman On Mon, 26 May 2008 13:47:46 +0100, "Norman Jones" wrote: Hi Minitman, I am not sure a function is what I need. The function was intended to sdemonstrate the concept. The following works for me: '========== Option Explicit Dim lDup As Long Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, rng As Range Dim arr As Variant Dim ListRange As Range Dim vSwap1 As Variant, vSwap2 As Variant, item As Variant Private Sub Defaultset() Dim wks As Worksheet On Error Resume Next Set wks = ThisWorkbook.Sheets("DataSheet") Set Rng1 = wks.Range("One") Set Rng2 = wks.Range("Two") Set Rng3 = wks.Range("Three") On Error GoTo 0 arr = VBA.Array(Rng1, Rng2, Rng3) End Sub Sub NonDuplicatesList(lPass As Long) Dim rRng As Range Dim cmBox As ComboBox Dim i As Integer Dim l As Long, j As Long, lRow As Long Dim iItem As Integer, iTBIndex As Integer Dim NoDupes As Collection Set NoDupes = New Collection Set rRng = arr(lPass - 1) Set cmBox = Me.Controls("TB" & lPass) '\\ Load the NoDupes Collection On Error Resume Next For lRow = 1 To rRng.Rows.Count With rRng(lRow) If Not .Value = vbNullString Then NoDupes.Add .Value, CStr(.Value) End If End With Next lRow On Error GoTo 0 '\\ Sort the collection (optional) j = 1 l = 1 For l = 1 To NoDupes.Count - 1 For j = l + 1 To NoDupes.Count If NoDupes(l) NoDupes(j) Then vSwap1 = NoDupes(l) vSwap2 = NoDupes(j) NoDupes.Add vSwap1, befo=j NoDupes.Add vSwap2, befo=l NoDupes.Remove l + 1 NoDupes.Remove j + 1 End If Next j Next l l = 1 j = 1 For Each item In NoDupes cmBox.AddItem item Next item i = 1 For i = 1 To NoDupes.Count NoDupes.Remove 1 'Removes 1st item every cycle until empty Next i i = 1 ' clear memory Set NoDupes = Nothing End Sub Private Sub UserForm_Initialize() Call Defaultset For lDup = 1 To 3 Call NonDuplicatesList(lDup) Next lDup End Sub '<<========== Incidentally, I found the naming of the ComboBoxes as TB1, TB2 ... TBn, confusing! :-) --- Regards. Norman "Minitman" wrote in message .. . Hey Norman, Thanks for the reply. I am not sure a function is what I need. Here is my code for this procedure (shortened to 3 items): '========== Option Explicit Dim lDup As Long Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, rng As Range, Dim ListRange As Range Dim vSwap1 As Variant, vSwap2 As Variant, item As Variant __________________________________________________ _________ Sub DefaultSet() On Error Resume Next Set wks = ThisWorkbook.Sheets("DataSheet") Set Rng1 = wks.Range("NamedRange1 ") Set Rng2 = wks.Range("NamedRange2") Set Rng3 = wks.Range("NamedRange3") On Error GoTo 0 End Sub __________________________________________________ _________ Sub NonDuplicatesList(lPass As Long) Dim rRng As Range Dim cmBox As ComboBox Dim i As Integer Dim l As Long, j As Long, lRow As Long Dim iItem As Integer, iTBIndex As Integer Dim NoDupes As Collection Set NoDupes = New Collection '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''' 'This is the code I tried to replace the following code with ' Set rRng = Range("Rng" & lPass) ' Set cmBox = Me.Controls("TB" & lPass) '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''' Select Case lPass Case 1 Set rRng = Rng1 Set cmBox = TB1 Case 2 Set rRng = Rng2 Set cmBox = TB2 Case 3 Set rRng = Rng3 Set cmBox = TB3 End Select '\\ Load the NoDupes Collection On Error Resume Next For lRow = 1 To rRng.Rows.Count With rRng(lRow) If Not .Value = vbNullString Then NoDupes.Add .Value, CStr(.Value) End If End With Next lRow On Error GoTo 0 '\\ Sort the collection (optional) j = 1 l = 1 For l = 1 To NoDupes.Count - 1 For j = l + 1 To NoDupes.Count If NoDupes(l) NoDupes(j) Then vSwap1 = NoDupes(l) vSwap2 = NoDupes(j) NoDupes.Add vSwap1, befo=j NoDupes.Add vSwap2, befo=l NoDupes.Remove l + 1 NoDupes.Remove j + 1 End If Next j Next l l = 1 j = 1 For Each item In NoDupes cmBox.AddItem item Next item i = 1 For i = 1 To NoDupes.Count NoDupes.Remove 1 'Removes 1st item every cycle until empty Next i i = 1 ' clear memory Set NoDupes = Nothing End Sub __________________________________________________ _________ Private Sub UserForm_Initialize() DefaultSet For lDup = 1 To 3 Call NonDuplicatesList(lDup) Next lDup End Sub '<<========== This all worked until I tried to replace the select case above as noted (I am trying to shorted the code. There are quite a few of the rRng variables). Is there anyway to replace that select case? Please let me know, thanks. _Minitman On Mon, 26 May 2008 11:57:33 +0100, "Norman Jones" wrote: Hi Minitman, For one possible approach, try something like: '========== Public Sub Demo() Dim rRng As Range Dim i As Long For i = 1 To 3 Set rRng = myRange(i) MsgBox rRng.Address(0, 0) Next i End Sub '-------------- Public Function myRange(iArg As Long) As Range Dim Rng1 As Range Dim Rng2 As Range Dim Rng3 As Range Dim arr As Variant Dim rRng As Range Dim i As Long Set Rng1 = Range("A1:A10") Set Rng2 = Range("A20:A25") Set Rng3 = Range("A30:A40") arr = Array(Rng1, Rng2, Rng3) Set myRange = arr(iArg - 1) End Function '<<========== As an alternative to the array, you could replace the function with the following version: '========== Public Function myRange(iArg As Long) As Range Dim Rng1 As Range Dim Rng2 As Range Dim Rng3 As Range Dim myColl As Collection Dim rRng As Range Dim i As Long Set Rng1 = Range("A1:A10") Set Rng2 = Range("A20:A25") Set Rng3 = Range("A30:A40") Set myColl = New Collection myColl.Add Rng1, Rng1.Address myColl.Add Rng2, Rng2.Address myColl.Add Rng3, Rng3.Address Set myRange = myColl(iArg) End Function '<<========== --- Regards. Norman "Minitman" wrote in message ... Greeting, I am confused over how to make a variable to be variable. I am running Office 2003 on an XP Pro machine I have a set of 21 variables called Rng1, Rng2, ... Rng21. these variable are equal to named ranges on a sheet. So far so good. I am trying to eliminate the use of select case in this procedure. It appears that I have the syntax wrong. Here is one of the case statements: Sub DoThis(iArg as Integer) Select Case iArg ... Case 6 Set rRng = Rng6 Set cmBox = TB6 ... I tried: Set rRng = Range("Rng" & iArg) And got the following error on this line: Run-time error '1004': Method 'Range' of object '_Global' failed. The Rng's are dimmed as Range. Anyone have any idea what I did wring? Or on how to fix it? Any help is appreciated. -Minitman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Syntax
Hi Minitman,
============ [...] I am having a hard time understanding what the purpose of the array (arr) is? [...] ============ In this instance it is a container for each of the range varables. How does it load the RowSource of the ComboBoxes? It doesn't;the code does not use the ComboBox's RowSource property. Each ComboBox is loaded from the NoDupes Collection, using the ComboBox's AddItem property, with the instruction lines ============ [...] For Each item In NoDupes cmBox.AddItem item Next item [...] ============ I forgot to mention that the named ranges are dynamic and not related to each other. This is why I am wondering if an array is even feasible? I am asking because I don't understand arrays or how they work and have not worked with any arrays as yet. If I understand what is going on (and I don't think I do), it seems like the array (arr) has all of the entries from all of named ranges. I don't see anyway for the code to limit which column in the array is being added to which ComboBox's RowSource. Could you please explain how this works? I really need to know. ============= ============= --- Regards. Norman "Norman Jones" wrote in message ... Hi Minitman, I am not sure a function is what I need. The function was intended to sdemonstrate the concept. The following works for me: '========== Option Explicit Dim lDup As Long Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, rng As Range Dim arr As Variant Dim ListRange As Range Dim vSwap1 As Variant, vSwap2 As Variant, item As Variant Private Sub Defaultset() Dim wks As Worksheet On Error Resume Next Set wks = ThisWorkbook.Sheets("DataSheet") Set Rng1 = wks.Range("One") Set Rng2 = wks.Range("Two") Set Rng3 = wks.Range("Three") On Error GoTo 0 arr = VBA.Array(Rng1, Rng2, Rng3) End Sub Sub NonDuplicatesList(lPass As Long) Dim rRng As Range Dim cmBox As ComboBox Dim i As Integer Dim l As Long, j As Long, lRow As Long Dim iItem As Integer, iTBIndex As Integer Dim NoDupes As Collection Set NoDupes = New Collection Set rRng = arr(lPass - 1) Set cmBox = Me.Controls("TB" & lPass) '\\ Load the NoDupes Collection On Error Resume Next For lRow = 1 To rRng.Rows.Count With rRng(lRow) If Not .Value = vbNullString Then NoDupes.Add .Value, CStr(.Value) End If End With Next lRow On Error GoTo 0 '\\ Sort the collection (optional) j = 1 l = 1 For l = 1 To NoDupes.Count - 1 For j = l + 1 To NoDupes.Count If NoDupes(l) NoDupes(j) Then vSwap1 = NoDupes(l) vSwap2 = NoDupes(j) NoDupes.Add vSwap1, befo=j NoDupes.Add vSwap2, befo=l NoDupes.Remove l + 1 NoDupes.Remove j + 1 End If Next j Next l l = 1 j = 1 For Each item In NoDupes cmBox.AddItem item Next item i = 1 For i = 1 To NoDupes.Count NoDupes.Remove 1 'Removes 1st item every cycle until empty Next i i = 1 ' clear memory Set NoDupes = Nothing End Sub Private Sub UserForm_Initialize() Call Defaultset For lDup = 1 To 3 Call NonDuplicatesList(lDup) Next lDup End Sub '<<========== Incidentally, I found the naming of the ComboBoxes as TB1, TB2 ... TBn, confusing! :-) --- Regards. Norman "Minitman" wrote in message ... Hey Norman, Thanks for the reply. I am not sure a function is what I need. Here is my code for this procedure (shortened to 3 items): '========== Option Explicit Dim lDup As Long Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, rng As Range, Dim ListRange As Range Dim vSwap1 As Variant, vSwap2 As Variant, item As Variant __________________________________________________ _________ Sub DefaultSet() On Error Resume Next Set wks = ThisWorkbook.Sheets("DataSheet") Set Rng1 = wks.Range("NamedRange1 ") Set Rng2 = wks.Range("NamedRange2") Set Rng3 = wks.Range("NamedRange3") On Error GoTo 0 End Sub __________________________________________________ _________ Sub NonDuplicatesList(lPass As Long) Dim rRng As Range Dim cmBox As ComboBox Dim i As Integer Dim l As Long, j As Long, lRow As Long Dim iItem As Integer, iTBIndex As Integer Dim NoDupes As Collection Set NoDupes = New Collection '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''' 'This is the code I tried to replace the following code with ' Set rRng = Range("Rng" & lPass) ' Set cmBox = Me.Controls("TB" & lPass) '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''' Select Case lPass Case 1 Set rRng = Rng1 Set cmBox = TB1 Case 2 Set rRng = Rng2 Set cmBox = TB2 Case 3 Set rRng = Rng3 Set cmBox = TB3 End Select '\\ Load the NoDupes Collection On Error Resume Next For lRow = 1 To rRng.Rows.Count With rRng(lRow) If Not .Value = vbNullString Then NoDupes.Add .Value, CStr(.Value) End If End With Next lRow On Error GoTo 0 '\\ Sort the collection (optional) j = 1 l = 1 For l = 1 To NoDupes.Count - 1 For j = l + 1 To NoDupes.Count If NoDupes(l) NoDupes(j) Then vSwap1 = NoDupes(l) vSwap2 = NoDupes(j) NoDupes.Add vSwap1, befo=j NoDupes.Add vSwap2, befo=l NoDupes.Remove l + 1 NoDupes.Remove j + 1 End If Next j Next l l = 1 j = 1 For Each item In NoDupes cmBox.AddItem item Next item i = 1 For i = 1 To NoDupes.Count NoDupes.Remove 1 'Removes 1st item every cycle until empty Next i i = 1 ' clear memory Set NoDupes = Nothing End Sub __________________________________________________ _________ Private Sub UserForm_Initialize() DefaultSet For lDup = 1 To 3 Call NonDuplicatesList(lDup) Next lDup End Sub '<<========== This all worked until I tried to replace the select case above as noted (I am trying to shorted the code. There are quite a few of the rRng variables). Is there anyway to replace that select case? Please let me know, thanks. _Minitman On Mon, 26 May 2008 11:57:33 +0100, "Norman Jones" wrote: Hi Minitman, For one possible approach, try something like: '========== Public Sub Demo() Dim rRng As Range Dim i As Long For i = 1 To 3 Set rRng = myRange(i) MsgBox rRng.Address(0, 0) Next i End Sub '-------------- Public Function myRange(iArg As Long) As Range Dim Rng1 As Range Dim Rng2 As Range Dim Rng3 As Range Dim arr As Variant Dim rRng As Range Dim i As Long Set Rng1 = Range("A1:A10") Set Rng2 = Range("A20:A25") Set Rng3 = Range("A30:A40") arr = Array(Rng1, Rng2, Rng3) Set myRange = arr(iArg - 1) End Function '<<========== As an alternative to the array, you could replace the function with the following version: '========== Public Function myRange(iArg As Long) As Range Dim Rng1 As Range Dim Rng2 As Range Dim Rng3 As Range Dim myColl As Collection Dim rRng As Range Dim i As Long Set Rng1 = Range("A1:A10") Set Rng2 = Range("A20:A25") Set Rng3 = Range("A30:A40") Set myColl = New Collection myColl.Add Rng1, Rng1.Address myColl.Add Rng2, Rng2.Address myColl.Add Rng3, Rng3.Address Set myRange = myColl(iArg) End Function '<<========== --- Regards. Norman "Minitman" wrote in message ... Greeting, I am confused over how to make a variable to be variable. I am running Office 2003 on an XP Pro machine I have a set of 21 variables called Rng1, Rng2, ... Rng21. these variable are equal to named ranges on a sheet. So far so good. I am trying to eliminate the use of select case in this procedure. It appears that I have the syntax wrong. Here is one of the case statements: Sub DoThis(iArg as Integer) Select Case iArg ... Case 6 Set rRng = Rng6 Set cmBox = TB6 ... I tried: Set rRng = Range("Rng" & iArg) And got the following error on this line: Run-time error '1004': Method 'Range' of object '_Global' failed. The Rng's are dimmed as Range. Anyone have any idea what I did wring? Or on how to fix it? Any help is appreciated. -Minitman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Syntax
Thanks Norman. I'm beginning to understand. At least enough to
modify as needed. :^) -Minitman On Mon, 26 May 2008 15:37:48 +0100, "Norman Jones" wrote: Hi Minitman, ============ [...] I am having a hard time understanding what the purpose of the array (arr) is? [...] ============ In this instance it is a container for each of the range varables. How does it load the RowSource of the ComboBoxes? It doesn't;the code does not use the ComboBox's RowSource property. Each ComboBox is loaded from the NoDupes Collection, using the ComboBox's AddItem property, with the instruction lines ============ [...] For Each item In NoDupes cmBox.AddItem item Next item [...] ============ I forgot to mention that the named ranges are dynamic and not related to each other. This is why I am wondering if an array is even feasible? I am asking because I don't understand arrays or how they work and have not worked with any arrays as yet. If I understand what is going on (and I don't think I do), it seems like the array (arr) has all of the entries from all of named ranges. I don't see anyway for the code to limit which column in the array is being added to which ComboBox's RowSource. Could you please explain how this works? I really need to know. ============= ============= --- Regards. Norman "Norman Jones" wrote in message ... Hi Minitman, I am not sure a function is what I need. The function was intended to sdemonstrate the concept. The following works for me: '========== Option Explicit Dim lDup As Long Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, rng As Range Dim arr As Variant Dim ListRange As Range Dim vSwap1 As Variant, vSwap2 As Variant, item As Variant Private Sub Defaultset() Dim wks As Worksheet On Error Resume Next Set wks = ThisWorkbook.Sheets("DataSheet") Set Rng1 = wks.Range("One") Set Rng2 = wks.Range("Two") Set Rng3 = wks.Range("Three") On Error GoTo 0 arr = VBA.Array(Rng1, Rng2, Rng3) End Sub Sub NonDuplicatesList(lPass As Long) Dim rRng As Range Dim cmBox As ComboBox Dim i As Integer Dim l As Long, j As Long, lRow As Long Dim iItem As Integer, iTBIndex As Integer Dim NoDupes As Collection Set NoDupes = New Collection Set rRng = arr(lPass - 1) Set cmBox = Me.Controls("TB" & lPass) '\\ Load the NoDupes Collection On Error Resume Next For lRow = 1 To rRng.Rows.Count With rRng(lRow) If Not .Value = vbNullString Then NoDupes.Add .Value, CStr(.Value) End If End With Next lRow On Error GoTo 0 '\\ Sort the collection (optional) j = 1 l = 1 For l = 1 To NoDupes.Count - 1 For j = l + 1 To NoDupes.Count If NoDupes(l) NoDupes(j) Then vSwap1 = NoDupes(l) vSwap2 = NoDupes(j) NoDupes.Add vSwap1, befo=j NoDupes.Add vSwap2, befo=l NoDupes.Remove l + 1 NoDupes.Remove j + 1 End If Next j Next l l = 1 j = 1 For Each item In NoDupes cmBox.AddItem item Next item i = 1 For i = 1 To NoDupes.Count NoDupes.Remove 1 'Removes 1st item every cycle until empty Next i i = 1 ' clear memory Set NoDupes = Nothing End Sub Private Sub UserForm_Initialize() Call Defaultset For lDup = 1 To 3 Call NonDuplicatesList(lDup) Next lDup End Sub '<<========== Incidentally, I found the naming of the ComboBoxes as TB1, TB2 ... TBn, confusing! :-) --- Regards. Norman "Minitman" wrote in message ... Hey Norman, Thanks for the reply. I am not sure a function is what I need. Here is my code for this procedure (shortened to 3 items): '========== Option Explicit Dim lDup As Long Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, rng As Range, Dim ListRange As Range Dim vSwap1 As Variant, vSwap2 As Variant, item As Variant __________________________________________________ _________ Sub DefaultSet() On Error Resume Next Set wks = ThisWorkbook.Sheets("DataSheet") Set Rng1 = wks.Range("NamedRange1 ") Set Rng2 = wks.Range("NamedRange2") Set Rng3 = wks.Range("NamedRange3") On Error GoTo 0 End Sub __________________________________________________ _________ Sub NonDuplicatesList(lPass As Long) Dim rRng As Range Dim cmBox As ComboBox Dim i As Integer Dim l As Long, j As Long, lRow As Long Dim iItem As Integer, iTBIndex As Integer Dim NoDupes As Collection Set NoDupes = New Collection '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''' 'This is the code I tried to replace the following code with ' Set rRng = Range("Rng" & lPass) ' Set cmBox = Me.Controls("TB" & lPass) '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''' Select Case lPass Case 1 Set rRng = Rng1 Set cmBox = TB1 Case 2 Set rRng = Rng2 Set cmBox = TB2 Case 3 Set rRng = Rng3 Set cmBox = TB3 End Select '\\ Load the NoDupes Collection On Error Resume Next For lRow = 1 To rRng.Rows.Count With rRng(lRow) If Not .Value = vbNullString Then NoDupes.Add .Value, CStr(.Value) End If End With Next lRow On Error GoTo 0 '\\ Sort the collection (optional) j = 1 l = 1 For l = 1 To NoDupes.Count - 1 For j = l + 1 To NoDupes.Count If NoDupes(l) NoDupes(j) Then vSwap1 = NoDupes(l) vSwap2 = NoDupes(j) NoDupes.Add vSwap1, befo=j NoDupes.Add vSwap2, befo=l NoDupes.Remove l + 1 NoDupes.Remove j + 1 End If Next j Next l l = 1 j = 1 For Each item In NoDupes cmBox.AddItem item Next item i = 1 For i = 1 To NoDupes.Count NoDupes.Remove 1 'Removes 1st item every cycle until empty Next i i = 1 ' clear memory Set NoDupes = Nothing End Sub __________________________________________________ _________ Private Sub UserForm_Initialize() DefaultSet For lDup = 1 To 3 Call NonDuplicatesList(lDup) Next lDup End Sub '<<========== This all worked until I tried to replace the select case above as noted (I am trying to shorted the code. There are quite a few of the rRng variables). Is there anyway to replace that select case? Please let me know, thanks. _Minitman On Mon, 26 May 2008 11:57:33 +0100, "Norman Jones" wrote: Hi Minitman, For one possible approach, try something like: '========== Public Sub Demo() Dim rRng As Range Dim i As Long For i = 1 To 3 Set rRng = myRange(i) MsgBox rRng.Address(0, 0) Next i End Sub '-------------- Public Function myRange(iArg As Long) As Range Dim Rng1 As Range Dim Rng2 As Range Dim Rng3 As Range Dim arr As Variant Dim rRng As Range Dim i As Long Set Rng1 = Range("A1:A10") Set Rng2 = Range("A20:A25") Set Rng3 = Range("A30:A40") arr = Array(Rng1, Rng2, Rng3) Set myRange = arr(iArg - 1) End Function '<<========== As an alternative to the array, you could replace the function with the following version: '========== Public Function myRange(iArg As Long) As Range Dim Rng1 As Range Dim Rng2 As Range Dim Rng3 As Range Dim myColl As Collection Dim rRng As Range Dim i As Long Set Rng1 = Range("A1:A10") Set Rng2 = Range("A20:A25") Set Rng3 = Range("A30:A40") Set myColl = New Collection myColl.Add Rng1, Rng1.Address myColl.Add Rng2, Rng2.Address myColl.Add Rng3, Rng3.Address Set myRange = myColl(iArg) End Function '<<========== --- Regards. Norman "Minitman" wrote in message m... Greeting, I am confused over how to make a variable to be variable. I am running Office 2003 on an XP Pro machine I have a set of 21 variables called Rng1, Rng2, ... Rng21. these variable are equal to named ranges on a sheet. So far so good. I am trying to eliminate the use of select case in this procedure. It appears that I have the syntax wrong. Here is one of the case statements: Sub DoThis(iArg as Integer) Select Case iArg ... Case 6 Set rRng = Rng6 Set cmBox = TB6 ... I tried: Set rRng = Range("Rng" & iArg) And got the following error on this line: Run-time error '1004': Method 'Range' of object '_Global' failed. The Rng's are dimmed as Range. Anyone have any idea what I did wring? Or on how to fix it? Any help is appreciated. -Minitman |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Syntax
Hi Minitman,
My apologies, a rejected draft response was inadvertently sent! ============ [...] I am having a hard time understanding what the purpose of the array (arr) is? [...] ============ In this instance it is a container for each of the range varables. How does it load the RowSource of the ComboBoxes? It doesn't;the code does not use the ComboBox's RowSource property. Each ComboBox is loaded from the NoDupes Collection, using the ComboBox's AddItem property, with the instruction lines For Each item In NoDupes cmBox.AddItem item Next item ============ I forgot to mention that the named ranges are dynamic and not related to each other. This is why I am wondering if an array is even feasible? I am asking because I don't understand arrays or how they work and have not worked with any arrays as yet. ============ For each value of the lPass variable, the code selects the next range variable in the array arr and assigns it to your range variable rRng;. Thus, the variable rRng is sequentially set to the each of the ranges corresponding to the range variables Rng1, Rng2 ... RngN, held in the array, At each increment of the lPass variable, each value in the range assigned to the rRng variable is passed to the NoDupes collection to filter out any duplicate values; the unique values held in the collection are then loaded into the requisite ComboBox (TB1, TB2 ... TbB), using the ComboBox's AddItem property. The relative sizes of the ranges are unimportant; the only relevantly important consideration is that the sequuence of ranges be the same as that of the ComboBoxes. i.e, that the sequence of Rng1, Rng2 ... RngN and TB1, TB2... TBN correspond. Have you tried the code? If you are having difficulty, I could send you my ugly, but functional, test workbook, in response to an email: norman_jones@NOSPAMbtconnectDOTcom (Delete "NOSPAM" and replace "DOT" with a full stop [period] ) --- Regards. Norman |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Syntax
Hi Minitman,
Thanks Norman. I'm beginning to understand. At least enough to modify as needed. :^) If you are able to understand the garbled, unintended initial response, you are a better man than I! See my subsequent post for a more comprehensive explanation. --- Regards. Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
syntax when using a variable in a formula | Excel Programming | |||
syntax for variable | Excel Programming | |||
SaveAs syntax when using a variable | Excel Programming | |||
Syntax for variable search | Excel Programming | |||
Syntax for variable search | Excel Programming |