Solved VBA to Create Array or Collection with Unique Values

Microsoft Excel 2010 - complete product...
July 30, 2018 at 06:08:11
Specs: Windows 7 x64, 2,4 GHz / 4 GB
This is related to https://www.computing.net/answers/o...
When trying to clear Best Answer in the topic above, the message said to only clear if there is another/a different Best Answer. But I am asking a new question here, so I started this new topic.

Using the Best Answer in the topic above I can extract unique values from a range variable. Working with it I realized I would need to remove certain values from the array based on an offset value. As far as I've read, removing values from array based on condition is troublesome and one should use a collection instead of an array.

Could you please guide me in modifying the Best Answer in the mentioned topic to use a collection instead of an array, to be able to also put a condition when creating the unique range?
Or could I put the condition when creating the array?

Assuming I have the following:
- myRange is a range defined using InputBox
- rngElement is each element of myRange

I would like to make a unique range of rngElements (only in VBA, not having to paste values in cells), where they all meet a criteria: rngElement.Offset(, 3).Value <> 0


See More: VBA to Create Array or Collection with Unique Values

Reply ↓  Report •

#1
July 30, 2018 at 13:36:36
I could be way off base with this, but I'm going to toss it out there anyway...

I need to say that I am "uncomfortable" with some of the terminology that you use. Maybe it's just me but your use of the terms "range" and "element" makes your question confusing, at least to me. (A similar same thing happened in the other thread, but I left it alone there.)

When I hear/read the term "range" I think of a range of cells, e.g. A1:B45 or even even a single cell. I guess that's because that is how Microsoft defines the Range object:

"Use Range ( arg ), where arg names the range, to return a Range object that represents a single cell or a range of cells."

So when you say "I would like to make a unique range of rngElements (only in VBA, not having to paste values in cells)" it's confusing. You say "range", I think "cells" then you say "not cells". Confusing.

If I understand your goal, you don't want to "make a range", you want to create a "group" or "set" or (dare I say it? ) an "array" containing unique items. While the input might come from a Range, you have specifically stated that you don't want the output to go back into a "range" (as defined by MS). So, I don't think that you should be using the term "range" in this situation.

You also said:

" myRange is a range defined using InputBox
- rngElement is each element of myRange"

When I hear/read the term "element" I think of an array, as in:

"An array is a group of elements. In Excel VBA, you can refer to a specific element of an array by using the array name and the index number."

If myRange is really a Range (i.e. a group of cells) then rngElement is nothing more than a cell within that Range. Calling each cell an "element" (and using rngElement) is confusing, again, at least to me. If we absolutely needed a valid term to refer to each individual cell within a range (other than just "cell") maybe Item would be better. (You can look up the Item method if you wish to see if that suggestion makes sense.

OK, with off my chest, let's get back to your question.

I don't know enough about creating Collections to be of much help in that arena. Instead, let's go with your "Or could I put the condition when creating the array" thought.

How are you building the myArray mentioned here:

For counter = 1 To UBound(myArray, 1)
    myDict.Item(myArray(counter, 1)) = myArray(counter, 1)
Next

I assume (dangerous) that you are looping myRange. Why not just check that offset for each cell within myRange and skip the "Add to myArray" instruction when appropriate? A simple If-Then.


message edited by DerbyDad03


Reply ↓  Report •

#2
July 30, 2018 at 23:44:57
Dammit, I try to be more clear with and end up being more confusing. Sorry about that. I was set up on all the terms from range to array and I didn't think that would confuse anyone. But then again terms have definitions and by definition you are right - it is confusing.

I'll try to be clearer and mind my words from now on.

I don't quite understand what you meant there and how to make that into code, but I found a way creating a new range (newRange) from the initial range (myRange) using conditions and Union, before doing the array part which remained the same except I used newRange.

Dim newRange As Range
Dim c As Range

For Each c In myRange
    If c.Offset(, 3).Value2 <> "" And c.Offset(, 3).Value2 <> "fara Dop" Then
    If Not newRange Is Nothing Then
            Set newRange = Union(newRange, c)
        Else
            Set newRange = c
    End If
    End If
Next

Now newRange contains only values that meet the criteria and I further use newRange in the array part that follows.

By the way, that array snippet that follows (from the other topic) won't display the value in tmpBody if I only selected 1 cell as myRange to begin with.

When doing the following loop myDict.Items returns 0 items.

    For counter = 1 To UBound(myArray, 1)
        myDict.Item(myArray(counter, 1)) = myArray(counter, 1)
    Next

        myArray = myDict.Items           'myDict has no items; Count = 0 in Locals window

I don't know what to do to it so I don't have to use an If-Then, like this:

    For counter = 1 To UBound(myArray, 1)
        myDict.Item(myArray(counter, 1)) = myArray(counter, 1)
    Next

    If UBound(myArray) - LBound(myArray) + 1 = 0 And myCell.Offset(, 15).Value = "" Then
        tmpBody = tmpBody & "- inv no. " & newRange.Value & vbNewLine & vbNewLine
    Else
        myArray = myDict.Items
        For nxtItem = 0 To UBound(myArray)
                tmpBody = tmpBody & "- inv no. " & myArray(nxtItem) & vbNewLine & vbNewLine
        Next
End if

message edited by Mrrrr


Reply ↓  Report •

#3
July 31, 2018 at 04:12:17
✔ Best Answer
Unless you include Option Base 1 in your code, VBA arrays start at 0, not 1. Try "For 0 to UBound" instead.

However, when passing a Range to an Array directly (i.e. without looping cell by cell) the Array starts at 1 as explained at the page mentioned below.

This page has an extensive write up on passing Ranges to Arrays. I'm not sure that it will help in this situation because of your "conditions" but it's worth the read.

http://www.cpearson.com/excel/Array...

Unfortunately for us all, the author, Chip Pearson, recently passed away.

This link is worth saving:

http://www.cpearson.com/Excel/Topic...

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
July 31, 2018 at 06:59:08
I will check it out, thank you.

Reply ↓  Report •

Ask Question