Use a set / array of values in function

[Cosmos75]

Is it possible to create a user defined function that uses a set/array of values as a variable? If YES, how?

 

[scottn]

vb definitely supports arrays. this link should help:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/html/vbconarrays.asp

 

[pono1]

Cosmos,

Not to downplay arrays, but here's another option. Winging it here but you should, I hope, get the idea, assuming, that is, I get what you're asking.

Private Function MyFunction(ByVal Num1 As Long, _
              		    ByVal Num2 As Long) _
As Collection
'Given two numbers, return their product and sum to
'the calling procedure.

Dim MyCollection as New Collection
MyCollection.Add num1*num2
MyCollection.Add num1+num2

MyFunction = MyCollection

End Function

Call the function like this...

Dim MyProductSum as new collection
Dim v

MyProductSum = MyFunction(9,7)
For each v in MyProductSum
  debug.print "Product = " v
  debug.print "Sum = " v
Next v

 

[Cosmos75]

scottn and pono1,

Thank you both for your replies.

What I want to create is a function that does something with numbers within a given range (Hi/Low number used in function but exclude certain numbers between the high and Hi/Low numbers). I don't know how many numbers are to be excluded.

Does that make more sense or is that more confusing?

 

[ChrisO]

G’day Cosmos75

“Does that make more sense or is that more confusing?”… More confusing!

Simple way to pass multiple arguments: -

Option Explicit
Option Compare Text

Sub RunTest()

    MsgBox TotalVariableArray
    MsgBox TotalVariableArray(1, 2, 3, 4, 0.0005)
    MsgBox TotalVariableArray(1, 2, 3, 4, 5, 0.0005)

End Sub


Public Function TotalVariableArray(ParamArray vntArgList() As Variant) As Variant

    Dim lngElement    As Long
    Dim vntArrayTotal As Variant

    For lngElement = LBound(vntArgList) To UBound(vntArgList)
        vntArrayTotal = vntArrayTotal + vntArgList(lngElement)
    Next lngElement
                                 
    TotalVariableArray = vntArrayTotal
                                 
End Function

Another way, but not so simple is that you can pass and return multiple numbers of arguments and of different data types and re-dimension the array with Preserve.

Please see attached A97 demo. (Obviously written for another purpose.)

Somewhere between those two extremes should lay the answer.

More info would be required to nail this Parrot to its perch.

Regards,
Chris.

 

[Cosmos75]

Thanks!

ChrisO,

Wonderful sample! That did the trick!
THANK YOU!

 

[ChrisO]

G’day Cosmos75

You’re welcome.

Just curious and maybe to ask for a little help on this if you don’t mind.

The curious part…
Which version did you go for, the simple or slightly more complex?

The help part…
If you went for the simple part then no problem but if you needed the more complex version then may I ask a question?
Were the comments, the ‘green stuff’, understandable?

Reason I ask…
I am notoriously bad at commenting my own code and that’s why I usually keep the green stuff to a minimum.
The green stuff is how we think it works ‘at the time’ but the black stuff is how it works ‘always’.

User defined data types seem to have become a thing of the past. This seems a pity because they still fill a need between temporary tables, which bloat, and classes that require too much effort and coding.

So if you, or anyone else for that matter, would like to suggest ways to improve the green stuff (or black stuff) then fire away and I’ll try to do my best to clean it.

Maybe if we get a joint effort on cleaning it, Mile-O can bang it into the FAQ for future reference.

Just a thought…

Regards,
Chris.

 

[Cosmos75]

The curious part…
Which version did you go for, the simple or slightly more complex?

I used the "simple " version as it did not require that I know how many elements are going to be in the array.

Were the comments, the ‘green stuff’, understandable?

Well, Yes and No. The reason I say that is that I am not very familiar with arrays so the parts I did not quite understand are due to my lack of knowledge and not undecipherable commenting on your part.

User defined data types seem to have become a thing of the past. This seems a pity because they still fill a need between temporary tables, which bloat, and classes that require too much effort and coding.

I learned about UDTs from you and Mile-O-Phile in this thread. Excellent explanation on both your parts. I have to admit that I use it when I can! Very cool!

I wonder if you can still use UDTs in VB.NET?
Haven't yet tried anything yet with VB.NET. It always boils down to finding the time and energy to devote myself to learning!

So if you, or anyone else for that matter, would like to suggest ways to improve the green stuff (or black stuff) then fire away and I’ll try to do my best to clean it.

Maybe if we get a joint effort on cleaning it, Mile-O can bang it into the FAQ for future reference.

I do intend to understand it, if and when I can find time. Hopefully this weekend! At that point, I am willing to help out.

But I must admit that my knowledge is sorely lacking and I am probably not the best person for the job.

As for creating an F.A.Q. from this, I wholeheartedly support it as it will certainly come in handy to be able to refer people to a F.A.Q. for a similiar question!