Excel Ranges 1

[RicksAtWork]

I have written a function that looks like this:

Function UniqueItemCollection(InputRange As Range) As Collection

'Bunch of code here

End Function

I have named a range Data_Countries

I want to pass this range to my function above..

So I wrote:

UniqueItemCollection(Range("Data_Countries"));

But this throws an error when run 'Object required'

What am I doing wrong?

 

[Zathras]

InputRange as you have declared it is in fact a range. However "Data_Countries" is a string and is therefore not an object.

See the contemporary thread thread707-1147839 for a similar situation that might illustrate the difference between a worksheet object and a worksheet name.

 

[RicksAtWork]

Strange.

I wrote these lines of code:

Range("Data_Country").Select

This selected the range as I expected.

The following didnt work:

Dim cl As Range
cl = Range("Data_Country")

Got the same error - Object required???

 

[Zathras]

Have you checked the help file?

"Data_Country" is a string.

Range("Data_Country") is a range.

To assign a value to a range, use the "Set" instruction:
[tt]
Set cl=Range("Data_Country")
[/tt]

 

[RicksAtWork]

Grrr - I hate VB syntax!!! I'm a C#/C++ developer by trade - fiinding VBA syntax very alien!!

 

[RicksAtWork]

Well - I've done what you say:

Set cl = Range("Data_Country")

Generic_Methods.UniqueItemCollection (cl)

But the second line doesnt work.... again an object required error


Why should this not work - its totally illogical???

 

[Zathras]

So.... what kind of object is Generic_Methods and what line of code did you use to assign an object to it?

(Look up Dim, Set and Free in the help file.)

No argument about the VBA syntax. But one does get used to it after a while.

 

[RicksAtWork]

Generic_Methods is a module which contains my function UniqueItemCollection

Currently this function is just:


Function UniqueItemCollection(InputRange As Range) As Collection

Dim cl As Range, cUnique As New Collection

Application.Volatile

On Error Resume Next

For Each cl In InputRange
If cl.Formula <> "" Then
cUnique.Add cl.Value, CStr(cl.Value)
End If
Next cl

UniqueItem = cUnique

On Error GoTo 0

End Function


Am I going mad?

 

[Zathras]

Quite possibily. The main issue appears to be one of scope. That concept should translate from C#/C++ so I don't know why you are struggling as much as you seem to be.

I'm not exactly sure what is happening, but it looks like cUnique is defined in your function as a local variable. Once the function ends, it is out of scope and the result UniqueItem is pointing to garbage.

If I were writing something like that, I would probably define the collection in the calling routine and then write the "function" as a sub, passing the collection to it. Then I would be more assured that the collection object would still be valid after returning from the lower level routine (be it sub or function).

 

[RicksAtWork]

It doesnt work when the function is converted to a sub with no code in it i.e.


sub UniqueItemCollection(InputRange As Range)

//absolutely no code whatsoever!


end sub

Calling this with:

Set cl = Range("Data_Country")

Generic_Methods.UniqueItemCollection (cl)
Doesnt work at all?

The code within the function/sub isnt whats causing the issue!!

 

[SkipVought]

PROBLEM:

    UniqueItem[red][b]Collection[/b][/red] = cUnique

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[vbap]

use:

Call Generic_Methods.UniqueItemCollection(cl)

or

Generic_Methods.UniqueItemCollection cl

 

[Zathras]

What exactly do you think
[tt]
Generic_Methods.UniqueItemCollection (cl)
[/tt]
is supposed to do?

Here is some working code to play with:

Option Explicit

Sub test()
Dim cUnique As New Collection
Dim i As Integer
Dim s As String

  ListFormulas Range("A1:B10"), cUnique
  For i = 1 To cUnique.Count
    s = s + vbNewLine + cUnique(i)
  Next i
  MsgBox s
End Sub

Sub ListFormulas(ARange As Range, ACollection As Collection)
Dim c As Range
  For Each c In ARange
    If c.HasFormula Then
      ACollection.Add (c.Formula)
    End If
  Next c
End Sub

Put a few formulas in A1:B10 and run the Test sub. Once you understand what it is doing, you should be able to adapt it to your needs.

 

[RicksAtWork]

This worked:

Generic_Methods.UniqueItemCollection Range("Data_Country")

Cheers!!!

 

[RicksAtWork]

Spoke far too soon!!


It would appear that you cant have this syntax!


countries As New Collection

countries = Generic_Methods.UniqueItemCollection Range("Data_Country")

What the???

 

[RicksAtWork]

This works:

Dim countries As New Collection

Set countries = UniqueItemCollection(Range("Data_Country"))

I know have my collection of unique countries!

 

[Zathras]

Stop putting the module name in front of the function call!

If the function is in a different module, declare it with the "Public" keyword (Not required, but it enhances the documentation):
[tt]
Public Function UniqueItemCollection( ARange as Range ) As Collection
[/tt]
Then use the "Set" instruction (I specified using it for ranges above, but it is needed to assign any object.):
[tt]
Set countries = UniqueItemCollection( Range("Data_Country") )
[/tt]

 

[RicksAtWork]

In VBA if I have a function that returns a variable, I do this via


functionname = variable

within the function?

 

[RicksAtWork]

Solved!

set functionname = variable

 

[RicksAtWork]

Thanks - I've rated you as a helpful poster...

Quickie

I'm automatically deleting a worksheet with

ws.Delete

A popup appears asking if I'm sure I want to do this..

I there anyway to stop the popup from appearing?