How to use a public function in a similar way as worksheetfunction in VBA?

[etropal]

I’m writing a very large VBA macro to analyze data from multiple worksheets. All my functions are used in the following form (example):
ReDim TotVAKWNAArray(1 To 5)
For bCount = 1 To 5
TotVAKWNAArray(bCount) = Application.WorksheetFunction.CountIfs(Range("D:D"), VAKW, Range("AW:AW"), "1", Range("N:N"), ToerReg, Range("K:K"), Prov, Range("W:W"), Gem)
If ActiveSheet.Index = 5 Then
Worksheets(1).Select
Else
ActiveSheet.Next.Select
End If
Next bCount

Now, at the end of a laborious process, I need to use a function in the macro, that is not a standard Excel worksheet function (counting conditional unique values). I managed to find a public function that does the trick perfectly (here called UniqueCountv4). If I try to use this like in the example things go wrong. Is there anyone who knows how to fix this problem? I would be REALLY grateful I anyone could provide me with a solution.

ReDim TotVAKLArray(1 To 5)
For bCount = 1 To 5
TotVAKLArray(bCount) = Application.WorksheetFunction.UniqueCountv4(Range("B:B"), Range("D:D"), VAKL) ', Range("N:N"), ToerReg, Range("K:K"), Prov, Range("W:W"), Gem)
If ActiveSheet.Index = 5 Then
Worksheets(1).Select
Else
ActiveSheet.Next.Select
End If
Next bCount

 

[Jaafar Tribak]

If the UniqueCountv4 Functio is reacheable from your code then you should be able to call it like this :

TotVAKLArray(bCount) = [COLOR=#ff0000]UniqueCountv4[/COLOR](Range("B:B"), Range("D:D"), VAKL)

You can't use the Application.WorksheetFunction qualifiyer because it is not part of the native worksheet functions.

 

[etropal]

Many thanks Jaafar!


Even though I had already tried your solution earlier my results always turned out wrong, so I went looking for answers in all the wrong places. Thanks to your kind reply I located the real problem; namely the fact that my variable VAKL was defined as a String instead of Variant which caused the problem with the public function...