VBA EXE from string. Aplicación ejemplo: expresiones regulares. Reemplazar con operaciones

Ejecutar un string como un comando

NOTA: Todo en Access 2016, válido para Excel con ligeras modificaciones, ver al final

Se trata de introducir en un campo de texto una cadena que contiene comandos que puedan ser ejecutados en un procedimiento o función, en tiempo de ejecución. Es decir, creamos un procedimiento (o función) con los comandos incluidos en esa cadena de texto.

Existen varios ejemplos en internet:

Varios enlaces de ejemplo

https://stackoverflow.com/questions/43216390/how-to-run-a-string-as-a-command-in-vba

https://stackoverflow.com/questions/42231887/vba-execute-code-in-string

Y varios enlaces con las referencias vba a los objetos VBE (VB Editor) que permiten insertar código en tiempo de ejecución.

http://www.cpearson.com/excel/vbe.aspx

https://codereview.stackexchange.com/questions/52364/extending-the-vba-extensibility-library

Meta Programming in VBA: The VBIDE and Why Documentation is Important

NOTA: en mis pruebas, he comprobado que para que todo funcione correctamente, hay que realizar necesariamente dos pasos (dos eventos distintos)

En un primer evento (un botón, un after_update, un _click, etc) se crea o inserta en tiempo de ejecución el código que queremos ejecutar (string)
En un segundo evento (un botón) realizamos cualquier acción usando el procedimiento o función que hemos creado

He comprobado que si lo hacemos todo seguido, es decir, si en un solo evento (subrutina de código) creamos el procedimiento/función a partir del string, y lo usamos, Access deja de funcionar y se reinicia. (quizá ocurra algo raro con el formulario, que ha de refrescarse…)

Primer paso: incluir referencia en Access y preparar entorno

Con eso ya lo tenemos

Así queda todo:

Ahora preparamos un pequeño entorno para la prueba

Creamos un formulario con dos campos de texto y un botón.

Creamos un formulario en blanco con un campo de texto, que será donde incluiremos el string a ejecutar. Otro campo de texto donde pondremos los resultados de esa ejecución y un botón para ejecutar

Los nombres que les he dado son:

textoIN, textoOut y botonExe. El botón botonExe le ponemos un procedimiento de código en su evento click

Ahora vamos con el código.

Abrimos el editor de VB y escribimos la rutina que crerá el procedimiento o función con los comandos que escribamos en textoIN.

Insertamos el siguiente código

Sub StringExecute(s As String)
Dim VBProj As VBProject
Dim VBComp As VBIDE.VBComponent
Dim VBCode As VBIDE.CodeModule
Dim ProcName As String
Dim StartLine As Long
Dim NumLines As Long

Set VBProj = VBE.ActiveVBProject
Set VBComp = VBProj.VBComponents("Form_Formulario1")
Set VBCode = VBComp.CodeModule

‘ Primero borramos lo que haya, esto borra todas las lineas, incluso las de definición y fin de la subrutina
ProcName = "ProcedimientoAUX"

StartLine = VBCode.ProcStartLine(ProcName, vbext_pk_Proc)
NumLines = VBCode.ProcCountLines(ProcName, vbext_pk_Proc)
VBCode.DeleteLines StartLine:=StartLine, Count:=NumLines

‘ ahora rellenamos las nuevas líneas
 VBCode.AddFromString "Public Sub ProcedimientoAUX()" & vbCrLf & vbCrLf _
      & textoIN & vbCrLf & "End Sub"

Set VBCode = Nothing
Set VBComp = Nothing
Set VBProj = Nothing
End Sub

Public Sub ProcedimientoAUX()
‘ necesario y vacío para inicializar la primera ejecución
‘ ya que si no, habrá errores de compilación/ejecución al no existir
End Sub

Lo que hace es, tras crear los componentes necesarios, borra el procedimiento (de hecho borra todas las líneas de él, desde su declaración hasta el final), que lo he llamado “ProcedimientoAUX” , con un parámetro de entrada que es la cadena de texto que incluye los comandos, y luego lo crea de nuevo, añadiendo en su cuerpo como líneas de código lo que hemos introducido en el campo de testo textoIN

Varias consideraciones/opciones a tener en cuenta:

Podemos usar el código en el formulario, en un módulo aparte, o bien usar una función

1 Procedimiento en el código del formulario

Es el código anterior, la colección VBComponents incluye todos los componentes que vemos en el editor de VB, en este caso, el nuevo procedimiento lo añadimos al formulario, con lo que tenemos acceso a los componentes de éste y ellos son los que pueden ejecutar el nuevo procedimiento creado

2. Procedimiento en el código de un módulo

En este caso, el acceso a los componentes de los formularios hay que hacerlos con sus referencias correctas.

Para verlo, creamos un módulo nuevo

Ahora comprobamos de nuevo los componentes que tenemos:

Podemos usar una estrategia u otra, pero la diferencia tiene que estar en la línea donde seleccionamos el componente y cuidado si en el código que introducimos hacemos referencia a algún componente de algún formulario (p.ej Forms(«»Formulario1″»)![textoIN])

Si elegimos el formulario:

Set VBComp = VBProj.VBComponents("Form_Formulario1")
Set VBCode = VBComp.CodeModule

Si elegimos el Módulo

Set VBComp = VBProj.VBComponents("Módulo1")
Set VBCode = VBComp.CodeModule

OJO, que si elegimos el formulario, tenemos que poner allí el procedimiento vacío para el inicio, para que no de error al compilar/ejecutar por primera vez

Nota: al ejecutar el debugger paso a paso, da un aviso en el momento en que inserta el código en el propio editor, darle a continuar

3. Usar una función

Nada impide que usemos una función

VBCode.AddFromString "Public Function FuncionAUX(in As String) As  String" & vbCrLf & vbCrLf _
    & textoIN & vbCrLf & "End Sub"
    

Ejecutar el Código

Como he comentado antes, necesitamos dos eventos para que todo funcione bien.

En el primer evento llamaremos al procedimiento del apartado anterior, para crear el código nuevo de nuestro procedimiento/función Auxiliar. En el segundo evento, la usaremos.

Para el primer evento podemos usar por ejemplo el evento after update del cuadro de texto textoIN

Simplemente llamamos a la función que borra y reescribe el procedimiento auxiliar.

Escribimos un texto y al pulsar enter se llama a la función

De modo que al continuar, comprobamos que se ha escrito la nueva procedimiento:

Antes procedimiento vacío:

Después, procedimiento sobreescrito (nos lo pone al principio del código)

En el segundo evento, en esta prueba simplemente ejecutamos el procedimiento (evento click del botonExe):

Y al pulsarlo:

Puede complicarse lo que se quiera:

Aplicación con Expresiones Regulares y reemplazo de texto

En Programas de mantenimiento de aeronaves, existen distintos tipos de unidades entre los datos fabricante y los que usa la organización. Aunque todo se puede hacer en Excel con fórmulas, puede automatizarse con expresiones regulares de una manera muy sencilla.

Se trata de realizar las siguientes conversiones, por ejemplo:

1291 Days convertir a 42 MO

10 YE convertir a 120 MO

Para ello usaré expresiones regulares.

Referencias a expresiones regulares en VBA:

https://sites.google.com/site/automatizacionexcel/expresiones-regulares

https://www.myonlinetraininghub.com/regex-regular-expressions-excel

Fundamental para las pruebas es el siguiente comprobador online:

https://regexr.com/

Hay que incluir la referencia VB expresiones regulares

Con esto, cambio un poco el formulario.

Añado un campo patrón(expresión regular a buscar) textoPatron, otro campo con la expresión (comandos ) a reemplazar(textoReemplazo), que será la que cree el código del procedimiento auxiliar, por tanto eliminamos el procedimiento after update de textoIN y se lo añadimos a este campo

En textoIN pondremos la cadena que queremos reemplazar, en textoOUT tendremos el texto reemplazado con la nueva función.

En este caso para la creación de procedimientos/funciones desde texto, usaré una función string, que simplemente va a devolver la cadena de sustitución, que calcula con los matches de la expresión regular operando con ellos, cuidado, que hay que cambiar las cosas para que todo sea consistente (el nombre cambia y el texto a insertar también, parámetro…)

‘ahora rellenamos las nuevas líneas

‘ ahora rellenamos las nuevas líneas
 VBCode.AddFromString "Public Function FuncionAUX(Matches As Object) As String" & vbCrLf & vbCrLf _
      & "FuncionAUX=&quot & textoReemplazo & vbCrLf & "End Function"

Ojo que ahora hay que crear al inicio la función vacía

Da igual que no pongamos parámetro, pues el borrado lo hace por el nombre.

El código del botón ahora es el siguiente:

Option Compare Database
Public Function FuncionAUX(Matches As Object) As String
‘ Vacío para inicializar
‘ 
End Function

Private Sub botonExe_Click()
Dim RegEx As RegExp
Dim Matches As Object
Dim resultado As String

Set RegEx = New RegExp
RegEx.Pattern = textoPatron
RegEx.Global = True ‘todos los match de la regexp

If RegEx.Test(textoIN) = True Then
    Set Matches = RegEx.Execute(textoIN)
    textoOUT = FuncionAUX(Matches)
End If

Set RegEx = Nothing

End Sub

Sub StringExecute(s As String)
Dim VBProj As VBProject
Dim VBComp As VBIDE.VBComponent
Dim VBCode As VBIDE.CodeModule
Dim ProcName As String
Dim StartLine As Long
Dim NumLines As Long

Set VBProj = VBE.ActiveVBProject
Set VBComp = VBProj.VBComponents("Form_Formulario1")
Set VBCode = VBComp.CodeModule

‘ Primero borramos lo que haya, esto borra todas las lineas, incluso las de definición y fin de la subrutina
ProcName = "FuncionAUX"

StartLine = VBCode.ProcStartLine(ProcName, vbext_pk_Proc)
NumLines = VBCode.ProcCountLines(ProcName, vbext_pk_Proc)
VBCode.DeleteLines StartLine:=StartLine, Count:=NumLines

‘ ahora rellenamos las nuevas líneas
 VBCode.AddFromString "Public Function FuncionAUX(Matches As Object) As String" & vbCrLf & vbCrLf _
      & "FuncionAUX=" & textoReemplazo & vbCrLf & "End Function"

Set VBCode = Nothing
Set VBComp = Nothing
Set VBProj = Nothing
End Sub


Private Sub textoReemplazo_AfterUpdate()
StringExecute (textoReemplazo)
End Sub

Muy sencillo,

Se inicializa y declara el objeto expresión regular, se indica el patrón a usar y si en nuestra cadena de entrada (textoIN) se encuentra el patrón, entonces lo sustituye por el valor devuelto por nuestra función creada, que lo único que va a hacer es operar con los resultados encontrados.

El objeto Matches es una colección con los elementos encontrados, que se agrupan con ()

Patrón: (abc) (xyz)

Objeto Matches: Matches(0): Primera coincidencia completa, o sea ‘abc xyz’

Matches(0).SubMatches(0): primera coincidencia del primer grupo, o sea ‘abc’

Matches(0).SubMatches(1): primera coincidencia del segundo grupo, o sea ‘xyz’

En mi ejemplo, vamos a tener patrones del tipo

XXXX Days definido por “\d+ Days” (uno o más dígitos, seguidos de espacio y seguidos de Days)

XXXXX YE definido por “\d+ YE” (uno o más dígitos, seguidos de espacio y seguidos de YE)

Si agrupamos los patrones “(\d+)( Days)” el primer submatch será el número y el segundo la cadena “ Days”. Igualmente con “(\d+)( YE)” el primer submatch será el número y el segundo la cadena “ YE”

Ahora ya podemos convertirlos fácilmente (redondeamos el resultado a 0 decimales y convertimos todo a string, para introducirlo en el textbox textOUT

CStr(Round(Matches(0).SubMatches(0) / 30.5, 0)) & " MO"
CStr(Round(Matches(0).SubMatches(0) * 12, 0)) & " MO"

Probamos:

Al pulsar enter (after_update) el el texto “Reemplazar por” se nos genera la función:

Que es lo que queríamos,

Ahora al pulsar el Botón ejecutar,

Todo funciona bien

Repitiendo el proceso con la otra expresión regular:

Yo esto lo uso en un bucle sobre todos los registro (datos) de sendas tablas con miles de registros (una por el fabricante y otra por la organización), y así poder convertir unidades rápidamente, en un solo paso, para poder comparar después ambos valores.

El código completo (formulario) de este segundo ejemplo:

Option Compare Database
Public Function FuncionAUX(Matches As Object) As String
‘ Vacío para inicializar
‘ 
End Function

Private Sub botonExe_Click()
Dim RegEx As RegExp
Dim Matches As Object
Dim resultado As String

Set RegEx = New RegExp
RegEx.Pattern = textoPatron
RegEx.Global = True ‘todos los match de la regexp

If RegEx.Test(textoIN) = True Then
    Set Matches = RegEx.Execute(textoIN)
    textoOUT = FuncionAUX(Matches)
End If

Set RegEx = Nothing

End Sub

Sub StringExecute(s As String)
Dim VBProj As VBProject
Dim VBComp As VBIDE.VBComponent
Dim VBCode As VBIDE.CodeModule
Dim ProcName As String
Dim StartLine As Long
Dim NumLines As Long

Set VBProj = VBE.ActiveVBProject
Set VBComp = VBProj.VBComponents("Form_Formulario1")
Set VBCode = VBComp.CodeModule

‘ Primero borramos lo que haya, esto borra todas las lineas, incluso las de definición y fin de la subrutina
ProcName = "FuncionAUX"

StartLine = VBCode.ProcStartLine(ProcName, vbext_pk_Proc)
NumLines = VBCode.ProcCountLines(ProcName, vbext_pk_Proc)
VBCode.DeleteLines StartLine:=StartLine, Count:=NumLines

‘ ahora rellenamos las nuevas líneas
 VBCode.AddFromString "Public Function FuncionAUX(Matches As Object) As String" & vbCrLf & vbCrLf _
      & "FuncionAUX=" & textoReemplazo & vbCrLf & "End Function"

Set VBCode = Nothing
Set VBComp = Nothing
Set VBProj = Nothing
End Sub


Private Sub textoReemplazo_AfterUpdate()
StringExecute (textoReemplazo)
End Sub

Y los archivos con ambas bases de datos:

ExeFromString Descarga

ExeFromString_RegEx Descarga

En EXCEL

Sólo unas pequeñas modificaciones, en la definición de objetos del editor de visual basic y el el evento que crea el procedimiento desde el texto, que en este caso lo vamos a hacer cuando la celda A1 cambia (pulsando enter)

Existe mucha información sobre Excel disponible en internet al buscar «vba execute string as command» o similares

Para empezar hay que permitir en el centro de confianza el acceso al proyecto VBA. Si no tendremos un error:

Es necesario habilitar la opción en el centro de confianza

Esta es la hoja de cálculo, muy simple, con un botón al que se le asigna la macro definida en el editor de código

El código completo es el siguiente:

Public Sub ProcedimientoAUX()
‘ vacío


End Sub

Sub boton()
ProcedimientoAUX

End Sub

Sub StringExecute(s As String)
Dim VBProj As VBProject
Dim VBComp As VBIDE.VBComponent
Dim VBCode As VBIDE.CodeModule
Dim ProcName As String
Dim StartLine As Long
Dim NumLines As Long

Set VBProj = Application.VBE.ActiveVBProject ‘ .ActiveVBProject
Set VBComp = VBProj.VBComponents("Hoja1")
Set VBCode = VBComp.CodeModule

‘ Primero borramos lo que haya, esto borra todas las lineas, incluso las de definición y fin de la subrutina
ProcName = "ProcedimientoAUX"

StartLine = VBCode.ProcStartLine(ProcName, vbext_pk_Proc)
NumLines = VBCode.ProcCountLines(ProcName, vbext_pk_Proc)
VBCode.DeleteLines StartLine:=StartLine, Count:=NumLines

‘ ahora rellenamos las nuevas líneas
 VBCode.AddFromString "Public Sub ProcedimientoAUX()" & vbCrLf & vbCrLf _
      & s & vbCrLf & "End Sub"

Set VBCode = Nothing
Set VBComp = Nothing
Set VBProj = Nothing
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Target.Worksheet.Range("A1")) _
           Is Nothing Then
    StringExecute (Range("A1"))
End If
    
End Sub

Y el archivo:

test Descarga

VBA ejecutar cadena de texto (string) como comandos (en un procedimiento/función). Aplicación: Reemplazar con expresiones regulares