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

[etropal]

Hi,

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

Who can help me?

Steven Laporte

Moderators Note:
Hello Steven & Welcome to the Board,
Please follow Forum Rule #3 about the use of code tags. Added for you this time, but please use them in the future…Thanks.

[Andrew-R]

Your user-defined function is not part of the Application.WorksheetFunction object, so it can't be called that way, but it should be fine to just do this:

TotVAKLArray(bCount) = UniqueCountv4(Range("B:B"), Range("D:D"), VAKL) ', Range("N:N"), ToerReg, Range("K:K"), Prov, Range("W:W"), Gem)