Value# in VBA script with reference

[TMJ Vermeer]

Hi,

I'm not a specialist in VBA, but also not a newbee (I think).
I try to get the contence of a cell from a different worksheet but I
get the message: VALUE#
(A value used in the formula is of the wrong data type). What am I
doing wrong!
Below the function.

Function CalcValue(pVal) As Long

If pVal = 1 Then
CalcValue = wegingsfactoren!D3

ElseIf pVal = 2 Then
CalcValue = wegingsfactoren!D4

ElseIf pVal = 3 Then
CalcValue = wegingsfactoren!D5

ElseIf pVal = 4 Then
CalcValue = wegingsfactoren!D6

ElseIf pVal = 5 Then
CalcValue = wegingsfactoren!D7

ElseIf pVal = 6 Then
CalcValue = wegingsfactoren!D8

ElseIf pVal = 7 Then
CalcValue = wegingsfactoren!D9

ElseIf pVal = 8 Then
CalcValue = wegingsfactoren!D10

ElseIf pVal = 9 Then
CalcValue = wegingsfactoren!D11

ElseIf pVal = 10 Then
CalcValue = wegingsfactoren!D12

ElseIf pVal = 11 Then
CalcValue = wegingsfactoren!D13

ElseIf pVal = 12 Then
CalcValue = wegingsfactoren!D14

ElseIf pVal = 13 Then
CalcValue = wegingsfactoren!D15

ElseIf pVal = 14 Then
CalcValue = wegingsfactoren!D16

ElseIf pVal = 15 Then
CalcValue = wegingsfactoren!D17

ElseIf pVal = 16 Then
CalcValue = wegingsfactoren!D18

ElseIf pVal = 17 Then
CalcValue = wegingsfactoren!D19

ElseIf pVal = 18 Then
CalcValue = wegingsfactoren!D20

Else
CalcValue = 0
End If

End Function

Thanks

Theo Vermeer

 

[Bob Phillips]

Sounds like you are trying to assign a string to a long variable. Check all
of those cells that they contain numeric data.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[ric_deez]

Hi Theo,

Try this:

dim wks as Excel.Worksheet
set wks = Thisworkbook.Worksheets("wegingsfactoren")
Function CalcValue(pVal) As Variant

If pVal >= 1 and pVal <=18 Then
CalcValue = wks.Range("D" & pVal + 2).value
else
CalcValue = 0
end if

end function

Sub Test()
Dim i as integer
Dim t as variant
for i = -1 to 20
t = CalcValue(i)
debug.print t
next i
end sub

This will quickly tell you if you have a problem on a cell having
anything other than a long integer and will test your function for the
boundary conditions.

I didn't really need to re-write your function other than to change the
return type to variant for the test, but I couldn't resist it!!!

Let me know how you go...

Regards,

Ric

 

[Peter T]

In addition to Bob's advice (maybe change "As Long" to "As Variant")

If pVal = 1 Then
CalcValue = wegingsfactoren!D3

CalcValue = Range("wegingsfactoren!D3").value

or
Dim sAddr as string

If pVal = 1 Then
sAddr = "D3"

etc

CalcValue =Worksheets("wegingsfactoren").range(sAddr)

or
sAddr = "wegingsfactoren!D3"
etc
CalcValue =Range(sAddr)

==================

do you need a UDF, eg
=IF(AND(A1>0,A1<19),INDIRECT("wegingsfactoren!D" & A1 + 2),0)

Regards,
Peter T