FormulaArray de más de 255 caracteres desde VBA

Esta mañana hemos tenido un problema serio en la oficina.

Estamos tratando de introducir una fórmula matricial en unas celdas mediante VBA. Todo correcto hasta que en una de las fórmulas se producía un error, motivado porque la longitud de la cadena con la fórmula no puede exceder los 255 caracteres.

No los puede exceder en VBA, porque manualmente sí se pueden escribir fórmulas matriciales más largas.

Qué curiosa limitación. Supongo que aunque han actualizado Excel para que admita textos más largos trabajando normalmente (esto ya con la versión 2000 o antes), no han tenido tiempo de actualizar las clases de VBA para que se comporten de la misma manera.
Se da así la paradoja de que puedes grabar una macro en la que das los pasos manualmente sin problemas, pero que luego no seas capaz de ejecutar la macro porque obtienes un error misterioso
Parece que es un error común a todas las versione de excel, de la 2000 a la 2007 (a ver si para la próxima versión...)
http://support.microsoft.com/kb/213181/en-us

¿Cómo lo hemos solucionado?

Tras numerosas pruebas infructuosas, lo que resolvió el problema ha sido tratar de reproducir con la macro la introducción manual, mediante la instrucción SendKeys. Este método envía pulsaciones de teclas al sistema, es como si pulsases las teclas manualmente.

La solución ha sido poner en la primera celda del rango la fórmula normalmente (para la fórmula no matricial no se produce el error si la cadena supera los 255 caracteres), luego seleccionar el rango que ocupará la matriz y con SendKeys reproduzco lo que haría a mano, esto es, pulsar [F2] para editar la fórmula y después pulsar [Ctrl][Mays][Intro] para introducirla como fórmula matricial... y sorprendentemente Funciona.
 

Sólo hay que tener la precaución de que se encuentre activa la celda que queremos editar, como si lo fuésemos a hacer a mano. Otra precaución es devolver el control al sistema operativo para que interprete las pulsaciones. Esto se consigue con el método DoEvents antes de las instrucciones SendKeys.

Espero que este truco pueda servir para resolver ésta y otras situaciones en que existe un desfase entre lo que Excel (o culaquier otro programa) puede hacer manualmente y lo que puede hacer desde programación con una macro.

¿sabes Excel, pero necesitas más? entonces mira este manual

Temática: 

Comentarios

Re: FormulaArray de más de 255 caracteres desde VBA

Imagen de juandudas

muy bueno esto de SendKeys, para cuando falla todo lo demas, esto si que es automatizar al usuario, reproduciendo sus pulsaciones del teclado.

en cualquier caso, muy raro que con la macro se comporte de una manera y haciendolo a mano de otra

mi cabeza es un mar de dudas

Re: FormulaArray de más de 255 caracteres desde VBA

Imagen de visitante

Justamente tengo este inconveniente, pero como no conozco mucho la forma de utilizar SendKeys, me gustaría puedan ayudarme con un ejemplo, aplicado a la fórmula que muestro: 

"SUM(IF(planillamjt!E2:E1000=""FEMENINO"",IF(planillamjt!L2:L1000=""EMPLEADOS"",planillamjt!S2:S1000+planillamjt!U2:U1000
+
planillamjt!W2:W1000+planillamjt!Y2:Y1000+planillamjt!AA2:AA1000+planillamjt!AC2:AC1000+planillamjt!AE2:AE1000
+
planillamjt!AG2:AG1000+planillamjt!AI2:AI1000+planillamjt!AK2:AK1000+planillamjt!AM2:AM1000+planillamjt!AO2:AO1000)))"

Aclaro que mi intención es aplicar este código a un botón que esta en una hoja, los datos a sumar están en otra ("planillamjt") y el resultado se tiene que escribir en una tercera.

Gracias por la ayuda.

Robert

Re: ejemplo SendKeys

Imagen de pacomegia

por ejemplo, tu código podría ser algo así:


Range("A1").Formula = "=Sum(A5:B7)" 'introducimos la formula normalmente (no es matricial, no tiene ese límite de 255)
Range("a1").Select 'seleccionamos la celda antes de enviar las teclas
sendKeys "{F2}^+{ENTER}"  'F2 para editar y ctrl (^) Mays (+)  Enter para introducir como matricial
DoEvents ' para enviar las teclas al sistema

------
Ya sé Excel, pero necesito más ahora en pdf

 

Data Tools Suite
datos y tablas con Excel

Re: ejemplo SendKeys

Imagen de visitante

Molesto una vez sobre el tema. He probado esta solucion en una planilla de ejemplo y como dije funciona perfectamente. Pero cuando quise aplicarla a mi planilla de trabajo, me di cuenta que asi como esta el código, solo funciona cuando el botón se encuentra en la misma hoja donde quiero colocar la fórmula.

Mi caso es particularmente asi:

El botón esta en hoja1.

Los datos a sumar están en hoja2.

El resultado se debe colocar en hoja3.

 

Probè activando la hoja3, escribe la formula, pero no la convierte a matricial.

Y para empeorar tal vez las cosas, no tengo 1 sola fórmula. Tengo varias.

Entonces pregunto también: ¿Doevents lo debo escribir 1 sola vez? o ¿al final de cada fórmula?

Muchas gracias!

Re: ejemplo SendKeys

Imagen de pacomegia

SendKeys se comporta como cuando lo haces a mano con el teclado, así que para escribir algo en la hoja 3, la hoja 3 tiene que ser la hoja activa, y para introducir una fórmula en la celda A5, esa celda A5 debe ser la activa.

debes mandar las teclas al sistema cuando edites cada celda,
realiza un DoEvents inmediatamente después de cada SendKeys

[si te das de alta como usuario e inicias sesión, tus comentarios se publicarán en el acto, no quedan en la lista de aprobación, y no aparecerás como "visitante", y además recibirás un email de aviso cuando alguien conteste].

------
Ya sé Excel, pero necesito más ahora en pdf

 

Data Tools Suite
datos y tablas con Excel

Re: ejemplo SendKeys

Imagen de marluca

Una vez mas debo decir que la ayuda que me dieron dio resultado.Active la hoja, luego active la celda y funciona.

Pero habia sido, las pruebas que realizo no son las mismas que en mi planilla real.Porque los botones en mi planilla de pruebas estan colocadas directamente en la hoja, pero en mi planilla real, el botón está dentro de un formulario. Aqui la gran diferencia. Y por esto creo entender que no me funciona.

Entonces lo que obtengo es la formula pero no la hace matricial.

¿Hay una solución para esto?

Agrego planilla con el ejemplo de boton en la hoja (funcionando) y boton en un formulario (sin funcionar), para mejor comprensión.

Robert B.

Re: ejemplo SendKeys

Imagen de pacomegia

el formulario toma el control y no te deja modificar celdas (a mano tampoco te deja),
a no ser que lo abras sin ser modal, para conseguirlo añade el argumento vbmodeless al mostrar el formulario

UserForm1.Show vbModeless

ahora sí funcionará lo de las teclas, pero puede que no te interese, porque el usuario de la aplicación también puede toquetear cosas de la hoja mientras el formulario se muestra..

 

------
Ya sé Excel, pero necesito más ahora en pdf

 

Data Tools Suite
datos y tablas con Excel

Re: FormulaArray de más de 255 caracteres desde VBA

Imagen de shantiago

Se que esto se trato hace ya mucho tiempo pero ahora es que tengo este problema, y no se porque no me funciona, cuando grabo la formula me queda de la siguiente manera:

 Range("C22").Select
    ActiveCell.FormulaR1C1 = _

"=SUM(IF((BD_RC!R8C3:R100000C3=R10C3)*(BD_RC!R8C5:R100000C5=R6C[37])*(BD_RC!R8C63:R100000C63=R7C44)*(BD_RC!R8C19:R100000C19=R7C42)*(BD_RC!R8C41:R100000C41=R9C40)*(BD_RC!R8C18:R100000C18=RC2),BD_RC!R8C81:R100000C81,))+SUM(IF((BD_RC!R8C3:R100000C3=R10C3)*(BD_RC!R8C5:R100000C5=R6C[37])*(BD_RC!R8C63:R100000C63=R7C44)*(BD_RC!R8C19:R100000C19=R7C42)*(BD_RC!R8C41:R100000C41" & _
        "*(BD_RC!R8C18:R100000C18=RC2),BD_RC!R8C81:R100000C81,))"
    Range("C22").Select

A pesar de que no la grabe como formula matricial me genera un error Se ha producido el error '1004' al momento de la ejecución.
A que se debe esto, me pueden ayudar muchas gracias

la formula original es la siguiente:
=SUMA(SI((BD_RC!$C$8:$C$100000=$C$10)*(BD_RC!$E$8:$E$100000=AN$6)*(BD_RC!$BK$8:$BK$100000=$AR$7)*(BD_RC!$S$8:$S$100000=$AP$7)*(BD_RC!$AO$8:$AO$100000=$AN$9)*(BD_RC!$R$8:$R$100000=$B22);BD_RC!$CC$8:$CC$100000;))+SUMA(SI((BD_RC!$C$8:$C$100000=$C$10)*(BD_RC!$E$8:$E$100000=AN$6)*(BD_RC!$BK$8:$BK$100000=$AR$7)*(BD_RC!$S$8:$S$100000=$AP$7)*(BD_RC!$AO$8:$AO$100000=$AN$10)*(BD_RC!$R$8:$R$100000=$B22);BD_RC!$CC$8:$CC$100000;))

Quedo atento y muchas gracias!

Re: FormulaArray de más de 255 caracteres desde VBA

Imagen de pacomegia

no sé cuál será la causa, lo investigaré con más calma en otro momento.

he probado a grabar la fórmula con una macro y luego da el error 1004  al reproducir lo que ha grabado.¿?

 

 

si en vez de utilizar formulas estilo FC utilizas la fórmula con referencias de tipo A1 sí que funciona, es decir, utilizando  Activecell.Formula en vez de ActiveCell.FormulaR1C1

esta fórmula es más corta que la otra, así que huele a que la longitud de la fórmula está causando el problema.

ActiveCell.Formula = "=SUM(IF((BD_RC!$C$8:$C$100000=$C$10)*(BD_RC!$E$8:$E$100000=AN$6)*(BD_RC!$BK$8:$BK$100000=$AR$7)*(BD_RC!$S$8:$S$100000=$AP$7)*(BD_RC!$AO$8:$AO$100000=$AN$9)*(BD_RC!$R$8:$R$100000=$B22),BD_RC!$CC$8:$CC$100000,))+SUM(IF((BD_RC!$C$8:$C$100000=$C$10)*(BD_RC!$E$8:$E$100000=AN$6)*(BD_RC!$BK$8:$BK$100000=$AR$7)*(BD_RC!$S$8:$S$100000=$AP$7)*(BD_RC!$AO$8:$AO$100000=$AN$10)*(BD_RC!$R$8:$R$100000=$B22),BD_RC!$CC$8:$CC$100000,))"

 

  

 

Data Tools Suite
datos y tablas con Excel

mirando con más detalle

Imagen de pacomegia

probando otra vez, comparando la versión R1C1 de la fórmula en la celda, con lo que ha grabado el grabador de macros, veo que la fórmula grabada por el grabador ha olvidado una parte justo al final de la línea

 

grabado por la macro:

ActiveCell.FormulaR1C1 = _

        "=SUM(IF((BD_RC!R8C3:R100000C3=R10C3)*(BD_RC!R8C5:R100000C5=R6C[37])*(BD_RC!R8C63:R100000C63=R7C44)*(BD_RC!R8C19:R100000C19=R7C42)*(BD_RC!R8C41:R100000C41=R9C40)*(BD_RC!R8C18:R100000C18=RC2),BD_RC!R8C81:R100000C81,))+SUM(IF((BD_RC!R8C3:R100000C3=R10C3)*(BD_RC!R8C5:R100000C5=R6C[37])*(BD_RC!R8C63:R100000C63=R7C44)*(BD_RC!R8C19:R100000C19=R7C42)*(BD_RC!R8C41:R100000C41" & _

        "*(BD_RC!R8C18:R100000C18=RC2),BD_RC!R8C81:R100000C81,))"

 

Fórmula correcta:

ActiveCell.FormulaR1C1 = _

        "=SUM(IF((BD_RC!R8C3:R100000C3=R10C3)*(BD_RC!R8C5:R100000C5=R6C[37])*(BD_RC!R8C63:R100000C63=R7C44)*(BD_RC!R8C19:R100000C19=R7C42)*(BD_RC!R8C41:R100000C41=R9C40)*(BD_RC!R8C18:R100000C18=RC2),BD_RC!R8C81:R100000C81,))+SUM(IF((BD_RC!R8C3:R100000C3=R10C3)*(BD_RC!R8C5:R100000C5=R6C[37])*(BD_RC!R8C63:R100000C63=R7C44)*(BD_RC!R8C19:R100000C19=R7C42)*(BD_RC!R8C41:R100000C41=R10C40)" & _

        "*(BD_RC!R8C18:R100000C18=RC2),BD_RC!R8C81:R100000C81,))"

así que parece que el grabador era quien estaba metiendo la pata.

 

Data Tools Suite
datos y tablas con Excel

Copiar con macro celda con formula matricial

Imagen de adri2476

Buenas tardes, y desde ya agradezco la ayuda que pueda brindarme. He utilizado una formula matricial la cual inserto en la celda A2 de mi hoja, hago los siguientes pasos: escribo la fórmula, presiono CTRL+SHIFT+ENTER, y perfecto agrega las llaves, luego la copio a un rango de celdas, arrastrando la  misma, y perfecto funciona, pero...cuando quiero hacerlo a traves de una macro, como la formula tiene mas de 255 caracteres, la inserto como formula, luego con sendkeys envio las teclas correspondientes, y hasta ahi llego, ya que al copiarlas desde la macro, me inserta en todas las celdas del rango la misma formula sin cambiar las referencias de los rangos incluidos en la fórmula, hay forma de solucionarlo...

Buen fin de semana, y espero una ayudita. Desde ya muchas gracias.

Saludos!!!

Adriana