Crear una función en excel, utilizando funciones propias de la aplicación

Si entendí bien, lo que quieres es, si tu celda actividades es igual a 1 entonces que la función te regrese el valor de la celda A1 de la hoja "act". Si actividades = 2, la función debe regresar el valor de la ceda A2 de la hoja "act", ¿es correcto?

Prueba con lo siguiente:

Public Function CASO_ACTIVIDADES(actividades As Object, subactividades As Integer, casilla_anterior As Object)
    If actividades <> 0 Then
        CASO_ACTIVIDADES = Evaluate("=INDIRECT(""ACT!A"" & actividades)")
    End If
End Function

También puedes hacerlo de forma directa así

Public Function CASO_ACTIVIDADES(actividades As Object, subactividades As Integer, casilla_anterior As Object)
    If actividades <> 0 Then
        CASO_ACTIVIDADES = Sheets("act").Range("A" & actividades)
    End If
End Function

Saludos.Dante Amor

No olvides valorar la respuesta.

Lo que quieres que cambie es el número de hoja, entonces utiliza esta:

Public Function CASO_ACTIVIDADES(actividades As Object, subactividades As Integer, casilla_anterior As Object)
    If actividades <> 0 Then
        CASO_ACTIVIDADES = Sheets("act" & actividades).Range("A1")
    End If
End Function

Muchas Gracias dante pero no que se es pero no me sirve

El código real es este

If actividades <> 0 Then
CASO_ACTIVIDADES = Evaluate("=IFERROR(INDIRECT(""'ACT"" & actividades & ""'!$A1""),""No existe la hoja"")")
End If

Donde ACT es un indicador de hoja y la celda actividades es un numero

Lo que hace la función escrita normalmente es que me devuelva el dato de la celda "a1", pero si la hoja no existe me da error, entonces quiero que me escriba que no existe.

Muchas gracias

No necesitas utilizar evaluate, ni indirecto, simplemente puedes llamar a la hoja con esto:

CASO_ACTIVIDADES = Sheets("act" & actividades).Range("A1")

Ahora para revisar si existe o no la hoja primero tienes que verificar hoja por hoja, puede ser así:

Public Function CASO_ACTIVIDADES(actividades As Object, subactividades As Integer, casilla_anterior As Object)
'Por.DAM
    hoja = "act" & actividades
    For Each h In Sheets
        If h.Name = hoja Then existe = True: Exit For
    Next
    If actividades <> 0 Then
        If existe Then
            CASO_ACTIVIDADES = Sheets(hoja).Range("A1")
        Else
            CASO_ACTIVIDADES = "La hoja : " & hoja & " no existe"
        End If
    End If
End Function

El código hace lo que necesitas, como te comenté no es necesario utilizar indirect, ya que puedes hacer referencia directamente a la hoja, ahora si es necesario que utilices indirect, pues puede ser así:

Public Function CASO_ACTIVIDADES(actividades As Object, subactividades As Integer, casilla_anterior As Object)
'Por.DAM
    hoja = "act" & actividades
    For Each h In Sheets
        If h.Name = hoja Then existe = True: Exit For
    Next
    If actividades <> 0 Then
        If existe Then
            'CASO_ACTIVIDADES = Sheets(hoja).Range("A1")
            CASO_ACTIVIDADES = Evaluate("=INDIRECT(""'ACT"" & actividades & ""'!A1"")")
        Else
            CASO_ACTIVIDADES = "La hoja : " & hoja & " no existe"
        End If
    End If
End Function

Ambas soluciones te ponen el valor de la celda A1 de la hoja ACT , si la hoja no existe te pone el mensaje "La hoja no existe".

Saludos

Si es lo que necesitas podrías cambiar la valoración de la respuesta.

Hola dante Te agradezco muchísimo toda la ayuda.

Que pena contigo, ¿me puedes ayudar con mi ultima condición que creo que lleva mucho más código?

Tengo esto

Public Function CASO_ACTIVIDADES(actividades As Object, subactividades As Integer, casilla_anterior As Object)

donde actividades esta en la columna B

subactividades esta en la columna C

Lo primero es:

Lo que necesito es que me busque el ultimo dato de un rango variable

Dicho rango comienza desde la celda b5 y termina en la fila que se encuenta la función en la misma celda b

En este rango hay varias celdas vacías ( una idea es que pueda buscar el primer dato de abajo hacia arriba), esto debe ser una variable ejemplo "var1"

Lo segundo es:

Me busque en dato de la celda "subactividades" en la columna "C" de la Hoja ("ACT" & var1) y me devuelva el dato que se encuentra en la columna "D" ( esto puede ser como lo que hace la función buscarV)

Si el dato de la celda "subactividades" no se encuentra me devuelma "no se encuentra"

Muchas gracias dante por toda tu ayuda

Saludos