Excel 2010 VBA function returning an array

Question

I got a compile error for Excel 2010 VBA function implementation for a deposit computing. I want to return an array with interest rate and its value for the given deposit.

Function exe3djxInterestSelect(deposite)
  Dim V() As Variant
  ReDim V(1, 2)
  Select Case deposite
  Case Is < 0
    exe3djxInterestSelect = CVErr(xlErrNum)
  Case 0 To 1000
    V(1) = 0.055
    V(2) = deposite * 0.055
    exe3djxInterestSelect = V
  Case 1000 To 10000
    V(1) = 0.063
    V(2) = deposite * 0.063
    exe3djxInterestSelect = V
  Case 10000 To 100000
    V(1) = 0.073
    V(2) = deposite * 0.073
    exe3djxInterestSelect = V
  Case Else
    V(1) = 0.078
    V(2) = deposite * 0.078
    exe3djxInterestSelect = V
  End Select

End Function

It returns #VALUE! .

What is wrong with it ?

Thanks

It's easier to debug a UDF if you call it from a Sub instead of a worksheet. — Tim Williams
– Tim Williams, Commented Feb 8, 2013 at 16:11
I call it from a worksheet. I need to define a new function to call this one ? thanks ! — runner frank
– runner frank, Commented Feb 8, 2013 at 16:18
Just create a sub in the same module: it only needs one line (eg) Dim v:v=exe3djxInterestSelect(100) Run it by pressing F5 with the cursor in the sub. — Tim Williams
– Tim Williams, Commented Feb 8, 2013 at 16:58
For debugging a worksheet formula without an extra sub, insert a breakpoint into your code with F9. After pressing Enter in a formula window, you'll be directed to the breakpoint in VBE window and you can use F8/Ctrl-F8 for stepping through the code. — Jüri Ruut
– Jüri Ruut, Commented Feb 9, 2013 at 5:24

Charles Williams · Accepted Answer · 2013-02-08 17:33:04Z

1

You are mixing up one-dimensional and two-dimensional arrays: try this

Function exe3djxInterestSelect(deposite)
  Dim V() As Variant
  ReDim V(1 To 1, 1 To 2)
  Select Case deposite
  Case Is < 0
    exe3djxInterestSelect = CVErr(xlErrNum)
  Case 0 To 1000
    V(1, 1) = 0.055
    V(1, 2) = deposite * 0.055
    exe3djxInterestSelect = V
  Case 1000 To 10000
    V(1, 1) = 0.063
    V(1, 2) = deposite * 0.063
    exe3djxInterestSelect = V
  Case 10000 To 100000
    V(1, 1) = 0.073
    V(1, 2) = deposite * 0.073
    exe3djxInterestSelect = V
  Case Else
    V(1, 1) = 0.078
    V(1, 2) = deposite * 0.078
    exe3djxInterestSelect = V
  End Select
End Function

answered Feb 8, 2013 at 17:33

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

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

Collectives™ on Stack Overflow

Excel 2010 VBA function returning an array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related