How to efficiently handle sets?

[troelsi]

Hi
I have to sets.
Set Pr is the universal set.
Set Pf i a subset of pr.

In case my data isn't sorted properly I would like to examine wheter the variable in question is part of PF.

For p = 1 To UBound(pr)
For i = 1 To UBound(pf)
If pf(i) = pr(p) Then
PisaPartofPF=1
end if
next i
next p


The above method seems a bit slow and heavy is there a better way to handle sets?

[Bob Phillips]

For p = 1 To UBound(pr)
If Not IsError(Application.Match(pr(p), pf, 0)) Then
PisaPartofPF = 1
Exit For
End If
Next p

[troelsi]

Great it works just perfect.

However I can't find any documentation on the match property (when I highlight it and press F1). Could you please explain to me how it works including the arguments - ie. (pr(p), pf, 0) from the above example.

Thanks

[Bob Phillips]

It is using the Excel MATCH worksheet function in VBA. Look it up in help from Excel, not VBA.

[troelsi]

Great thanks!

I'm making a rather large code here that has to handle a lot of data, and I've heard that using functions from Excel such as this match function slows your code down significantly - is that true?

[Bob Phillips]

Well you tell me, which is quicker, your code or mine?

[troelsi]

As of now they are just as fast.
But it may change as my code continues to grow.