Archive

Posts Tagged ‘Macro’

Solución a: Excel no me ejecuta las macros de mi archivo

August 18, 2012 2 comments

Existen ocasiones en que corremos un archivo de Excel con macro y al momento de abrirlo, Excel lanza un error con el texto:

Parte quitada: Parte /xl/vbaProject.bin.  (Visual Basic para Aplicaciones (VBA))

Y aunque se abre el archivo, nuestras macros no se ejecutan. Incluso en la pestaña de Programador, los botones correspondientes a Visual Basic está deshabilitados.

La razón es que cuando se instaló Office no se intaló el VBA, por lo que no corre las macros, aunque le cambiemos en las opciones de seguridad.

Si vamos a instalar Excel por primera vez debemos elegir una instalación personalizada y añadir todas las características de Office. Si ya está instalado, se tendrá que correr el programa de instalación o modificar la instalación y elegir Agregar o quitar funciones. Al final elegimos Características compartidas de Office > Visual Basic para Aplicaciones o damos click en la opción anterior y seleccionamos Ejecutar todo desde mi PC.

Imagen del ejemplo

image

Categories: Macros, Trucos, vba Tags: , , ,

Prohibir el acceso a las macros de Excel con Alt + F11

August 14, 2012 1 comment

En algunas ocasiones, cuando desarrollamos nuestras aplicaciones en Excel, deseamos que los usuarios no puedan entrar a nuestras macros mediante la combinación de celdas Alt + F11. Resulta imprescindible también asignarle una contraseña a nuestras macros, para tener bloqueo total.

Funcionamiento

Al abrirse el archivo, se ejecutará la macro ProhibirF11, la cual asignará al evento OnKey la macro de MensajeF11 para que se lance un texto indicando la prohibición de esa combinación de teclas. Al salir del archivo, quedará anulada la prohibición.

image

Se mostrará un mensaje al momento de querer entrar a las macros.

Código de la macro

Dentro de nuestro objeto ThisWorkBook, incluímos el código:

Private Sub Workbook_Open()
Call ProhibirF11
End Sub
'
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call PermitirF11
End Sub

Y dentro de un módulo, incluímos el código:

Sub ProhibirF11()
Application.OnKey "%{F11}", "MensajeF11"
End Sub
'
Sub MensajeF11()
MsgBox "No se puede acceder al IDE de VBA", vbExclamation, "EXCELeINFO"
End Sub
'
Sub PermitirF11()
Application.OnKey "%{F11}"
End Sub
Categories: Excel, Macros, Trucos, vba Tags: , , , , ,

Rellenar celdas en blanco en Excel

Todos nos hemos encontrado alguna vez con archivos que contienen celdas en blanco y se nos vuelve necesario ingresarles un valor para no tener celdas vacías o simplemente para completar datos faltantes.

Método tradicional

El primer método implica usar las funciones que ofrece Excel para elegir las celdas en blanco y después ingresarle un valor.

La manera de elegir las celdas en blanco es:

Inicio > Buscar y seleccionar > Ir a…

Aquí Excel elegirá sólo las celdas que no tengan datos y así podremos insertarle un valor. Una vez elegidas las celdas, ingresamos el valor deseados y posteriormente pulsamos las teclas Ctrl + Enter para que sean copiadas a todas.

image

Excel nos permité elegir celdas con determinado criterio.

Método con macros vba

El segundo método sigue la misma lógica que el primero, pero sacándole provecho a las macros y añadiendo esta funcionalidad a otras aplicaciones que pretendamos realizar. El ejemplo nos pedirá un valor a ingresar.

Código de la macro

Sub RellenarCeldas()
Dim Celdas As Range
Dim Valor As Variant
Valor = InputBox("Ingresa el valor", "EXCELeINFO")
Selection.SpecialCells(xlCellTypeBlanks).Select
For Each Celda In Selection
    Celda.Value = Valor
Next Celda
End Sub

image

Excel solicitará el valor a insertar.

Video tutorial # 1 – Uso de BUSCARV con vba

February 5, 2012 10 comments

Comparto el primer video tutorial de nuestro Blog, el cual en esta ocasión explico la manera de utilizar la función BUSCARV con lenguaje vba.

Código usado en el ejemplo

Private Sub CommandButton1_Click()
Dim Nombre As String
Dim Rango As Range
Set Rango = Sheets(1).Range("A1:B4")
Nombre = Application.WorksheetFunction.VLOOKUP(Me.TextBox1.Value, Rango, 2, 0)
Me.TextBox2.Value = Nombre
End Sub

Video tutorial

Video tutorial # 1–Uso de Buscarv con vba

Cálculo en barra de estado en Excel 2003 similar a Excel 2010

December 22, 2011 1 comment

De los cambios positivos que tuvo Excel 2007 y 2010 es que ahora la barra de estado permite mostrar disintos cálculos en una misma vista, tal como Suma, Promedio, Recuento, etc.

En Excel 2003 y anteriores había que elegir, mediante un menú, sólo un cálculo para que sea mostrado en la barra de estado.

Cómo funciona ?

El siguiente evento permite mostrar distintos cálculos en la barra de estado a medida que elegimos un rango de celdas. Para esto, debemos entrar a nuestro IDE de vba y entrar el objeto ThisWorkbook. Añadiremos el evento Workbook_SheetSelectionChange para que cada vez que hacemos una selección de rangos, nos muestre distintos cálculos.

Código

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Suma, Promedio, Cuenta, Maximo, Minimo
    'Si al menos están elegidas dos celdas, muestra los cálculos
    If Selection.Cells.Count <> 1 Then
        On Error Resume Next
        Suma = "Suma: " & Application.WorksheetFunction.Sum(Selection)
        Promedio = "Promedio: " & Application.WorksheetFunction.Average(Selection)
        Cuenta = "Cuenta: " & Application.WorksheetFunction.CountA(Selection)
        Maximo = "Máximo: " & Application.WorksheetFunction.Max(Selection)
        Minimo = "Mínimo: " & Application.WorksheetFunction.Min(Selection)
        'Muestra en la barra de tareas los cálculos antes definidos
        Application.StatusBar = Suma & "    " & Promedio & "   " & _
                                Cuenta & "   " & Maximo & "     " & Minimo
    Else
        Application.StatusBar = False
    End If
    On Error GoTo 0
End Sub

Imagen de ejemplo

image

Categories: Excel, Trucos, vba Tags: , , ,

Controlar comportamiento de la tecla ESC (Cancelar) con EnableCancelKey en Excel

December 9, 2011 Leave a comment

Complementando mi Lista incompleta de tips para programar macros en Excel, añado un procedimiento llamado EnableCancelKey dependiente del objeto Application, el cual nos permite controlar el comportamiento de la tecla Cancelar en nuestras macros.

Cancelar macro usando ESC con mensaje personalizado

Si lo que deseamos es que cuando nuestra macro dure demasiado, el usuario tenga la opción de Cancelar la acción, pero con la salvedad de que podemos personalizar un mensaje de aviso que sustituya al clásico error de vba cuando cancelamos una macro.

Código

Sub EnableCancelKey1()
    Dim i As Double
    'Cuando se genere un error nos mandará a la etiqueta ManejoError
    On Error GoTo ManejoError
    Application.EnableCancelKey = xlErrorHandler
    'Damos la opción al usuario de poder cancelar la acción de la macro
    MsgBox "El siguiente código puede tomar mucho tiempo: " & _
           "presionar ESC para cancelar", vbInformation, "EXCELeINFO"
    For i = 1 To 100000
        ActiveSheet.Range("A" & i).Value = i
    Next i
    '
ManejoError:
    If Err = 18 Then
        MsgBox "La acción se ha cancelado", vbExclamation, "EXCELeINFO"
    End If
End Sub

Evitar el uso de la tecla ESC

Existe también la opción de que impidamos que el usuario haga uso de la tecla ESC para cancelar la macro, pero tal acción debe programarse con cuidado, ya que si se corre una macro con un ciclo infinito, no se podrá cancelar la macro.

Código

Sub EnableCancelKey2()
    Dim i As Double
    'Deshabilitamos el uso de la tecla ESC
    Application.EnableCancelKey = xlDisabled
    MsgBox "La siguiente macro no podrá cancelarse", vbInformation, "EXCELeINFO"
    For i = 1 To 100000
        ActiveSheet.Range("A" & i).Value = i
        Application.StatusBar = Range("A" & i).Value
    Next i
    Application.StatusBar = False
End Sub

Ejecutar consulta SQL desde Excel

November 8, 2011 15 comments

Hace algunos días me surgió la necesidad de extraer datos de unas bases de SQL a Excel, a lo cual me di a la tarea de buscar información al respecto. Cuál fue mi sorpresa que no existe mucha información al respecto. Encontré un ejemplo de ejecutar consulta SQL desde Excel del cual tomé el código que realiza la función y adecué un formulario para que sea más amigable la consulta.

Cómo funciona?

El ejemplo funciona si se tiene una cadena de conexión a SQL, y aunque no está probado para MySql no dudo que también funcione, sólo modificando la cadena.

Se muestra un formulario donde se especifica el nombre del servidor, de la base de datos, así como el usuario y la contraseña, además de un espacio para se que introduzca la sentencia SQL a ejecutar.

image

Consideraciones

Se requieren conocimientos básicos de SQL para armar la consulta, así como un servidor SQL local o en red.

Código

Private Sub CommandButton1_Click()
'Llamas la función Ejecutar
Cells.ClearContents
Call Ejecutar(Sheets(2).Range("consulta"), "Hoja1")
End Sub
Function Ejecutar(Sql As String, Hoja As String)
    On Error GoTo ErrorHandler
    Dim cn As Object
    ' crea un objeto Connection
    Set cn = CreateObject("ADODB.Connection")
    ' IMPORTANTE: Indicar la cadena de conexión a usar
    servidor = Sheets(2).Range("servidor")
    base = Sheets(2).Range("base")
    Usuario = Sheets(2).Range("usuario")
    pass = Sheets(2).Range("pass")
    Conexion = "Provider=SQLOLEDB.1;" & _
               "Password=" & pass & ";" & _
               "Persist Security Info=True;" & _
               "User ID=" & Usuario & ";" & _
               "Initial Catalog=" & base & ";" & _
               "Data Source=" & servidor
    'cn.ConnectionString = "Provider=SQLOLEDB.1;Password=s3cr3t0;Persist Security Info=True;User ID=sa;Initial Catalog=Cobranza;Data Source= 192.168.2.6"
    cn.ConnectionString = Conexion
    ' verifica que los parámetros no estén vacios
    If Sql <> vbNullString And Hoja <> vbNullString Then
        ' variable para al rec de ado
        Dim rst As Object
        ' abre la conexión a la base de datos
        cn.Open
        ' crea un nuevo objeto recordset
        Set rst = CreateObject("ADODB.Recordset")
        ' Ejecuta el sql para llenar el recordset
        rst.Open Sql, cn, 1, 3
        ' variables para los indices de las filas y columnas
        c = 0
        f = 0
        ' recorre las columnas, añade el nombre del campo al encabezado
        For i = 0 To rst.Fields.Count - 1
            Sheets(1).Range(Chr(i + 65) & f + 1).Value = rst.Fields(i).Name
        Next
        f = f + 1
        ' recorre todo el recordset hasta el final
        Do While Not rst.EOF
            ' recorre los campos en el registro actual del recordset para recuperar el dato
            For i = 0 To rst.Fields.Count - 1
                ' añade el valor a la celda
                Sheets(1).Range(Chr(c + 65) & _
                                f + 1).Value = rst.Fields(c)
                c = c + 1
            Next
            ' resetea el indice de las columnas
            c = 0
            ' Referencia al registro actual (incrementa )
            f = f + 1
            ' Siguiente registro
            rst.MoveNext
        Loop
        ' cierra y descarga las referencias
        On Error Resume Next
        rst.Close
        cn.Close
        Set cn = Nothing
        Set rst = Nothing
    End If
    Call Macro1
    Exit Function
ErrorHandler:
    MsgBox "Ha ocurrido un error: " & Err.Description, vbExclamation, "EXCELeINFO"
End Function

:: Descargar EXCELeINFO – Ejecutar consulta SQL desde Excel

Categories: Excel, Macros, Trucos, vba Tags: , , , , ,