Archive

Posts Tagged ‘vba’

EXCELeINFO addin 2.4.0 – Visualizar Configuración regional y de idioma desde Excel con Application.International

December 1, 2012 1 comment

Hace días me hice la pregunta: Para qué ir al Panel de control para ver mi configuración de regional ? Por ejemplo, qué separador de listas tengo configurado, qué idioma tengo configurados, qué formato de fecha tengo.

Me di a la tarea de comprender el funcionamiento de la propiedad International del objeto Application, y me di cuenta que es más sencillo de lo que podría parecer.

Si por ejemplo, queremos saber el símbolo de moneda que maneja nuestro Excel, sólo ejecutamos:

Sub Moneda()
MsgBox Application.International(xlCurrencyCode)
End Sub

Imagen del addin

image

image

Listado de configuraciones que muestra el addin

Configuración de país o región

Constante Significado

xlCountryCode

Versión de país o región de Microsoft Excel.

xlCountrySetting

Configuración actual de país o región en el Panel de control de Windows.

xlGeneralFormatName

Nombre del formato numérico General.

Moneda

Constante Significado

xlCurrencyCode

Símbolo de moneda.

xlCurrencyDigits

Número de decimales que van a utilizarse en los formatos de moneda.

xlNoncurrencyDigits

xlNoncurrencyDigits

Fecha y hora

Constante Significado

xl24HourClock

Muestra si el formato de hora que se utiliza es de 24 horas o si es de 12 horas.

xlDateOrder Orden de los elementos de la fecha.

xlDateSeparator

Separador de fecha (/).

Separadores

Constante Significado

xlDecimalSeparator

Separador decimal.

xlListSeparator

Separador de lista.

xlThousandsSeparator

Separador de miles o cero.

:: Descargar EXCELeINFO add-in 2.4.0

Permitir 3 intentos de contraseña para usar un archivo de Excel antes de cerrarse

November 21, 2012 1 comment

En un post anterior llamado Simular acceso con usuario y password en Excel con formulario vba presentaba un ejemplo en el que teníamos un formulario que nos solicita usuario y contraseña. Tanto el usuario como la contraseña los tomaba de una tabla y sólo cuando ambos coincidían, permitía cerrar el formulario y continuar.

En este ocasión, y tomando en cuenta el post mencionado, comparto un formulario que se lanza al momento de abrir nuestro archivo de Excel y nos solicita una contraseña para usarlo.

Cómo funciona

A diferencia del otro formulario, este sólo pide una clave de acceso para poder usar el archivo. En caso de que intentemos en 3 ocasiones una clave y ésta sea inválida, se cerrará el archivo.

Código

'Definimos la variable pública Intentos
Public Intentos As Byte
'
'Validar la clave
Private Sub CommandButton1_Click()
If Me.txtPass.Value = 1234 Then
    MsgBox "Contraseña válida. Se cerrará el formulario.", _
        vbInformation, "EXCELeINFO"
    Unload Me
Else
    Intentos = Intentos + 1
    MsgBox "Contraseña inválida. Llevas " & Intentos & " intento(s).", _
        vbInformation, "EXCELeINFO"
    Me.txtPass.SetFocus
    Me.txtPass.Value = ""
End If
If Intentos = 3 Then
    MsgBox "Has cumplido 3 intentos. Aquí se cerrará el archivo Excel.", _
        vbInformation, "EXCELeINFO"
    Unload Me
    ActiveWorkbook.Close SaveChanges:=False
Else
End If
End Sub
'
'Formateamos los objetos del formulario
Private Sub UserForm_Initialize()
With Me
    .txtPass.PasswordChar = "*"
    .txtPass.MaxLength = 8
End With
End Sub
'
'Evitamos que use la x para cerrar el formulario
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Por favor, ingresa una contraseña.", vbInformation, "EXCELeINFO"
End If
End Sub

Animación del formulario en acción

EXCELeINFO - 3 intentos de contraseña

:: Descargar el ejemplo

Crear fórmulas Excel con macros independientemente del idioma o separador de argumentos, coma o punto y coma

November 8, 2012 2 comments

Cuando queremos insertar fórmulas en Excel mediante macros, nos encontramos con 2 disyuntivas:

  1. Usar el nombre de la función mdiante lenguaje vba, es decir, su parte en inglés que sea compatible con cualquier Excel en cualquier idioma.
  2. Escribir la fórmula en nuestro propio lenguaje, cual sea que este fuera, adecuando el separador de argumentos: coma (,) o punto y coma(;), pero sólo compatible con otro Excel en nuestro idioma

Insertar fórmulas mediante vba aplicables a Excel en cualquier idioma

Supongamos que necesitamos insertar en una celda la función BUSCARV desde vba. Si esta macro se utilizará en otras versiones de Excel con diferentes idiomas al que usamos, lo mejor será usar los nombres en inglés.

Desde vba usamos la propiedad FORMULA del objeto RANGE:

Range("F6").Formula = "=VLOOKUP(E6,A1:B12,2,0)"

Insertar fórmulas mediante vba aplicables a nuestro idioma

La misma función BUSCARV la deseamos escribir desde vba en nuestro mismo idioma. Para eso debemos hace uso de la propiedad FORMULALOCAL del objeto RANGE.

Range("F6").FormulaLocal = "=BUSCARV(E6,A1:B12,2,0)"

El caso del separador de argumentos cuando es coma o punto y coma

El separador de listas o argumentos varía dependiendo de la configuración regional de nuestro sistema. Por ejemplo, es coma cuando la cofiguración es el Español y punyo y coma cuando la configuración en el inglés. Pero como no nos interesa escribir fórmulas para cada tipo de región, mejor hacemos uso de la propiedad INTERNATIONAL del objecto APPLICATION, para meter a una variable el separador que nos detecte Excel.

Para obtener el separador de argumentos configurado en el Panel de control usamos la siguiente instrucción:

Application.International(xlListSeparator)

Y si dejamos el separador como variable, las instruccones quedarían así:

'Obtener el separador de argumentos
Separador = Application.International(xlListSeparator)
'Para fórmula multi idioma
Range("F6").Formula = "=VLOOKUP(E6" & Separador & "A1:B12" & Separador & _
    "2" & Separador & "0)"
'Para fórmula en nuestro idioma
Range("F6").FormulaLocal = "=BUSCARV(E6" & Separador & "A1:B12" & Separador & _
    "2" & Separador & "0)"

Restringir ingreso de texto o números en TextBox en Excel

November 7, 2012 7 comments

El presente artículo muestra un formulario en el que tenemos 2 TextBoxes: uno para ingresar sólo texto y otro para ingresar sólo números.

Cómo funciona

  • Sólo números: aquí utilizamos la función Chr(x), donde especificamos que si el caracter es menor  a 48 (0) y mayor a 57 (9) entonces éste deberá reemplazarse con “”, es decir, vacío.
  • Sólo texto: aquí utilizamos la función IsText para validar que todo lo que no sea texto se reemplace con “”, es de decir, vacío.

Código

Los siguientes códigos deberán ir en el Change de cada uno de los TextBoxes

'Validar que sólo se ingresen números (a-z y símbolos)
Private Sub txtNumero_Change()
    Dim Texto As Variant
    Dim Caracter As Variant
    Dim Largo As Integer
    On Error Resume Next
    Texto = Me.txtNumero.Value
    Largo = Len(Me.txtNumero.Value)
    For i = 1 To Largo
        Caracter = Mid(Texto, i, 1)
        If Caracter <> "" Then
            If Caracter < Chr(48) Or Caracter > Chr(57) Then
                Me.txtNumero.Value = Replace(Texto, Caracter, "")
            Else
            End If
        End If
    Next i
    On Error GoTo 0
    Caracter = 0
    Caracter1 = 0
End Sub
'
'Validar que sólo se ingrese texto (0-9)
Private Sub txtTexto_Change()
    Dim Texto As Variant
    Dim Caracter As Variant
    Dim Largo As String
    On Error Resume Next
    Texto = Me.txtTexto.Value
    Largo = Len(Me.txtTexto.Value)
    For i = 1 To Largo
        Caracter = CInt(Mid(Texto, i, 1))
        If Caracter <> "" Then
            If Not Application.WorksheetFunction.IsText(Caracter) Then
                Me.txtTexto.Value = Replace(Texto, Caracter, "")
            Else
            End If
        End If
    Next i
    On Error GoTo 0
End Sub

Animación del formulario en ejecución

Validar texto y números

:: Descargar el ejemplo

EXCELeINFO add-in: normalizar/unificar registros en bases de datos

November 4, 2012 2 comments

Cuando trabajamos con bases de datos, siempre nos topamos con registros que vienen escritos de manera diferente, pero que muchas veces se refieren a la misma cosa, tal es el caso de apellidos, departamentos. Dicha diferencia en captura provoca que nuestros filtros o tablas dinámicas no sean efectivas.

La herramienta

La herramienta que se incluye en esa versión del add-in intenta resolver el tema de las bases ‘sucias’, permitiendo filtrar todos los datos únicos e identificarlos para unificar los nombres y así normalizar/unificar nuestras bases para un mejor manejo en las distintas herramientas que nos ofrece Excel para manejo de bases de datos.

Cómo funciona

El primer paso que realiza la herramienta es un filtro avanzado sobre la columna elegida, para después llenar el ListBox del formulario y hacer el conteo de cada uno de los registros únicos. Al final podemos elegir los registros a unificar y asignarles la captura que deseemos.

Animación con la herramienta en ejecución

Normalizar bases de datos

:: Descargar EXCELeINFO add-in 2.3.9

Escuchar música desde Excel con el control WindowsMediaPlayer1

October 21, 2012 1 comment

El objetivo principal de este Post no es mostrar un reproductor de música en Excel, cosa que sería algo extensa de desarrollar por todo lo que implicaría los diferentes tipos de archivos de música y listas de reprodución.

El objetivo de este Post es mostrar como Excel puede manipular archivos de sonido mediante el control WindowsMediaPlayer, disponible en la librería de controles de Windows.

Al momento de insertar el control de Windows Media Player, ya podemos disponer de todas sus propiedades y métodos, como se muestra en la imagen.

image

Cómo funciona

El formulario contiene un ListBox donde se contendrá todos los archivos de audio en formato MP3 y WMA de la carpeta y sucarpetas que se elijan.

Posteriormente los botones de Anterior, Reproducir/Pausa, Parar y Siguiente se activarán para cumplir con las funciones de un reproductor de música.

En la parte inferior se mostrará el Título, artista, álbum, ubicación del archivo, género y duración de la canción. Aunque se podrán mostrar más etiquetas dependiendo de lo que se desee ver, tales como:

Sub Test()
    With frmAudio
        .WindowsMediaPlayer1.currentMedia.getItemInfo ("Name")
        .WindowsMediaPlayer1.currentMedia.getItemInfo ("author")
        .WindowsMediaPlayer1.currentMedia.getItemInfo ("Title")
        .WindowsMediaPlayer1.currentMedia.getItemInfo ("Album")
        .WindowsMediaPlayer1.currentMedia.getItemInfo ("copyright")
        .WindowsMediaPlayer1.currentMedia.getItemInfo ("Artist")
        .WindowsMediaPlayer1.currentMedia.getItemInfo ("Genre")
        .WindowsMediaPlayer1.currentMedia.getItemInfo ("Bitrate") / 1000 & " kbps"
        .WindowsMediaPlayer1.currentMedia.getItemInfo ("Abstract")
        .WindowsMediaPlayer1.currentMedia.getItemInfo ("bitRate")
        .WindowsMediaPlayer1.currentMedia.getItemInfo ("duration")
    End With
End Sub

Formulario en ejecución

image

Código del formulario

Private Sub btnAnterior_Click()
    On Error Resume Next
    Me.ListBox1.ListIndex = Me.ListBox1.ListIndex - 1
    Call Reproducir
    On Error GoTo 0
End Sub
'
Private Sub btnParar_Click()
    Me.WindowsMediaPlayer1.Controls.stop
    Me.btnReproducir.Caption = "Reproducir"
End Sub
'
Private Sub btnReproducir_Click()
    Items = Me.ListBox1.ListCount
    Cuenta = 0
    For i = 0 To Items - 1
        If Me.ListBox1.Selected(i) Then
            Cuenta = Cuenta + 1
        End If
    Next i
'
    If Cuenta = 0 Then
        Me.ListBox1.ListIndex = Me.ListBox1.ListIndex + 1
        Call Reproducir
    Else
        If Me.WindowsMediaPlayer1.playState = wmppsPaused Then
            Me.WindowsMediaPlayer1.Controls.Play
            Me.btnReproducir.Caption = "Pausa"
        Else
            If Me.WindowsMediaPlayer1.playState = wmppsPlaying Then
                Me.WindowsMediaPlayer1.Controls.pause
                Me.btnReproducir.Caption = "Reproducir"
            Else
                Call Reproducir
            End If
        End If
    End If
End Sub
'
Private Sub btnSiguiente_Click()
    On Error Resume Next
    Me.ListBox1.ListIndex = Me.ListBox1.ListIndex + 1
    Call Reproducir
    On Error GoTo 0
End Sub
'
Private Sub CommandButton1_Click()
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & " \ "
        .Title = "EXCELeINFO - Seleccionar carpeta"
        .Show
        If .SelectedItems.Count = 0 Then
        Else
            Me.txtRuta.Value = .SelectedItems(1)
            frmAudio.ListBox1.Clear
            Call Listar
        End If
    End With
    Call EstadoBotones
End Sub

Sub Listar()
    If Me.txtRuta.Value = "" Then
        MsgBox "Debe seleccionar una ruta.", vbExclamation, AddIn
    Else
        Call ListFiles
    End If
End Sub
'
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Call Reproducir
    Me.btnReproducir.Caption = "Pausa"
End Sub
'
Private Sub UserForm_Initialize()
    Call EstadoBotones
    With Me
        .WindowsMediaPlayer1.Visible = False
        .lblTrack.Visible = False
        .lblTipo.Visible = False
        .lblUbicacion.Visible = False
        .ListBox1.ColumnWidths = "0 pt;170 pt;100 pt;200 pt"
    End With
End Sub

El código completo se podrá consultar dentro del mismo archivo.

:: Descargar EXCELeINFO – escuchar música en Excel con WindowsMediaPlayer1.xlsm

Eliminar filas en Excel que no cumplan una condición, largo, contenido, color

October 10, 2012 1 comment

Este tip me ha ayudado a depurar bases de datos donde, por ejemplo, necesito que en la columna Número telefónico sólo haya números con un largo de 10 caracteres.

Cómo funciona

En la imagen de ejemplo tenemos una tabla con 2 columnas: NOMBRE, TELEFONO. Elegimos la celda B2 y corremos la siguiente macro:

Sub EliminarFilas()
'Hacemos un bucle Do While el cual se ejecutará hasta que haya una celda vacía
Do While ActiveCell.Value <> ""
    'La condición es que si el largo es diferente a 10...
    If Len(ActiveCell) <> 10 Then
        '... elimina la fila
        ActiveCell.EntireRow.Delete
        ActiveCell.Offset(-1, 0).Select
    Else
    End If
    'Recorre una fila hacia abajo
    ActiveCell.Offset(1, 0).Select
Loop
End Sub

Resultado

Sólo quedarán las filas donde haya habido números a 10 dígitos

image

Otras condiciones

La macro anterior tiene la siguiente línea la cual es la que condiciona el largo de la celda.

If Len(ActiveCell) <> 10 Then

Pero también puede ser que queramos condicionar contenido o formato de la celda.

Ejemplos de otras codiciones:

'Eliminia diferente a largo de 10
If Len(ActiveCell) <> 10 Then

'Elimina diferentes a 1234567890
If ActiveCell.Value <> "1234567890" Then

'Eliminar los que tengan fondo rojo
If ActiveCell.Interior.ColorIndex = 3 Then

'Eliminar que comiencen con ABC""
If Mid(ActiveCell.Value, 1, 3) = "ABC" Then