Excel - FUNCIONES VBA

Hola a todos de nuevo

Estoy intentando hacer una funcion con VBA y no puedo resolver lo siguiente:

Quiero que al llamar a la funcion el resultado me aparezca en un rango a modo de tabla dinamica.

He intentado usar ActiveSheet.Cells( fila, columna) para indicar en la funcion donde quiero poenr los resultados que obtengo., pero no lo he conseguido.

Tambien he usado ActiveWorkBook y tambien con ActiveWorbook.Application....

De momento no he conseguido lo que quiero.

Es posible lo que pido o deberia de hacer una Sub en vez de una Function??

Muchas gracias.

Un saludo a todos.

Function arroja un solo resultado

es verdad que Function devuelve un solo valor
pero ese valor puede ser una matriz y de esa manera
se puede llenar un rango utilizando la función como función matricial
la clave está en saber usar una función matricial
no sé cuál será tu caso, probablemente no sea necesaria una función
sino con una macro 'normal' (un Sub) puede andar bien
si querés hacerlo mediante funciones, creo q no queda otro camino
que las funciones matriciales... porque por otro lado, una función no puede
modificar una celda cualquiera, sólo devuelve un valor en la celda desde
la cual fue llamada y nada más (en realidad esto último no es tan así, conozco un
'truco' que permite modificar celdas desde una Function... pero eso es
otra cosa...)

Saludos desde Baires, JuanC

"
conozco un
'truco' que permite modificar celdas desde una Function... pero eso es
otra cosa...) "

Si es mas allá de VBA entonces dejalo, mis conocimientos son limitados.

Cuando dices de una Sub a que te refieres llamarla desde la Function o una Sub normal.

Queria en forma de Function para que se actualicen los rangos en caso necesario.

Lo que intenté era devolver el valor en un rango seleccionado llenando sus celdas mediante codigo en la function , pero no me hizo nada dejor aqui el codigo a ver si es un error mio.

Gracias de todas formas por el interes.

'buscar y sumar rangos de datos de terrenos por F.Naranjo
Public Function suma_dinamica(ByVal Celda_Colocacion_tabla As Excel.Range, ByVal suelo_buscado As Variant, ByVal Rango_de_suelo As Excel.Range, ByVal Rango_de_datos As Excel.Range) As Variant

Dim rango_salida_resultado As Excel.Range
Dim todo_valores() As Variant

ancho = Rango_de_suelo.Columns.count
alto = Rango_de_suelo.Rows.count
ReDim todo_valores(30) As Variant
ReDim celdas(ancho * alto)
contador = 0

For columna = 1 To ancho
For fila = 1 To alto
contador = contador + 1
celdas(contador) = Rango_de_suelo.Cells(fila, columna)

Next fila
Next columna
contador = 1
contador_comparativo = 1

For columna = 1 To ancho
For fila = 1 To alto
contador = contador + 1
existe = False
valor = Rango_de_suelo.Cells(fila, columna)
On Error Resume Next
For x = 1 To contador_comparativo
If celdas(contador) = todo_valores(x) Then

existe = True



End If
Next x
If existe = False Then

todo_valores(contador_comparativo) = celdas(contador)
contador_comparativo = contador_comparativo + 1
End If

Next fila
Next columna

celda_salida_fila = Celda_Colocacion_tabla.Row
celda_salida_columna = Celda_Colocacion_tabla.Column

For x = 1 To contador_comparativo - 1

'***************
'***************
'la siguiente linea de codigo es la que no consigo que me rellene la celda que quiero

Celda_Colocacion_tabla.FormulaR1C1(x, 1) = todo_valores(x)
Cells(celda_salida_fila + x, celda_salida_columna) = todo_valores(x)

Next x

On Error Resume Next

For columna = 1 To ancho
For fila = 1 To alto

valor = Rango_de_suelo.Cells(fila, columna)
If Rango_de_suelo(fila, columna) = suelo_buscado Then
rango_fila = fila
rango_columna = columna

suma_area = suma_area + Rango_de_datos(rango_fila, rango_columna)
count = count + 1

End If

Next fila
Next columna

suma_dinamica = suma_area

End Function

el tema no va más allá de VBA, pero requiere conocer muchos pequeños detalles...
digo que por ahí te convenga hacerlo como una macro normal,
que pueda ser llamada a través de un UserForm para cambiar los distintos
parámetros (argumentos actuales de tu Function)
al código lo entiendo pero no lo comprendo...
quisiera poder ayudarte pero no tengo la información adecuada para saber
qué debe hacer la función (sólo vos sabés cuáles son los datos y qué querés hacer)
desde ya que el código no puede modificar cualquier celda, pero si
trabajás con un UserForm y lo ejecutás como macro y no como UDF
seguramente va a funcionar...

Saludos desde Baires, JuanC

Bueno en realidad no es necesario que debas comprender el codigo pues la verdad que es algo que como bien dices lo se yo porque es una cosa adaptada a mis necesidades.

Realmente mi codigo posiblemente se pueda realizar mas eficientemente de otra forma y seguro que a lo mejor incluso con una tabla dinamica se puede hacer ( no las domino muy bien)

Pero aun asi es para mi un reto poder hacermelo yo mismo :-)

En resumidas cuentas lo que pretendo hacer con esta funcion es poder sumar todas las veces que aparece un tipo de terreno ( el cultivo al que se dedica en una tabla)

Son aprox 200 parcelas y cada una de ellas puede tener subparcelas dedicadas a diferentes cultivos. Es por eso que para que entiendas lo que hace deberias tener la tabla, pero a modo de pequeño ejemplo hace asi:

A B C D E F H
1 2 8 Regadio Secano Regadio Tipo
1 0 5 Frutales pastos Perales

Lo que hace la Funcion es sumar todas las parcelas de Regadio, Secano ... por separado claro.

Regadio=1+2+8
.
.
.
Etc.
Lo he hecho para un solo tipo marcando el rango H donde el tipo corresponde al tipo de cultivo que he de escribirlo en esa celda.

Para cada tipo lo he de escribir y luego arrastro la formula que la tengo la columna I

pero que me ha pasado que a veces en el rango de columnas D:F el ripo de cultivo esta escrito de forma incorrecta ( escrito or mi :-( ) "Regadio ,regadio ,rEgadio" )

Y al ser una gran cantidad de datos no se buscar donde está el error en las sumas que me deben de dar al comprobar la suma del Rango A:C

Lo que prentendia en la nueva funcion era poder en primer lugar hacer un abusqueda en el Rango D:F de todo lo que hay y crear una lista de todos los tipos que existen, para luego sumarlos por separadao y mostrarlos en un nuevo rango de salida de datos por ejemplo el rango I, pero que pasa? que no he podido.

Se que se puede hacer mediante Sub pero el reto para mi es poder hacerlo mediante una Function porque asi puedo en primer lugar marcar rangos con un Form propio de Excel que parece mas profesional y segundo porque de esa manera si actualizo datos en los rangos A:F automaticamente se actualiza el Function sin itervencion mia y en tiempo real ( no quiero un sub con Selection.Change )

Es posible pues hacerlo como quiero?

Muchas gracias y espero hacerte aclarado como funciona el Function.

Un saludo.

pasame el archivo o un copia de la hoja con los datos y un ejemplo
de uso de la función (aún no me queda del todo claro)

Saludos desde Baires, JuanC

Lo tendre que pasar a tu email verdad?

Saludos.

Ante todo mil gracias JuanC por el codigo

Creo que es mas o menos lo que andaba buscando.

La verdad es que tengo que digerirlo pues no lo entiendo demasiado, mi nivel de programacion esta muy lejos del tuyo y para mi es dificil comprender lo que hace la funcion :-(

Realmente hace lo que pretendia con mi codigo e intuyo que 3000000% mas eficientemente :-)

Tengo de todas formas dudas de como poder ampliar la matriz de 12x1 por si se me queda pequeña.

Muchisimas gracias de nuevo y voy a intentar comprender el funcionamiento de tu codigo.

Ah y lo de la publicacion aqui mejor te lo dejo a ti que eres tu el autor y entiendes su funcionamiento ya que no podria explicar como funciona siquiera.

Saludos.

Francisco Naranjo

Option Explicit

'//By JuanC - 05 de Junio 2007

'//Disposición de los datos:
'----------------------------
'1 1 1 regadio secano regadio
'2 2 2
'3 3 3 frutales secano regadio
'4 4 4 regadio

'//La función suma los valores 'por grupos' y
'//devuelve una matriz/vector M(nx1);
'//n=nº de elementos de la matriz de datos
'//En el ejemplo n=12, luego M(12x1)

'//El argumento de la función es el rango
'//que corresponde a la matriz de Valores (números)
'//Para el ejemplo el rango es A2:C5

'//Fórmula matricial: {=Sumar_Suelos(A2:C5)}

'//Valores devueltos:
'(la fórmula es ingresada como matriz de 12x1
' en el rango A7:A18)
'---------------------------------------------
'//Valores devueltos (elementos del vector)
'[A7] corresponde a M(0) --> regadio: 9
'[A8] corresponde a M(1) --> secano: 4
'[A9] corresponde a M(2) --> : 14
'[A10] corresponde a M(3) --> frutales: 3
'[A11] corresponde a M(4) --> (vacío)
'.....
'[A18] corresponde a M(17) --> (vacío)

'//Nota: podría mejorarse el código para
'// ordenar el resultado
'// también podría usarce una matriz
'// en lugar de un tipo de dato definido,
'// pero por 'costumbre' usé Type
'// espero que les sirva... de eso se trata...

'//Código de la función

Type T_datos
sSuelo As String
dValor As Double
End Type

Public Function Sumar_Suelos(R As Range) As Variant
Dim cell As Range
Dim sSuelos$, sSuelo$
Dim v() As T_datos, c&, i&
Dim w() As String

On Error Resume Next
Application.Volatile

c = 0

For Each cell In R
With cell
sSuelo = Cells(.Row, .Column + 3)
If InStr(1, sSuelos, "<" & sSuelo & ">", vbTextCompare) > 0 Then
For i = 0 To c - 1
If v(i).sSuelo = sSuelo Then
v(i).dValor = v(i).dValor + .Value
Exit For
End If
Next
Else
sSuelos = sSuelos & "<" & sSuelo & ">"
ReDim Preserve v(c) As T_datos
v(c).sSuelo = sSuelo
v(c).dValor = .Value
c = c + 1
End If
End With
Next

ReDim w(R.count) As String

For i = 0 To c - 1
With v(i)
w(i) = .sSuelo & ": " & .dValor
End With
Next

Sumar_Suelos = Application.WorksheetFunction.Transpose(w)

Erase v
Erase w
End Function

Saludos desde Baires, JuanC

Tras mucho intentar, no consigo comprender como funciona lo de la funcion matricial

Recuerda como puedo variar el rango de la matriz M(nx1) para que tenga el valor n=numero maximo de parcelas o datos.

He intentado en el codigo pero no se como la dimensiona y que tan solo se dimensiona la matriz W( )

otra cosa que tiene tu codigo pero que si lo puedo arreglar a mi conveniencia es que el rango de datos y de parcelas no son contiguos,( ero bueno eso no tiene importancia9

Lo mas importante para mi es comprender como funciona la matriz M(nx1)

Ah me ha gustado tu metodo de busqueda de parcelas, el mio se que era muy bestia pero buno jajajaja.

Muchas gracias y un saludo desde Marbella (Malaga)

Frany

De la Ayuda de Excel
-------------------------------
Fórmula matricial

Ejecuta varios cálculos y devuelve un solo
resultado o varios resultados. Las fórmulas
matriciales actúan en uno o en varios conjuntos
de valores denominados argumentos matriciales.
Cada argumento matricial debe ser rectangular y tener
el mismo número de filas o el mismo número de
columnas que los otros argumentos. Para obtener varios
resultados, la fórmula matricial deberá insertarse en varias celdas.

Para introducir una fórmula matricial, presione
CONTROL+MAYÚSCULAS+ENTRAR.
Microsoft Excel coloca las fórmulas matriciales entre llaves ({}).

Introducir una fórmula matricial

1. Si la fórmula matricial devuelve un único resultado,
haga clic en la celda en que desee introducirla.
Si la fórmula matricial devuelve varios resultados,
haga clic en el rango de celdas en que desee introducirla.

2. Escriba la fórmula matricial.

3. Presione CTRL+MAYÚS+ENTRAR.

----------------------------------------------------------------------

Lo que sigue es mío... bah, de todos...
Primero y antes que nada decir que el tema en
cuestión no es de fácil digestión...
Básicamente se necesita:
saber qué es una matriz, saber usar funciones
básicas de Excel y sobre todo usar el cerebro...
Voy a intentar ser claro y breve, con un par de
ejemplos como para 'ver de que se trata'.

Aclaración: FM = abreviatura de Fórmula Matricial

Ejemplos
---------------
Caso 1: FM que devuelve un valor

Datos: en el rango A1:A10 se tienen números, 1 y 2
Objetivo: calcular cuántos números 2 hay en el rango

Resolución con FM:
La idea es comparar cada valor del rango con el nº 2
y si es igual, contarlo.
La FM justamente lo que hace es: le aplica la
fórmula 'simple' a cada valor del rango
y obtiene un resultado único.

Es algo así como calcular
(1) =SUMA(SI(A1=2 ; 1 ; 0)) Si A1 = 2 obtiene 1, si no, 0
(2) =SUMA(SI(A2=2 ; 1 ; 0))
(3) =SUMA(SI(A3=2 ; 1 ; 0))
...
(10) =SUMA(SI(A10=2 ; 1 ; 0))

Obtener el valor final:
=(1)+(2)+(3)+...+(10)

En una FM el proceso anterior queda resumido
en una sola expresión:

{=SUMA(SI(A1:A10)=2 ; 1 ; 0)}

Caso 2: FM que devuelve una matriz (varios valores)

Datos: en el rango A1:B2 se tienen:
1 2 (A)
3 4
en el rango C1:D2 se tienen:
-1 0 (B)
0 -1

Objetivo: calcular el producto matricial de A por B

Resolución con FM:
Se utiliza la función MMULT (Categoría Matemáticas
y Trigonométricas) que multiplica dos matrices y
devuelve una matriz producto.

Se selecciona el rango E1:F2 (un rango que tenga
4 celdas: 2filas x 2columnas) y se escribe la
fórmula =MMULT(A1:B2; C1:D2) en la celda activa;
luego se presiona CTRL+MAYÚS+ENTRAR.

El resultado es:
-1 -2
-3 -4

Comentarios
-------------------
Para construir una matriz(*) con una FM:
Seleccionar el rango de tamaño equivalente a la
matriz que se desea obtener e ingresar
la fórmula en la celda activa (perteneciente
al rango seleccionado) y presionar
CTRL+MAYÚS+ENTRAR.

Ej: Seleccione el rango A1:B2 para obtener
una matriz de 2x2 como resultado.
Una vez seleccionado el rango escriba la
fórmula =ALEATORIO() en la celda activa
-para cambiar de celda activa presione ENTRAR-
y para terminar presione CTRL+MAYÚS+ENTRAR
Excel automáticamente agrega las {} a la fórmula
y completa el rango con la misma FM.
De esa manera se acaba de crear una matriz.
Cada FM devuelve un valor que corresponde a cada
elemento de la matriz.

(*) matriz incluye el caso de los vectores (n-upla)

Saludos desde Baires, JuanC

La verdad que eres del todo didactico, da gusto leer tus explicaciones.

:-) no es por ser pesado, pero.....esto se podria de alguna manera generar con codigo?

La verdad que es muy interesante, poder por ejemplo hacer la tabla que se necesite de esta forma.

Bueno en realidad para mi calculo use la funcion de suma_datos que habia creado inicialmente , aunque un poco burda, pero es que no tenia tiempo ya que tenia que entregar el trabajo hoy mismo y bueno me ha servido muchisimo la verdad toda tu explicacion de funciones, por que si miras el codigo de la funcion que cree veras que parte de la base del codigo inicial que tu mismo pusiste ( gracias)

De todas maneras creo que el tema de funciones no lo puedo explotar tando como las Subrutinas. gran parte del trabajo lo he hecho con pequeñas Sub, mas que nada porque necesitaba pasar datos de una tabla a otra con referencias en el ID de ambas.

No se si todo esto sea mas facil con ACCESS, pero la desventaja de ACCESS al menos para mi es que no puede poner formuals de calculo primordial para el trabajo que he hecho, sin enmbargo EXCEL combina calculo con base de datos,

Finalmente casi todos los listados los he generado con COMBINAR CORRESPONDENCIA en Word.

Me queda aun mucho que aprender. Quisiera poder manejar bien lo de COMBINAR CORRESPONDECIA mediante comandos SQL y varias tablas, pero eso lo veo aun muy lejano.

NUEVAMENTE GRACIAS POR TODO JUANC.

Saludos ty buen fin de semana.

Francisco Naranjo

Range("K15:K23").Select
Selection.FormulaArray = "=sumar_suelos(R[-13]C[-10]:R[-5]C[-8])"

con esto?

Olvidaba la grabadora por un momento :-(