Pregunta Comportamiento extraño al asignar una matriz VBA a fórmulas de un rango de excel


Esto es extraño ... Alguien tiene algún conocimiento o puede descubrir por qué está sucediendo esto?

En el pasado, pude asignar una matriz de fórmulas a un rango de Excel (Ejemplo de asignación rápida:  Range("A1:A1000") = ArrayOfFormulas ) Funcionaba bien y RÁPIDO (muy  RÁPIDO) comparada para asignar celda por celda (Ejemplo de celda por celda: Range("A" & i).Formula=ArrayOfFormula(i) dentro de un bucle ¡Es lento!)

Ahora estoy trabajando con Excel 2013, y cuando intento asignar las fórmulas almacenadas en una matriz de cadenas a un rango de Excel, no funciona (Excel muestra la formulación pero no el cálculo según la parte izquierda de la imagen a continuación) Desafortunadamente, no puedo recuperar el código antiguo para comparar. Por ejemplo, el siguiente código muestra "= 1 + 2" en lugar de "3" en el rango de A1 a A1000.

Sub AssignFormulas_1()
    Dim i as Long
    Dim FORML_ARRAY(1 To 1000, 1 To 1) As String
    For i = 1 To 1000
         FORML_ARRAY(i, 1) = "=1+2"
    Next i
    Range("A1:A1000").Formula = FORML_ARRAY  '<- Don't work as formula
                                             '   It put the value!
End Sub

Sin embargo, cuando no utilizo la matriz de fórmulas funciona bien (todas las celdas muestran "3" en lugar de "= 1 + 2" según la parte derecha de la imagen a continuación). Aquí el código:

Sub AssignFormulas_2()
    Dim i as Long
    Dim FORML_SINGLE As String
    FORML_SINGLE = "=1+2"
    Range("A1:A1000").Formula = FORML_SINGLE '<- works ok, not practical for my
                                             '   real life case as I need 
                                             '   different formula for each cell.
End Sub

El siguiente código también funciona bien (en este caso, asigno las fórmulas celda por celda, por lo que es más flexible, pero sloooowly para las fórmulas grandes).

Sub AssignFormulas_3()
    Dim i as Long
    Dim FORML_ARRAY(1 To 1000, 1 To 1) As String
    For i = 1 To 1000
         FORML_ARRAY(i, 1) = "=1+2"
         Range("A1:A" & i).Formula = FORML_ARRAY(i, 1) '<- works ok, but slowly
    Next i
End Sub

Las imágenes con la salida de todo el código anterior:

Example of result wrong (left) and correct (right)


Nota: ¡Las celdas fueron formateadas correctamente como número, no como texto!


5
2017-10-08 03:57


origen


Respuestas:


Tu primer código debería funcionar.
En lugar de declararlo como una cadena, declararlo como variante.

Así que cambia tu declaración de esto:

Dim FORML_ARRAY(1 To 1000, 1 To 1) As String

a esto:

Dim FORML_ARRAY(1 To 1000, 1 To 1) As Variant

No tengo forma de probarlo en Excel 2013, pero debería funcionar.
Espero que sí.


6
2017-10-08 04:59



Interesante pregunta. Nunca he usado esta funcionalidad antes.

El primero tiene el mismo resultado para mí en 2010 y 2013: una columna que contiene la cadena "=1+2". Cambié FORML_ARRAY de una cuerda a una Variant y funciona como se esperaba Para una buena medida cambié la fórmula para incluir i y todavía llena el rango con fórmulas:

Sub AssignFormulas_1()
    Dim i As Long
    Dim FORML_ARRAY(1 To 1000, 1 To 1) As Variant 'changed to Variant
    For i = 1 To 1000
         FORML_ARRAY(i, 1) = "=" & i & "+2" 'changed 1 to i - still works
    Next i
    Range("A1:A1000").Formula = FORML_ARRAY
End Sub

3
2017-10-08 05:00



Solo para agregar otra opción, porque dijiste que preferirías atenuar tu matriz correctamente. Con esto puedes, solo agrega 1/25 de segundo a la EN GENERAL Sub (no por bucle), pero le permitirá atenuar su matriz como una cadena.

Sub AssignFormulas_2()
    Dim i As Long

    Dim FORML_ARRAY(1 To 1000, 1 To 1) As String

    For i = 1 To 1000
         FORML_ARRAY(i, 1) = "= " & i & " + 2"
    Next i

    Range("A1:A1000").Formula = FORML_ARRAY  '<- Don't work as formula
                                             '   It put the value!

    Range("A1:A1000").Value = Range("A1:A1000").Value
End Sub

Ahora, no estoy seguro de por qué excel lee las cadenas como constantes, pero si ejecuta su fórmula original y va a Evaluar fórmula, da el error de que la celda contiene una constante, Esto realmente solo parece ser un error, pero si figura algo más editaré mi respuesta también.

NOTA:


2
2017-10-08 13:39