#VALUE ERROR from my VBA function

Question

So I wrote a simple function in VBA and I want to use it in my excel workbook. I wrote the following code:

Option Explicit
Public Function KOLICINA(fiksnacena As Long, ceni() As Long, nedela() As Long) As Long
    Dim brojac As Integer
    For brojac = 1 To UBound(nedela)
        If Not ((IsEmpty(nedela(brojac) Or nedela(brojac) = 0) And ceni(brojac) <>  fiksnacena)) Then KOLICINA = nedela(brojac)
    Next brojac
End Function

When I try to use it in a worksheet cell (using =KOLICINA(18;G22:G26;H22:H26)) , I get the #VALUE error. I don't understand why. The function should go through nedela Array and if it finds a Non empty or different value than 0 AND if the matching cell in the ceni Array is different from the number fiksnacena, it should return the value of the cell in nedela.

Maybe change ceni() and nedela() to Range instead of Long? — TheGuyThatDoesn'tKnowMuch
– TheGuyThatDoesn'tKnowMuch, Commented Mar 5, 2016 at 12:20
Then KOLICINA = nedela(brojac) could perhaps be replaced by Then followed by KOLICINA = nedela(brojac) followed by Exit Function followed by End If. Once you find the return value, why continue the loop (unless you want the last match)? — John Coleman
– John Coleman, Commented Mar 5, 2016 at 12:23
Apparently you meant If Not (IsEmpty(nedela(brojac)) Or nedela(brojac) = 0) And ceni(brojac) <> fiksnacena Then. — GSerg
– GSerg, Commented Mar 5, 2016 at 12:28
@JohnColeman Yes it could, I tried it but still the #VALUE error is shown... — Jane Panovski
– Jane Panovski, Commented Mar 5, 2016 at 12:29
There is no such thing as an Empty long, so I don't see the point of the isEmpty test on the elements of an array of longs — John Coleman
– John Coleman, Commented Mar 5, 2016 at 12:38

score 3 · Accepted Answer · 2016-03-05 12:54:48Z

3

You cannot simply pass a cell range reference into a UDF and have it interpreted as a single dimensioned array of longs.

Public Function KOLICINA(fiksnacena As Long, ceni As Range, nedela As Range) As Long
    Dim brojac As Long, vCeni As Variant, vNedela As Variant
    vCeni = ceni.Value2
    vNedela = nedela.Value2
    For brojac = LBound(vNedela, 1) To UBound(vNedela, 1)
        If Not ((IsEmpty(vNedela(brojac, 1) Or vNedela(brojac, 1) = 0) And vCeni(brojac, 1) <> fiksnacena)) Then
            KOLICINA = vNedela(brojac, 1)
            Exit For
        End If
    Next brojac
End Function

When you dump values from a range reference into an array, you always end up with a two dimensioned array; in your example it is 1 to 5, 1 to 1.

To further illustrate this point, your original UDF code would work if you pulled the values from the ranges after transposing them and finish off the UDF with CSE so that the values are processed as an array.

=KOLICINA(18, VALUE(TRANSPOSE(G22:G26)), VALUE(TRANSPOSE(H22:H26)))

Finalize with [ctrl]+[shift]+[enter].

edited Mar 5, 2016 at 12:54

answered Mar 5, 2016 at 12:40

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

Jane Panovski Over a year ago

Thank you all, @Jeeped especially, your answer solved my problem. beginner's mistake

Collectives™ on Stack Overflow

#VALUE ERROR from my VBA function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related