Acceder

Trucos y tretas en Excel VBA para programadores

Este post no está hecho para novatos que no sepan nada del macros de Excel, sino aquellos que saben algo de programación de Visual Basic. Si eres neófito en macros de Excel en Visual Basic, deberías ver mi post Excel VBA y macros: Una herramienta muy útil para trabajar hojas de cálculo (1).  Me han de perdonar la manera en que aparece el código, pero es un problema en la manera en que aparece el código HTML para desplegar código con indentación en este sitio.

Especialmente en finanzas o en bolsa, pero también en negocios de todo tipo, programar macros de Excel suele ser sumemante útil y por eso he querido deicar este post a transmitir algunos trucos para ahorrarte tiempo.  Cuando programamos código para macros de Excel VBA a menudo nos encontramos con que los detalles de cómo invocar tal o cual cosa de Excel termina siendo muy críptica y debes navegar por foros, tutoriales o incontables páginas ininteligibles de Microsoft que no te responden lo que andas buscando.

Si sabes Visual Basic, lo más lógico es que para elaborar macros, trates de grabar una macro primero, y luego empiezas a ver el código de muestra para ver cómo hacer determinadas cosas.  En este post se pretende proveer trucos de programación en Excel VBA para efectuar tareas que no se pueden derivar del código grabado en una macro en Excel.  Estos trucos son el resultado de resolver una serie de problemas que en su momento significaron un dolor de cabeza.

Obtener valor de una celda


    Cells(4,5)=23
    x = Cells(4,5)+1

Este ejemplo inserta el valor 23 en la celda de la fila 4 y columna 5.  Luego la variable x toma ese valor y le suma 1.  Cabe añadir que cada vez que llamamos una celda, una cantidad muy grande de código de Excel es invocada, de modo que si piensas trabajar con muchas celdas o muchas veces con la misma celda, es mejor que pases los valores a variables, en lugar de llamar a la celda en sí misma.

    Cells(2,3).Select
    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter

En este ejemplo, la celda de la fila 2 columna 3 es seleccionada, y la selección luego es objeto de centrado vertical y horizontal usando Selection.

Usando rangos

Pasar de la notación de fila y columna a la de celdas suele ser un poco incómodo.  Normalmente para seleccionar un rango usaríamos algo como esto:


    Range("A5:B8").Select

Como has visto, lo que está procesando la función Range() entre comillas es una hilera de caracteres. Para efectos de escribir código, podemos usar esta expresión equivalente:


    Range(Cells(5,1),Cells(8,2)).Select

Convertir números en letras (pasar de la notación fila 5 columna 1 a "A5") suele ser sumamente incómodo. Para ello he construido una función que hace el trabajo.


  Private Function GetColumnLetter(index) As String
    Dim FL, LL As Long
    
    GetColumnLetter = ""
    LL = (index - 1) Mod 26 + 65
    If LL > 63 Then GetColumnLetter = Chr(LL)
    If index > 26 Then
        FL = Int((index - 1) / 26) + 64
        GetColumnLetter = Chr(FL) & GetColumnLetter
    End If

  End Function

La función convierte el número en letras, en caracteres. Cabe anotar que sólo sirve para llegar hasta columna ZZ, de modo que si se pasa de esa columna, esta función no te servirá. Para llamar esta función con las mismas coordenadas (5,1) y (8,2) harías algo como esto para convertir en hilera:


  Range(GetColumnLetter(5) & Format(1,"0") & ":" & GetColumnLetter(8) & Format(2,"0")).Select

Como habrás notado he usado la función Format() que evita que se agreguen espacios al convertir de número a hilera, como pasaría con la función Str().

Ventana emergente para que el usuario ingrese un rango

 

Si quieres que aparezca una ventana emergente con la cual puedas pedir al usuario que seleccione un rango de datos, puedes usar el código que se muestra a continuación.  Lo que se hace es declarar un objeto de tipo Range llamado celdasSeleccionadas.  A ese objeto se le asigna el resultado de la función Application.InputBox que despliega la ventana emergente que solicita la información al usuario.  Si el usuario no ingresa ningún rango, el valor que tendrá el objeto es Nothing.

El comando On Error Resume Next indica que si ocurre un error, entonces ignore el error y proceda conla siguiente instrucción. El comando On Error Goto 0 indica que se cancela la detección de errores.  El comando Msgbox despliega información en una ventana emergente.

He aquí el código:


    Dim celdasSeleccionadas As Range
    
    On Error Resume Next
    Set celdasSeleccionadas = Application.InputBox(prompt:="Seleccione un rango de celdas", Type:=8)
    On Error GoTo 0
    If celdasSeleccionadas Is Nothing Then
        'Aquí va el código que se ejecuta si no se selecciona ninguna celda.
        'En este caso se despliega una ventana emergente con un mensaje.
        MsgBox "No se ha seleccionado ninguna celda."
    Else
        'Aquí va el código que se ejecuta si se se seleccionó celdas.
        'En este caso se despliega una ventana emergente con el resultado de la suma de celdas.
        MsgBox Application.WorksheetFunction.Sum(celdasSeleccionadas.Cells)
    End If

Como habrás notado en este código lo que se hace si el usuario digita un código es desplegar el valor de la suma de todos los valores del rango usando la función Application.WorksheetFunction.Sum, pero tú puedes hacer que haga algo distinto.

Si quisieras obtener el valor contenido en el rango seleccionado puedes usar este código.


    MiVariable = celdasSeleccionadas.Value

En este caso el contenido del rango se almacena en una variable llamada MiVariable.

Acelerar la ejecución

Cuando un programa corre, todo lo que tiene que ver con actualización de pantalla es sumamente lento, y lo mejor es desconectar toda actualización de video.  Para ello debes agregar el siguiente código al inicio de la macro.


    Application.ScreenUpdating = False
    Application.DisplayStatusBar = True
    Application.StatusBar = "Iniciando..."

Y este código al final.


    Application.ScreenUpdating = True
    Application.StatusBar = "Ejecución terminada."

Application.ScreenUpdating se encarga de encender y apagar la actualización de video. Application.DisplayStatusBar = True habilita la barra de estado que está en la parte inferior de la ventana de Excel, y allí despliegas mensajes asignando una hilera a Application.StatusBar. La idea de usar la barra de estado es que si la ejecución de la macro tarda mucho, es bueno que el usuario sepa que la macro está trabajando, y que no se ha quedado atascada. 

No hay nada que fastidie más que tener un programa atascado frente a uno.  Por eso sería bueno que dentro de la macro agregues otros mensajes donde el usuario pueda ver que la macro va caminando, pero si pones mensajes con demasiada frecuencia eso ralentizará la ejecución, porque como dijimos, la actualización de video consume mucho tiempo de la máquina.

Esto es especialmente útil cuando vas a correr una macro que pasa de un libro de Excel a otro o que se mueve mucho a lo largo de las páginas o celdas de manera muy intensiva.

Obtener posición de la última celda

Cuando tienes hojas de Excel de tamaño variable y necesitas ubicar la última celda ocupada (la que está más abajo y más a la derecha), puedes usar lo siguiente:


    ActiveCell.SpecialCells(xlLastCell).Select
    UltimaFila = ActiveCell.Row
    UltimaColumna = ActiveCell.Column

Si buscas en Internet acerca de cómo obtener posición de la última celda, encontrarás maneras muy diversas.  Sin embargo, esta es la mejor que he encontrado.  Si agregas valores más allá de la última celda, el valor se actualiza.  El único problema es que si borras filas o columnas, este número no cambiará, sino hasta que abras de nuevo el archivo de Excel.  Sin embargo eso es mejor que nada, y puedes verificar si encuentras celdas en blanco, así que eso no debería ser problema.

Obtener los nombres de libros de Excel abiertos.

En ocasiones ocupas una lista de los libros de Excel que están abiertos.  Para ello te vales del objeto Workbooks de Excel.


    Dim NombreDeArchivo() as String

    Redim NombreDeArchivo(Workbooks.Count-1)
    For i = 1 To Workbooks.Count
        NombreDeArchivo(i-1) = Workbooks.Item(i).Name
    Next i

El arreglo NombreDeArchivo almacenará los nombres de los archivos de Excel que están abiertos en un momento dado.

Obtener la lista de páginas del libro de Excel

Puedes usar el siguiente código.


    Dim NombreDePagina()
    Dim Cantidad as Long

    Cantidad = Sheets.Count
    redim NombreDePagina(n)
    For i = 1 To Cantidad 
        NombreDePagina(i) = Sheets(i).Name 
    Next i 

Los nombres se almacenan en el arreglo NombreDePagina.

Obtener los nombres de las gráficas (charts) que hay en una página de Excel

Hay ocasiones en que ocupas buscar todas las gráficas contenidas en una página de Excel.  para ello te vales de ActiveSheet.ChartObjects


    Dim NombreDeChart()

    Redim NombreDeChart(ActiveSheet.ChartObjects.Count-1)
    For i = 1 To ActiveSheet.ChartObjects.Count
        NombreDeChart(i-1) = ActiveSheet.ChartObjects(i).Name
    Next i

El arreglo NombreDeChart contendrá los nombres de las gráficas de la hoja de Excel en que nos encontramos.

Obtener el nombre del libro de Excel en que te encuentras actualmente

Existen algunas situaciones donde ocupas saber el nombre del libro de Excel en que te encuentras actualmente.


    LibroActual = ActiveWorkbook.Name

El nombre de la hoja actual será guardado en la variable LibroActual.

Cambiar de libro o de hoja

Si deseas pasarte a otro libro abierto u otra hoja, puedes usar el siguiente código.


    Workbooks("MiLibro.xls").Activate
    Sheets("Sheet1").Select

Este código hará que te pases al libro de Excel MiLibro.xls y a la página Sheet1 de ese libro. Como ves, el valor entre comillas puede ser reemplazado por una variable tipo String si lo deseas.

Cómo lidiar con libros de Excel protegidos por password


    If ActiveSheet.ProtectContents Then
        ActiveSheet.Unprotect Password:="XYZ"
    End If

Este código se encargará de eliminar la protección contra password.  En este caso el password es XYZ.  Cuando trabajas con hojas protegidas con password, obtendrás errores al tratar de acceder a material protegido y por eso debes desproteger la hoja para trabajar en ella.

Enviar un correo con archivo adjunto

Si quieres generar un correo y pegarle un archivo adjunto, puedes usar el siguiente código:


    Dim oLook As Object
    Dim oMail As Object
    
    Set oLook = CreateObject("Outlook.Application")
    Set oMail = oLook.createitem(0)
    With oMail
        .To = "[email protected]"
        .body = "Este es el cuerpo del mensaje. Ver archivo adjunto"
        .Subject = "Asunto. Envio de archivo adjunto"
        .Attachments.Add ("C:\MiArchivo.xlsx")
        .Send
    End With
   
    Set oMail = Nothing
    Set oLook = Nothing

Este código buscará el archivo MiArchivo.xlsx ubicado en C:\, construirá el mensaje y lo enviará a la dirección [email protected].  La lógica de este código usa un poco de programación orientada a objetos.  oLook es un objeto que se encarga de manejar la aplicación Outlook, y oMail es un objeto que se encarga de manejar el correo.

Ocultar una hoja e impedir que el usuario la vea

Si quieres ocultar una hoja de Excel para que el usuario no la pueda ver, y no pueda mostrar su contenido usa este código:


    Set hide_sheet = Sheet1
    hide_sheet.Columns.Hidden = True
    hide_sheet.Visible = False

El código lo que hace es ocultar todas las columnas de la hoja Sheet1 y luego oculta la hoja.  Cabe notar que Sheet1 no es el nombre de la hoja visible para el usuario, sino el nombre del objeto que aparece en el editor de código de Excel VBA fuera de los paréntesis.

Crear un botón en una hoja de cálculo

Si quieres crear un botón:

   ActiveSheet.Buttons.Add ActiveCell.Left, ActiveCell.Top, ActiveCell.Width, ActiveCell.Height

El código crea un botón sobre la celda en la que estás ubicado actualmente.

Cargar, refrescar, salvar y cerrar una hoja de cálculo

Si quieres efectuar esas tareas:


    Workbooks.Open Filename:="C:\MiArchivo.xlsx", UpdateLinks:=3
    Workbooks("MiArchivo.xlsx").RefreshAll
    ActiveWorkbook.Save
    ActiveWindow.Close

El código efectúa las cuatro tareas descritas en ese orden.

Cómo usar el portapapeles

El portapapeles no viene incluido en la versión básica de Excel VBA, así que primero tenemos que configurar algunas cosas.  

  • Ve al editor de Visual Basic (Alt F11).
  • Selecciona Tools > References


     
  • Activa Microsoft Forms Object Library y presiona Ok.

Si no aparece Microsoft Forms 2.0 Object Library en la lista, quizás quieras usar el botón Browse para buscar el archivo FM20.DLL que se encuentra en la ubicación en disco duro que se muestra en la imagen.

Ahora que ya configuramos todo, veamos el código.
Primero deberías declarar el objeto de portapapeles (en este caso le llamaremos doClip)

    Dim doClip As DataObject

Este sería el método para crear el portapapeles en memoria.

    Sub CrearClipboard()
        Set doClip = New DataObject
    End Sub

 

Con este método puedes destruir el objeto portapapeles en memoria para liberar espacio.

    Sub DestruirClipboard()
        Set doClip = Nothing
    End Sub

 

Con este método copias un texto al portapapeles.


    Sub CopiarAlClipboard(sTexto As String)
        doClip.Clear
        doClip.SetText sTexto
        doClip.PutInClipboard
    End Sub

Con esta función extraes el texto del portapapeles. 


    Function ClipboardTexto() As String
        doClip.GetFromClipboard
        ClipboardTexto = doClip.GetText
    End Function

Cómo configurar área de impresión

Para configurar el área de impresión ocupas la orientación portrait o landscape.  He puesto las muestras de las líneas para ambos casos, aunque tú sólo ocuparías una.  Si vas a ajustar a una o más páginas horizontal o verticamente, primero necesitas poner el modo Zoom en apagado y luego puedes configurar los ajustes.  Y también debes delimitar el área de impresión que en este ejemplo es desde A1 hasta H25.


    ActiveSheet.PageSetup.Orientation = xlPortrait
    ActiveSheet.PageSetup.Orientation = xlLandscape

    ActiveSheet.PageSetup.Zoom = False
    ActiveSheet.PageSetup.FitToPagesWide = 1
    ActiveSheet.PageSetup.FitToPagesTall = 1
    ActiveSheet.PageSetup.PrintArea = "A1:H25"

Cómo crear una lista de archivos y directorios

Para obtener una lista de archivos, ve al siguiente sitio:  List all files in a directory.

Para listar archivos y subdirectorios ve al sitio List folders and subdirectories.

Otros trucos

Existen trucos para optimizar el desempeño de Visual Basic, tales como:

  • Evitar al máximo el uso de propiedades de controles.  Las celdas de Excel son controles. Los forms (formulario, ventana) y los controles sobre un form son controles. Usar propiedades de controles agrega mucha carga de procesamiento innecesario.  Por ejemplo, el valor de una celda es una propiedad del control llamado celda.  El texto de un control de texto es una propiedad. Hay que minimizar la referencia a controles hasta donde sea posible, pasando los valores de los controles a una variable, se trabaja todo en la variable, y al final se actualiza la propiedad del control con el valor final de la variable.
  • Apaga la actualización de video, porque el tiempo de actualizar la pantalla innecesariamente consume enormes cantidades de tiempo de procesamiento.
  • Evitar el uso de variables tipo Variant.  Estas variables generalmente aparecen cuando no se declaran las variables.  Para hacer que el Visual Basic muestre un error cuando vea variables no declaradas, puedes poner esto al inicio de cada módulo.

          Option Explicit

 Espero que estos trucos te sirvan, porque a mí me han servido mucho.

38
¿Te ha gustado el artículo?

Si quieres saber más y estar al día de mis reflexiones, suscríbete a mi blog y sé el primero en recibir las nuevas publicaciones en tu correo electrónico.

  1. #40
    27/12/20 19:40
    Hola espero me puedan ayudar.

    Tengo un textbox en el que al dar click en un comandbuton parace la pregunta numeto uno de mi encuesta, necesito que al registrar la respuesta con otro comanbuton se cambie en automático a la siguiente pregunta y que las respuestas se guarden en los ranfos correspondientes a la pregunta mostrada en el textbox. ¿esto es posible y como sería? tengo 10 preguntas ordenadas una en las cedssa d, e, f, g, h, i, j, k, l y M; al iniciar la encuesta aparece en el textbox la pregunta de la celda D.

  2. Nuevo
    #39
    25/05/20 12:44
    ¿No es mejor emplear la Address que tu función?
  3. en respuesta a Cryztina
    -
    Top 100
    #38
    15/05/20 16:35
    Busca en google "excel vba process selected cells"
  4. Nuevo
    #37
    15/05/20 01:30
    Hola espero puedan ayudarme. Quiero correr una macro en la misma hoja indefinidamente, en las celdas que se especifiquen previamente. Ejemplo.. Seleccione (A1:B12) ahi presionar boton y correr macro... Despues seleccionar cualquier otro rango de celdas y correr la misma macro. La macro se xorreria despues de seleccionar las celdas.
  5. en respuesta a Emallarach
    -
    Top 100
    #36
    30/04/20 18:27
    Cuando haces una referencia literal a celdas o columnas, la ejecución de código se verá afectada.
    Sugiero buscar en google "vba relative cell reference" para que las referencias sean relativas.
    Ahora que si insertas algo y la posición relativa cambia, vas a tener que hacer una busqueda de la columna que vas a usar, y luego convertir el resultado de esa busqueda a letras para alimentar la información de la columna.  Todas esas tareas tendrías que encapsularlas en funciones o procedimientos genéricos para que no requiera de muchas lineas en el programa principal y la depuración se facilite en el futuro.
    Como regla general, en el largo plazo todo cambia en las hojas de Excel, sobre todo si hay muchos usuarios usando el formato. Si haces el código muy literal, con el tiempo vas a tener que hacer mucho mantenimiento.
  6. en respuesta a Comstar
    -
    #35
    30/04/20 12:02
    Buenas,
    Con lo que indica en el post no ser ver como solucionar el problema.
    Básicamente me gustaría saber si en lugar de la siguiente instrucción:
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=AStr
    podría indicar algo como lo siguiente:
     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Sheets("Sheet2").Range("B2:B" & Lrow)

    Paralelamente hay alguna manera en vba para que si inserto una columna/fila el código no quede afectado?

    Gracias
  7. en respuesta a Fmenocalc
    -
    Top 100
    #34
    28/04/20 17:24
    Si usas cells(fila,columna) puedes hacer referencia a cualquier celda, y puedes variar el numero de fila a tu antojo. 
    fila y columna son numeros enteros.
  8. en respuesta a Emallarach
    -
    Top 100
    #33
    28/04/20 17:22
  9. #32
    28/04/20 12:17
    Buenas,
    Estoy haciendo una macro donde en varias celdas hay un desplegable que se gestiona en una hoja diferente.

    En la siguiente rutina donde se asigna el desplegable me gustaría asignar en lugar de una variable ( AStr ) , que solo puede contener 255 caracteres, unas celdas de otra hoja (Sheets("hoja2").Range("B2:B" & Lrow), es posible?
      
    Sub AddData()
    Dim Lrow As Single
    Dim AStr As String
    Dim Value As Variant
     
    Lrow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
     
    For Each Value In Range("A1:A" & Lrow)
        AStr = AStr & "," & Value
    Next Value
     
    AStr = Right(AStr, Len(AStr) - 1)
     
    With Worksheets("Sheet1").Range("C2").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= xlBetween, Formula1:=AStr     'En lugar de Astr asignar  Sheets("hoja2").Range("B2:B" & Lrow) 
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    End Sub 

    Gracias,
  10. Nuevo
    #31
    23/06/19 22:28

    Muy buenas tardes queria saber si alguien me puede ayudar resulta que estoy trabajando en un formulario de visual basic el cual quiero que me lea la informacion de una celda de excel en el formulario lo cual ya lo e conseguido con el siguiente codigo
    Private Sub CommandButton1_Click()

    TextBox1.Value = ActiveSheet.Range("A10").Value
    TextBox8.Value = ActiveSheet.Range("F10").Value
    TextBox11.Value = ActiveSheet.Range("D10").Value
    TextBox10.Value = ActiveSheet.Range("B10").Value
    TextBox9.Value = ActiveSheet.Range("C10").Value
    End Sub
    pero ahora lo que deseo es que cada vez que le de click en un boton me lea la informacion de la siguiente celda hacia abajo por favor les pido su ayuda si alguien sabe como hacerlo

  11. Nuevo
    #30
    28/05/19 20:04

    He creado un formulario pero a la hora de visualizarlo no se ven todos los campos.
    ¿Qué debo hacer para crear una barra y desplazarme para ver el resto del formulario?

  12. en respuesta a Hugocities
    -
    Top 100
    #29
    12/05/19 06:44

    No entiendo muy bien la estructura de lo que me tratas de describir.

  13. Nuevo
    #28
    11/05/19 00:14

    gracias por tu material, es EXCELente; Yo no soy programador, pero entiendo de sistemas y me gustan los compu ya que estudie Sistemas; por tanto te consulto: es un listado de personas, cuando alguien cumple años, sus datos van a parar a otro listado en otra hoja. Cuando abro Excel, el programa hace su trabajo, busca los cumpleañeros y los envía a otra hoja. Lo hago y vb hace su trabajo mientras el programa corre por el Formulario en cuestión, pero cuando abro el libro, no funciona. Me podrías dar una somera idea de cómo puedo hacer esto? gracias.

  14. en respuesta a Jeuv1992
    -
    Top 100
    #27
    13/04/19 21:07

    Contactar a Microsoft tech support.

  15. Nuevo
    #26
    12/04/19 20:07

    Hola cerré un archivo excel con la ventana de códigos abierta y me salio que se me depuro,lo cual ahora no me deja abrir la ventana de códigos, que debo hacer para solucionar eso?

  16. Nuevo
    #24
    11/07/18 16:32

    Buen día, estoy buscando como asignar a mi código en el asunto (o subject) el contenido de una celda, en este programa lo que se hace es enviar desde excel con una macro cierto rango de información de una hoja, pero para que el asunto sea el que esta en una celda es que quiero saber como asignarlo a una variable.

  17. #23
    15/05/18 17:32

    Excelente Publicación, muchas gracias por este aporte.

  18. en respuesta a Marcosjvj.8
    -
    Nuevo
    #22
    09/03/18 04:41

    Puedes generar la ino en xml y en cuanto el usuario refresque la hoja o entre a ella aprovechas para actualizar ya que no puedes ejecutar ninguna macro en segundo plano.

  19. en respuesta a Jose Rojas
    -
    Top 100
    #21
    08/02/18 13:40

    Si grabas una macro, realizando la labor manualmente, tendrás el código que buscas.