Simple VBA question

[AgnessGP]

Hi All,

Just trying to get this to work:

Option Explicit
Option Base 1
Public Function selectProduct() As String

Dim list(1 To 5) As String
Dim RndNum As Integer

list(1) = "3D"
list(2) = "Holographic"
list(3) = "LCD"
list(4) = "LED"
list(5) = "Plasma"

RndNum = Rnd() * 5 + 0.5

If RndNum = 1 Then
selectProduct = list(1)
ElseIf RndNum = 2 Then
selectProduct = list(2)
ElseIf RndNum = 3 Then
selectProduct = list(3)
ElseIf RndNum = 4 Then
selectProduct = list(4)
ElseIf RndNum = 5 Then
selectProduct = list(5)
End If

End Function

The task is: "write a function that picks and displays a random product from an array of 5"

At the minute it just returns the value #NAME?

Any ideas?

Thanks!

 

[arc]

What's with all the IF/THENs? Can't you just

selectProduct = list(RndNum) ?

As for the return.. do need to cast it? I've never used VBA, so am guessing here.

 

[AgnessGP]

Was thinking how I could shorten it so thanks for that!

As for the return, I would simply type =functionX(CellX) into an empty cell within excel

EDIT: after changing the ifs to what you said above, i now get #Value? in the cell

 

[AgnessGP]

Fixed it, just in case anyone was interested:

Option Explicit
Option Base 1
Public Function selectProduct() As String

Dim list(1 To 5) As String
Dim RndNum As Integer

list(1) = "3D"
list(2) = "Holographic"
list(3) = "LCD"
list(4) = "LED"
list(5) = "Plasma"

RndNum = Rnd() * 5 + 0.5

If RndNum >= 1 And RndNum <= 5 Then
selectProduct = list(RndNum)
MsgBox selectProduct

End If

End Function