CODEWITHSUNDEEP
vbaexcel
Jane Panovski
Jane Panovski

Reputation: 13

#VALUE ERROR from my VBA function

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.

Upvotes: 0

Views: 727

Answers (1)

user4039065
user4039065

Reputation:

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].

Upvotes: 3

Related Questions