Macro para reemplazar las referencias de una formula por sus valores desde VBA
EL PROBLEMA ES SIMPLE DE ENTENDER: quiero que en las celdas seleccionadas y que contienen una fórmula, se reemplacen las referencias (argumentos), por los valores de donde jala.
Es decir, quiero que haga lo que hace la función "EVALUAR FÓRMULA" (de la pestaña FÓRMULAS del grupo AUDITORIA DE FÓRMULAS), pero dentro de la misma celda. No me importa perder las referencias, solo quiero los valores de esas referencias en esa celda.
Ejemplo:
> La celda A1 tiene una fórmula:
Celda A1: "= B2 + C3"
> Las celdas B2 y C3 tienen los siguientes valores:
Celda B2: "=1"
Celda C3: "=2"
>Al ejecutar la macro deseada:
> La celda A1 debe quedar así:
Celda A1: "= 1 + 2"
Ojo:
La celda A1 puede tener otra configuración de fórmulas y referencias.
Ejemplo: Celda A1: "=D4+F4-AA5"
Agradecería que me ayuden, pues he buscado como hacerlo por varios foros, pero no he encontrado una solución para ello. Solo he encontrado para poner el resultado final, pero eso no deseo, quiero los valores de cada referencia en dicha celda.
1 respuesta
[Hola
Pues sí, cuando hablas de "Auditoría de fórmulas" es relativamente fácil de entender y más con la explicación que complementa. Eso sí, el hacerlo no es fácil pero por suerte el gran "Chip" Pearson (QEPD) nos dejó algo que ayudará.
Lo primero es dejar en claro algunas cosas:
- Solo será útil para fórmulas hechas con operadores matemáticos: * / + - =
- Si hay otro tipo de fórmulas (que usen funciones, que tengan paréntesis, etc.) no funcionará (habría que hacer muchos más cambios que conlleva un tiempo que no es el que damos algunos para ayudar ad honorem).
- El ejemplo lo he dejado para aplicar en una celda en específico ("C1"), pero por ejemplo con un "For Each" puede ser adaptado a varias a la vez (ya esa es tarea tuya si lo necesitas).
Entonces, lo primero es copiar y pegar esto en un módulo "standard":
Function SplitMultiDelims(Text As String, DelimChars As String) As String() ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' SplitMutliChar ' This function splits Text into an array of substrings, each substring ' delimited by any character in DelimChars. Only a single character ' may be a delimiter between two substrings, but DelimChars may ' contain any number of delimiter characters. If you need multiple ' character delimiters, use the SplitMultiDelimsEX function. It returns ' an unallocated array it Text is empty, a single element array ' containing all of text if DelimChars is empty, or a 1 or greater ' element array if the Text is successfully split into substrings. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim Pos1 As Long Dim N As Long Dim M As Long Dim Arr() As String Dim I As Long '''''''''''''''''''''''''''''''' ' if Text is empty, get out '''''''''''''''''''''''''''''''' If Len(Text) = 0 Then Exit Function End If '''''''''''''''''''''''''''''''''''''''''''''' ' if DelimChars is empty, return original text ''''''''''''''''''''''''''''''''''''''''''''' If DelimChars = vbNullString Then SplitMultiDelims = Array(Text) Exit Function End If ''''''''''''''''''''''''''''''''''''''''''''''' ' oversize the array, we'll shrink it later so ' we don't need to use Redim Preserve ''''''''''''''''''''''''''''''''''''''''''''''' ReDim Arr(1 To Len(Text)) I = 0 N = 0 Pos1 = 1 For N = 1 To Len(Text) For M = 1 To Len(DelimChars) If StrComp(Mid(Text, N, 1), Mid(DelimChars, M, 1), vbTextCompare) = 0 Then I = I + 1 Arr(I) = Mid(Text, Pos1, N - Pos1) Pos1 = N + 1 N = N + 1 End If Next M Next N If Pos1 <= Len(Text) Then I = I + 1 Arr(I) = Mid(Text, Pos1) End If '''''''''''''''''''''''''''''''''''''' ' chop off unused array elements '''''''''''''''''''''''''''''''''''''' ReDim Preserve Arr(1 To I) SplitMultiDelims = Arr End Function
Luego, también copias y pegas esta macro:
Sub Convertir()
Dim MiArray() As String
Dim MiFormula As String
Dim x As Integer
MiArray = SplitMultiDelims(Range("C1").Formula, "=+-*/")
MiFormula = Range("C1").Formula
For x = 2 To UBound(MiArray)
MiFormula = Replace(MiFormula, MiArray(x), Range(MiArray(x)).Value)
Next x
Range("C1").Formula = MiFormula
End SubEsa macro es la que usaras para obtener el resultado deseado. No olvides que en "C1" debes tener la fórmula.
Saludos]
Abraham Valencia
Ah, solo por si acaso, no reemplazan por valores, pero usando las propiedad "Precedents" y "DirectPrecedents" se puede tener algo parecido a lo que hacen las herramientas de "Auditoría de fórmulas" pero, repito, sin reemplazar como tú deseabas.
Abraham Valencia
Gracias por tu respuesta amigo, pegue los 2 códigos en un módulo y corrí la de "Convertir", teniendo en cuenta que la fórmula debe estar en "C1" (En este caso la celda C1: "=A1+A2"), pero me sale un error en esta línea (<-), pues cuando depuro me resalta esa línea.
For x = 2 To UBound(MiArray)
MiFormula = Replace(MiFormula, MiArray(x), Range(MiArray(x)).Value) ' (<-)
' Donde: MiArray(x) aparece =0 cuando lo señalo con el puntero.
Next x
El error dice:
Se ha producido el error '1004' en tiempo de ejecución:
Error en el método 'Range' de objeto '_Global'
Realmente nose si funciona esta primera respuesta, pues en la segunda me mencionas que no se puede hacer lo que estoy buscando.
De todas maneras gracias por tu respuesta. Al final, artificio que planteas tiene sentido, pero falta codificarlo mejor. Igualmente seguiré buscando un poco más, y si no hay solución no me queda más remedio que hacerlo manualmente.
Por cierto, la celda A1: "=1"
y la celda A2: "=2"
[Hola
Ese error es genérico y puede deberse a varias cosas, como por ejemplo:
- No colocaste lo enviado en un módulo "standard". Por si caso los de las hojas y el del libro (que lleva nombre por defecto "ThisWorkBook") no son módulos "standard".
- Cuando a un objeto "Range" no lo antecede un objeto "Sheet" o "WorkSheet" el VBA asume que es parte de la hoja activa, por lo tanto si no lo pusiste en un módulo "standard" puede dar error como el mencionado.
- Si activaste la macro cuando la hoja activa era otra y no la de la fórmula, también dará algún error por lo mencionado líneas arriba.
- Si la hoja está protegida, también dará el error.
Entonces, más que "codificarlo mejor", lo que aparentemente podría estar faltando es aplicar lo que te he enviado sin obviar esos últimos puntos que espero ahora sí aclaren mejor todo (y que erróneamente asumí que conocías) y sin dejar de lado los otros que ya había comentado en el anterior mensaje.
Si te sigue dando error a pesar de que no hay alguno de los problemas mencionados, sugiero hacer lo siguiente: Crear un libro nuevo, que quede solo una hoja, en esa hoja aplicar la fórmula en "C1" (puede ser en cualquier celda si reemplazas lo necesario en la macro, pero por ahora mejor probar tal cual) y los valores en "A1" y "A2" tal cual mencionas lo has hecho. Insertas un módulo "standard" (por si no lo sabes, en el editor de VBA vas a "Insertar" y ahí eliges "Módulo") y ahí pegas todo, "corres" la macro y miras lo que ocurre.
Por último, no confundir las cosas, con las herramientas de "Auditoría de fórmulas" no se puede hacer lo que quieres, yo mencioné propiedades de VBA equivalentes solo como para que las conozcas y leas sobre ellas.
Abraham Valencia
Gracias por responder. En cuanto a la pruebas sobre los códigos, siempre los hago en una hoja nueva. Y sobre la inserción del código en un módulo es algo básico y por ahí no tengo problemas.
Si entonces no se puede hacer lo que busco, no hay razón para continuar preguntando sobre esto.
Finalmente agradecerte por haber atendido a mi consulta, y hasta pronto.
Pues el "artificio", como lo has llamado, que hice lo logra, si no has podido activarlo de forma correcta por ahí "algo" se te ha escapado.
Otra cosa que repetiré por tercera vez: Con "Auditoría de fórmulas", y equivalentes/similares no se puede, pero sí con lo que te he propuesto (cumpliendo todas la otras recomendaciones).
Abraham Valencia
