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.
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.