Counting rows in VBA excel -

i'm designing function in vba of form myfunction(x,y,z) z table, , x can take values of column headings. part of function need find number of rows in z.

i'm having problems this, everywhere suggests using length = z.rows.count, when try , output value (as in, set myfunction = length), produces value error. however, when output myfunction = a doesn't directly use length (it form part of if statement once working), function works fine. code below:

public function myfunction(x string, y double, z range) double      dim upper_threshold double     dim lower_threshold double     dim double      dim rates variant      dim u byte     dim l byte     dim r byte      dim length byte       = 0      u = 2     l = 1      rates = application.worksheetfunction.index(z, 1, 0)     r = application.worksheetfunction.match(x, rates, 0)      length = z.rows.count      upper_threshold = z(u, 1)      while y > upper_threshold          u = u + 1         l = l + 1          upper_threshold = z(u, 1)         lower_threshold = z(l, 1)          if y < upper_threshold             = + z(l, r) * (y - lower_threshold)         else             = + z(l, r) * (upper_threshold - lower_threshold)         end if      loop  myfunction =  end function 

to test out created function:

public function myrows(mytable range) double      myrows = mytable.rows.count  end function 

this 1 works fine on own, when try use within other function, still value error. i've tried declaring length every type can think of , doesn't seem help.

can see what's going on?

edit: i'm not making myself clear. function without 2 lines referring length works intended. however, need add bit of code increase functionality , involves calculating number of rows in table z. when add 2 lines shown here function continues work, since doesn't affect output. however, if set output show length, i.e. change penultimate line myfunction = length gives me value error. leaves me 2 options far can see: either else in program impacting on these 2 lines (some clashes of syntax or something), or i'm making mistake in assuming can output length that.

your problem with:

rates = application.worksheetfunction.index(z, 1, 0) 

index accepts single row or column, otherwise value error.