Problemas con una función

[minayax]

He creado una funcion para realizar una serie de calculos, hasta aqui no hay problemas. El problema radica en que cuando cambio los datos de origen la función no se actualiza y me muestra los datos antiguos. Tengo otras funciones que se actualizan perfectamente, y la diferencia entre unas y otras (aparte del codigo) es que las que funcionan cogen los datos de la misma hoja y las que no se actualizan cogen los datos de otra hoja, alguien tiene alguna idea de cual puede ser el problema?

 

[Juan Pablo González]

La función utiliza los datos de las celdas como parámetros ? o está utilizando información de formato de las celdas ? cómo es el código ?

 

[minayax]

La función utiliza los datos como parametros, exactamente el código es el siguiente:
Public Function SCuentas(Cuenta As String, Ejercicio As String, Hoja As Integer) As Double
If Ejercicio = 1 Then
e = 2
ElseIf Ejercicio = 2 Then
e = 3
End If
i = 1
Tcuenta = Len(Cuenta)
SCuentas = 0
For i = 1 To 65536
Set datos = Worksheets(Hoja).Cells(i, 1)
datos2 = Left(datos, Tcuenta)
If datos2 = "" Then
'i = 655536
Exit Function
Else
If Cuenta = datos2 Then
SCuentas = SCuentas + Worksheets(Hoja).Cells(i, e).Value
End If
End If
Next

End Function

Donde Cuenta es el valor de una celda de la misma hoja donde va la función, Ejercicio indica la columna donde están los datos, y Hoja indica en qué hoja están los datos, va desde la 1 hasta la 4.

El modo de calcular está en automático, y tengo otra función personalizada que lo que hace es sacar los números que hay dentro de una celda que funciona perfectamente.

 

[minayax]

Al final ya encontre la solución, consiste en incluir al principio de la función el método Application.Volatile Así todo funciona correctamente y se actualiza ante cualquier cambio.

De todas formas muchas gracias por tu tiempo.

 

[Juan Pablo González]

Ah, el problema de la función es que no sabe, con el parámetro hoja, que se está haciendo referencia a otra hoja. Por eso es que no se recalcula automáticamente, como debería hacerse.

El uso de Application.Volatile, aunque util, es peligroso, porque puede hacer que la hoja se vuelva muy lenta en recalcular.

 

[minayax]

Abusando de tu amabilidad, y estando totalmente de acuerdo contigo en que el metodo Volatile ralentiza mucho la hoja, ¿cómo se debería de hacer para que la función se de cuenta de que se están buscando datos de otra hoja?

 

[Juan Pablo González]

Depronto así:

<pre>Public Function SCuentas(Cuenta As String, Ejercicio As String, Rango As Range) As Double
Dim Hoja As Worksheet
Set Hoja = Rango.Parent
If Ejercicio = 1 Then
e = 2
ElseIf Ejercicio = 2 Then
e = 3
End If
i = 1
Tcuenta = Len(Cuenta)
SCuentas = 0
For i = 1 To 65536
Set datos = Hoja.Cells(i, 1)
datos2 = Left(datos, Tcuenta)
If datos2 = "" Then
'i = 655536
Exit Function
Else
If Cuenta = datos2 Then
SCuentas = SCuentas + Hoja.Cells(i, e).Value
End If
End If
Next

End Function</pre>

Donde Rango es una celda de la hoja que se quiere tener en cuenta.

 

[Juan Pablo González]

Puedo preguntar cuál es el objetivo de la función ? creo que esta UDF no es la mejor manera de hacerlo...

 

[minayax]

El objetivo de la funcion es sumarme todos los valores de la hoja 1,2,3 o 4 (la que corresponda) que conicidan con los valores buscados. Me explico
En la hoja de destino tengo por ejemplo la cuenta 631, y en la hoja de origen tengo en la columna A 631000008, 63100009, 5630008 etc, y en la columna C (ejercicio 1) el valor de esa cuenta. El problema es que yo no se exactamente en que Celda de la columna A están los valores, ni siquiera se si existen, puede que si puede que no, puede que halla uno o mas. Y el valor que estoy comparando puede que tenga 3 digitos o cuatro, eso tampoco lo se. Y son unos 600 valores los que estoy comparando.

 

[minayax]

Hola Juan Pablo,
Al hacerlo como me indicas me vuelve a salir el problema de que no se actualizan los valores si realizo algún cambio. También puede ser que esté metiendo mál el parametro rango. Yo pongo Hoja2!A1 o la hoja que corresponda.

Valiendome de tu ejemplo he echo una pequeña modificación y es definir Hoja como worksheet, y eliminar Rango con lo que quedaría más o menos:

Public Function SCuentas(Cuenta As String, Ejercicio As String, Hoja As Worksheet) As Double
Si compruebo su validez desde VBA traspasandole estos valores, funciona perfectamente,
SCuentas(100, 1, Hoja2)
Pero si intento hacer lo mismo desde la hoja de excel me da error.
=SCuentas(100;1;Hoja2)