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
– Siddharth Rout, Commented Oct 10, 2013 at 17:25
Check in my post the implementations of my code whith your recommendations, It`s not working unfortunately. — CreamStat
– CreamStat, Commented Oct 10, 2013 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
– CreamStat, Commented Oct 10, 2013 at 17:41

enderland · Accepted Answer · 2013-10-10 17:28:04Z

1

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.

answered Oct 10, 2013 at 17:28

enderland

14.2k18 gold badges105 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrey Lujankin Over a year ago

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.

user2140261 · Accepted Answer · 2013-10-10 17:46:49Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related