Using text in VB Code

[nappyjim]

Hi, very new to VB code, I just found a code online and was trying t
convert it to work as I want. Pretty much, as you can see in the cod
below, I want the cell to say Hi, if the referenced cell has a 1 in it
But when I try it in excel, i get a #VALUE! error. if I put in a 2,
do get the correct return, and putting in anything else gets a 0, as i
supposed to. But how do I get text to return in the cell? I trie
changing cell format to text, but that didnt work.

And I need to use VB because I will end up having more than 7 I
statements, which I found out, is the most excel can handle.

Thanks.

Function CalcValue(pVal As String) As Long
Select Case pVal
Case "1"
CalcValue = "hi"
Case "2"
CalcValue = 64
Case Else
CalcValue = 0
End Select
End Functio

 

[Gord Dibben]

Either of these modifications works.

I would go with second one personally so Case numbers are numbers, not
text.

Function CalcValue(pVal As String)
Select Case pVal
Case "1"
CalcValue = "hi"
Case "2"
CalcValue = 64
Case Else
CalcValue = 0
End Select
End Function


Function CalcValue(pVal As Long)
Select Case pVal
Case 1
CalcValue = "hi"
Case 2
CalcValue = 64
Case Else
CalcValue = 0
End Select
End Function


Gord

 

[FxM]

Hi nappyjim,

With choice "1", you try to return a text ("hi") into a "long" format
(Function ... as Long).

Try with the following :
Function CalcValue(...)
(rest is the same).

@+
FxM



Le 28/02/2012 15:17, nappyjim a écrit :