Thread: Data Validation list

Hi everybody, just starting with VBA and got stuck on this....
I'm working on a function that gets its values from a Data Validation List.
My problem is that I want the function to return a value when an item in the list is selected and another if the user did not make a selection... just a basic if....then....else ... but the cell where I call this function shows the #VALUE! error while no item in the list is selected.
I call the function like this:
=myFunction(RC[-5], RC[-2])
where RC[-5] is the Data Validation List and RC[-2] is a cell where the user should enter a number
Any idea on why I'm getting the #VALUE! error instead of my message

The function basically looks like this:

Function myFunction(valueFromList, numericValue)

   If valueFromList <> "" and isNumeric(numericValue) Then

      ' do all the calcullations here
      '           .
      '           .
      '           .
   else
      myFunction = "choose item from list and enter numeric value"
   end if


end Function

I think all you need to do is dimension the function to return a string.

Try:

Function myFunction(valueFromList, numericValue) As String

It's hard to tell what might be wrong if we can't see the whole code.
From what I see, I'd suggest declaring all the variables.

Function myFunction(valueFromList As String, numericValue As Double) As String

Worksheet functions often have cell addresses as arguments. Declaring the argument as string tells Excel to pass the value in that address rather than the range.
It would be better if we could see

' do all the calcullations here

in more detail.

Thanks XLScottW and mikerickson,
I tryed declaring the variables but it did not work I still get the same #VALUE! error

This is a link to my excel test file http://www.creativeusacorp.com/VBAtest.xls

the error happens if you press DEL to clear a selection from the list

I'm looking for information everywere but there is no documentation of this behavior, could it be a bug on excel and VBA..???

Can you try putting the below formula in the cell "J15"?

=IF(LEN(H15)=0,"",lineTotal(H15,I15))

This should give you the required result.

Thanks Vidyadhar for your sugestion I try it and it worked but still.. won't be better to be able to do that check inside of the function... I'm still trying to do it that way, ... but still cant find an answer to the #VALUE! error, looks to me like a bug on excel or VBA..!

Hi

The following worked for me

Function myFunction(valueFromList, numericValue)

   If valueFromList <> "" And IsNumeric(numericValue) Then

      ' do all the calcullations here
      '           .
      '           .
      '           .
      myFunction = numericValue * 10
   Else
      myFunction = "choose item from list and enter numeric value"
   End If


End Function

Called as =myfunction(B8,C8)
B8:blank; C8:blank, result "choose from list.."
B8:blank; C8: 15; result "choose..."
B8:kkk; C8,15; result 150


rylo

Does not work for me , in your code B8 is a validation list..??

Here is a link to the excel test file that shows the error:
http://www.creativeusacorp.com/VBAtest.xls

thanks...

Hi

Try

Function lineTotal(product As String, qty As Double)

    If product <> "" And IsNumeric(qty) Then
        productPrice = WorksheetFunction.VLookup(product, Range("arrayProducts"), 2)
        lineTotal = productPrice * qty
    Else
        lineTotal = ""
    End If


End Function

rylo