VBA Excel- Function which returns either a number or an string

Question

I have the following code: It´s working OK but what I want to improve it´s in the final part of the code, if contador = 0, I want mespt="No se considera" instead of just returning 0.

    Public Function mespt(tutor As String, mes As String, j As Long) As Double


Application.Volatile

Dim a As Long
Dim totalmesp As Double


mespt = 0
contador = 0
totalmespt = 0
For i = 4 To 1000
If Sheets("Hoja1").Cells(i, 2).FormulaR1C1 = tutor And Sheets("Hoja1").Cells(i, 5).FormulaR1C1 = mes Then
Select Case Sheets("Hoja1").Cells(i, j).Value



Case "No cumple"
a = 0
contador = contador + 1
Case "Regular"
a = 1
contador = contador + 1
Case "Pleno"
a = 3
contador = contador + 1
Case "No se considera"
a = 0
End Select


totalmespt = totalmespt + a
If contador = 0 Then
mespt = 0
Else
mespt = totalmespt / contador
End If

End If
Next


End Function

I`ve used with variant in the following way:

Funcion mespt(                       ) as Variant
.......

if contador = 0 then
mespt="No se considera" then
mespt=totalmespt/contador
end if

end function

But in the case contador=0, the function just returns #!Valor

Sorry, with the variant type it's workink OK as I expect now, the problem was just with a formula in excel which works with the function mespt.

@jsj: It is a valid answer. :) You might want to put it as an answer? — Siddharth Rout, Oct 10 '13 at 17:25
Check in my post the implementations of my code whith your recommendations, It`s not working unfortunately. — CreamStat, Oct 10 '13 at 17:30
Sorry, with the variant type it's workink OK as I expect now, the problem was just with a formula in excel which works with the function mespt. — CreamStat, Oct 10 '13 at 17:41

score 1 · Accepted Answer · answered Oct 10 '13 at 17:28

Use a Variant as JSJ said.

VBA automatically converts the Variant type to the appropriate type. In the below example, the function sets its return to either a boolean or string value depending on the argument to the function.

Private Function returnVariant(returnBoolean As Boolean) As Variant

    If returnBoolean Then
        returnVariant = False
    Else
        returnVariant = "Hi this is a string"
    End If

End Function

Private Sub showFunctionExample()
    Dim v As Variant
    Dim v2 As Variant

    v = returnVariant(True)
    v2 = returnVariant(False)

    Debug.Print CStr(v) + "- Type: " + CStr(TypeName(v))
    Debug.Print v2 + "- Type:" + TypeName(v2)
End Sub

For your code, do:

Public Function mespt(tutor As String, mes As String, j As Long) As Variant


Application.Volatile

Dim a As Long
Dim totalmesp As Double


mespt = 0
contador = 0
totalmespt = 0
For i = 4 To 1000
If Sheets("Hoja1").Cells(i, 2).FormulaR1C1 = tutor And Sheets("Hoja1").Cells(i, 5).FormulaR1C1 = mes Then
Select Case Sheets("Hoja1").Cells(i, j).Value



Case "No cumple"
a = 0
contador = contador + 1
Case "Regular"
a = 1
contador = contador + 1
Case "Pleno"
a = 3
contador = contador + 1
Case "No se considera"
a = 0
End Select


totalmespt = totalmespt + a
If contador = 0 Then
mespt="No se considera" 
Else
mespt = totalmespt / contador
End If

End If
Next


End Function

Note that you will have to be careful assigning this function to variables which are not of the Variant type themselves as you will get errors if you return a string and assign it to a double.

Is there a possibility to make it work in a VBA function that I am gonna use as a custom worksheet-function ?? I tested your returnVariant function and it does not seem to work. Background - I would like to return either a double or an error message as string. — Andrey Lujankin, Jan 27 '14 at 11:14

user2140261 · Answer 2 · 2013-10-10T17:46:49.020

I wouldn't use the Variant approach cause you could run into errors later on when trying to set variable with returns. I would return as a String on all accounts.

Public Function mespt(tutor As String, mes As String, j As Long) As String


Application.Volatile

Dim a As Long
Dim totalmesp As Double


mespt = 0
contador = 0
totalmespt = 0
For i = 4 To 1000
If Sheets("Hoja1").Cells(i, 2).FormulaR1C1 = tutor And Sheets("Hoja1").Cells(i, 5).FormulaR1C1 = mes Then
Select Case Sheets("Hoja1").Cells(i, j).Value



Case "No cumple"
a = 0
contador = contador + 1
Case "Regular"
a = 1
contador = contador + 1
Case "Pleno"
a = 3
contador = contador + 1
Case "No se considera"
a = 0
End Select


totalmespt = totalmespt + a
If contador = 0 Then
mespt = 0
Else
mespt = totalmespt / contador
End If

End If
Next


End Function

And then when using it test the return

Sub Sample()
IF IsNumeric(mespt("a","b","c") Then
    'Code if it comes back with a number
Else
    'Code to run if it doesn't not return number
End If
End Sub

OR

=if(ISNUMBER(mespt(a,b,c)), "What to do If Number is Returned", "What To do if NON-Number is returned")

IsNumeric returns True if the data type of Expression is Boolean, Byte, Decimal, Double, Integer, Long, SByte, Short, Single, UInteger, ULong, or UShort, or an Object that contains one of those numeric types. It also returns True if Expression is a Char or String that can be successfully converted to a number.

IsNumeric returns False if Expression is of data type Date or of data type Object and it does not contain a numeric type.

IsNumeric also returns False if Expression is a Char or String that cannot be converted to a number.

VBA Excel- Function which returns either a number or an string

2 Answers2