Variable Syntax

[Minitman]

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

 

[Norman Jones]

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
'<<==========

 

[Minitman]

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, before:=j
NoDupes.Add vSwap2, before:=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

 

[Norman Jones]

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, before:=j
NoDupes.Add vSwap2, before:=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!

 

[Minitman]

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

 

[Norman Jones]

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.


=============
=============

 

[Minitman]

Thanks Norman. I'm beginning to understand. At least enough to
modify as needed. :^)

-Minitman

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

Hi Minitman,


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, before:=j
NoDupes.Add vSwap2, before:=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!

 

[Norman Jones]

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] )

 

[Norman Jones]

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.