Excelforo - Cursos Macros para Excel - Noviembre 2014

¿No crees que Excel te permitirá crecer en tu trabajo?.
Aprende con los mejores y adquiere una buena base: Edición de Cursos de Excel y Macros online con tutor personal de Noviembre de 2014.

Los cursos de Excel y Macros abiertos para este mes de Noviembre son:

Curso Excel Avanzado

(ver más)

Curso Macros Medio

(ver más)

Curso Macros Iniciación

(ver más)

Curso Excel Nivel Medio

(ver más)

Curso Tablas dinámicas en Excel

(ver más)

Curso preparación MOS Excel 2010 (Examen 77-882)

(ver más)

Curso Excel Financiero

(ver más)


Esta nueva edición de Cursos de Excel y macros en modalidad elearning (online) comienzan el día 1 de Noviembre de 2014; y la matrícula estará abierta hasta el día 10.

Excelforo: con la confianza de siempre....estás a tiempo!!

También formación Excel a empresas. Explota los recursos a tu alcance (ver más).

Informarte sin compromiso en cursos@excelforo.com o directamente en www.excelforo.com.

Seguir leyendo...

VBA: Un procedimiento Sub con parámetro.

Hoy veremos un uso bastante cómodo de los procedimientos Sub, que consiste en definir tantos parámetros como necesitemos.
En el ejemplo del día aprovecharemos la selección realizada sobre un ListBox para ejecutar un procedimiento Sub que trata la información aportada por ese ListBox.

Tenemos el siguiente cuadro de información de diferentes empresas de un grupo para ciertos años:

la idea es construir un UserForm para desde él, seleccionar una de las empresas y que se resalten los datos correspondientes...

Construimos un simple UserForm con dos controles:
1- un ListBox1
2- un commandButton1 para Salir de él.

La carga de código de este formulario es:

Private Sub UserForm_Initialize()
'Cargamos el formulario con datos
UserForm1.ListBox1.RowSource = "A2:A10"
End Sub

Private Sub ListBox1_Click()
'asociamos el evento para al hacer click llamar a la macro
Call Módulo1.ResaltaDatos(ListBox1.Value)
End Sub

Private Sub CommandButton1_Click()
'descargamos el userForm1 para Salir
Unload UserForm1
End Sub

Este es el aspecto del userform al mostrarlo:

La parte de programación interesante es la siguiente... que hemos insertado en un Módulo estándar:

Sub ResaltaDatos(empresa)
'comenzamos quitando el formato anterior
Range("B2:D10").Interior.Color = xlNone

'Buscamos la fila que corresponde a la empresa seleccionada
'con el ListBox
With Worksheets(1).Range("A1:A10")
    Set c = .Find(empresa, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            fila = c.Row    'fila buscada
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

'aplicamos un formato de color de fondo
Range("B" & fila & ":D" & fila).Interior.Color = vbCyan

End Sub

Donde hemos definido un parámetro dentro del proceso Sub ResaltaDatos- 'empresa', que utilizamos como haríamos con cualquier variable.
La diferencia es que en este caso la cargamos de información desde el evento _click del Listbox, indicando que el valor de esta variable será el elegido del desplegable del ListBox1:
Call Módulo1.ResaltaDatos(ListBox1.Value)

El efecto es claramente el buscado, al seleccionar las diferentes empresas, se van resaltando...

Seguir leyendo...

VBA: ¿Existe la ruta de archivo de mi función HIPERVINCULO?. La función VBA DIR.

Puede parecer una pregunta extraña.. pero tiene su sentido.
En ocasiones construimos unos vínculos o enlaces a otros archivos (para facilitar su apertura), empleando la función HIPERVINCULO (ver ejemplo), pero dejando la ubicación del archivo vinculado fija... posteriormente en el tiempo, estas ubicaciones de los archivos cambian y nuestra función HIPERVINCULO pierde su sentido y falla (error tipo: No se puede abrir el archivo especificado).

La solución será construir una sencilla macro que evalúe si la ruta empleada como argumento en la función HIPERVINCULO existe o no.
Veamoslo con un ejemplo, donde tenemos tres funciones con tres ubicaciones (dos existen y una tercera no):

El código que insertaremos en un módulo del Explorador de proyectos del Editor de VBA:

Sub VerificarHipervinculo()
Dim ruta As String, ruta3 As String

'recorremos el rango de celdas con las funciones HIPERVINCULO
For Each celda In Range("C3:C5")
    'con estas variables extraemos la ruta del argumento
    ruta = Replace(celda.Formula, "=", "")
    pos1 = InStr(1, ruta, "(") + 2
    pos2 = InStr(1, ruta, ",") - 1
    ruta = Mid(ruta, pos1, pos2 - pos1)
    
    'con la función DIR verificamos si existe o no dicha ruta
    If Dir(ruta, vbArchive) <> "" Then
        MsgBox ruta & " existe"
    Else
        MsgBox ruta & " no existe"
    End If
Next celda

End Sub

Lo importante de este código es la función de VBA DIR que nos retorna un valor tipo String que representa el nombre de un archivo, directorio o carpeta que coincide con el patrón o atributo de archivo especificado, o la etiqueta de volumen de una unidad de disco; según los parámetros:

Constante Valor Descripción
vbNormal 0 Normal
vbReadOnly 1 De sólo lectura
vbHidden 2 Oculto
vbSystem 4 Archivo de sistema
vbVolume 8 Etiqueta de volumen
vbDirectory 16 Directorio o carpeta
vbArchive 32 El archivo ha cambiado desde la última copia de seguridad

Si la función DIR devuelve algún valor entonces implica que la ruta existe... de manera sencilla y práctica.
Si ejecutamos el procedimiento vemos los siguientes mensajes. Una ruta existente:

Y una ruta no existente o fallida:

Seguir leyendo...

VBA: Rellenando un formulario web HTML desde Excel.

Aprenderemos hoy a rellenar un formulario o similar en HTML dentro de una web, empleando Internet Explorer (como complementario que es de Office) utilizando un simple código de VBA para Excel.
Para facilitar la comprensión, realizaremos una búsqueda en Google, ejecutando una macro desde nuestro Excel.

Veamos en primer lugar el código insertado en un módulo de nuestro Libro de trabajo:

Sub RellenarWEBGoogle()
'www.excelforo.com
Dim IE As Object
'creamos el objeto para el navegador Internet Explorer
Set IE = CreateObject("InternetExplorer.application")
'dentro del navegador, accedemos al buscador Google (o cualquier otra wev con un formulario tipo HTML
IE.Navigate "https://www.google.es/"

Do
    DoEvents
Loop Until IE.readystate = 4

'para completar el campo de búsqueda:
'código identificado en la url www.google.es:
'input id=gbqfq class=gbqfif name=q type=text autocomplete=off value=""
IE.Document.getelementbyid("gbqfq").Value = "excelforo"
'para realizar la búsqueda presionamos el botón de Buscar..
''código identificado en la url www.google.es:
'button class="gbqfba" aria-label="Buscar con Google" id="gbqfba" name="btnK"
IE.Document.getelementbyid("gbqfba").Click
'IE.Document.All("btnK").Click otra forma de llamarlo..

'finalmente hacemos visible la ventana de Internet Explorer
IE.Visible = True

End Sub

Fundamental para el correcto funcionamiento es instalar la referencia Microsoft Internet Controls, desde el menú Herramientas > Referencias del editor de VBA:

Si lanzamos y ejecutamos nuestra macro, conseguiremos ver directamente:

Para comprender correctamente el código empleado, debemos saber localizar el Id o Name de los cuadros de texto o botones que vayamos a completar o presionar (clicar), siempre en un lenguaje HTML.. ya que otros formularios en script no son accesibles...

El primer paso, en la barra de direcciones del navegador (para este paso se recomienda o FireFox o Google Chrome), introduciremos la url indicada (en nuestro ejemplo: www.google.es).

Seguir leyendo...

Marcar proyectos finalizados con un formato condicional.

Hoy veremos cómo aprovechándonos de las estructuras de datos con las que trabajamos, y aplicando sencillas técnicas, podemos conseguir nuestras metas. En particular hoy combinaremos un formato condicional con una fórmula (CONTAR.SI.CONJUNTO), para remarcar aquellos proyectos de nuestro listado ya finalizados.
Veamos nuestro listado:

Vemos en la imagen el resultado final a conseguir, donde vemos resaltado en verde aquellos proyectos que tras su 'Inicio' han completado sus acciones y su estado es 'Finalizado'.

El proceso es sencillo, ya que basta seleccionar el rango completo A2:C11, asegurándonos que la celda activa que queda es A2 !!!. A continuación accederemos a la herramienta Formato Condicional, y buscaremos el tipo fórmula, donde incorporaremos la siguiente fórmula:
=Y(CONTAR.SI.CONJUNTO($B$2:$B$11;$B2;$C$2:$C$11;"finalizado");CONTAR.SI.CONJUNTO($B$2:$B$11;$B2;$C$2:$C$11;"inicio"))
Un poco larga pero muy simple... exigimos que se verifique para el mismo proyecto que tenga un estado 'Inicio' y otro, al tiempo, 'Finalizado', lo que conseguimos con la función lógica Y, la cual nos devuelve un VERDADERO si ambas condiciones se cumplen, y por tanto marca dichas filas...
En la imagen siguiente comprobamos el comportamiento de la fórmula en la herramienta Formato condicional:

Por supuesto proyectos no 'finalizados' no se marcarán por incumplir las condiciones dadas de nuestra fórmula.. o aquellos que por error, no se hayan 'iniciado'...

Seguir leyendo...

Conciliando partidas con la función CONTAR.SI.

Muy frecuentemente, trabajando en temas de tesorería, buscamos la forma de facilitarnos el trabajo de conciliar importes... (ver ejemplo).
En el ejemplo de hoy veremos una manera de localizar importes equivalentes con la función CONTAR.SI.

Partimos de un listado de importes, con unas fechas dadas, con nuestra sencilla fórmula conseguiremos localizar aquellos importes sin conciliar (o de manera complementaria, los conciliados), con ciertas restricciones... y es que en caso de cantidades repetidas, asociaremos la conciliación a la primera encontrada.
Veamos nuestro ejemplo:

la idea es localizar, para conciliarlas, los importes con signo contrario; por ejemplo, localizar si existe un importe con signo contrario al de la celda b2 (buscar -390).
La fórmula buscada y que resuelve nuestro problema sería (en C2):
=CONTAR.SI($B$2:B2;B2)>CONTAR.SI($B$2:$B$15;-B2)

OJO!! el primer CONTAR.SI tiene un rango corrido $B$2:B2 !!!; mientras el segundo trabajo sobre el rango completo.

Para facilitar la comprensión, anido el resultado de esta fórmula (será VERDADERO o FALSO), en un condicional.. quedando la fórmula completa:
=SI(NO(CONTAR.SI($B$2:B2;B2)>CONTAR.SI($B$2:$B$15;-B2));"conciliado";"SIN CONCILIAR")

Podemos comprobar en las columnas auxiliares, D y E, la comparación que realizamos con los dos CONTAR.SI, y el sentido de nuestra fórmula aplicada en C2:C15 (celdas en amarillo de la imagen).
En la columna D, con la función:
CONTAR.SI($B$2:B2;B2)
conseguimos numerar las veces que se repite cada importe; mientras que con la función en la columna E:
CONTAR.SI($B$2:$B$15;-B2)
directamente vemos las veces que se repite en todo el rango cada importe...

Obviamente al comparar una y otra, fila a fila, con el operador 'mayor estricto', determinamos si el importe de cada fila tiene una contrapartida negativa; en caso contrario nos encontraríamos en la situación de un importe positivo sin contrapartida, o directamente una negativa sin importe positivo con que cuadrar.

Seguir leyendo...

VBA: Cambiar nombres de ficheros con la instrucción NAME.

La idea de hoy es cambiar el nombre de un fichero empleando una sencilla macro, basada en la instrucción poco conocida Name.
Deberemos saber que esta instrucción Name nos permite cambiar el nombre de un archivo y moverlo a un directorio o carpeta diferente (si así lo deseamos).
Tendremos cuidado, ya que Name puede mover un archivo a través de unidades pero sólo puede cambiar el nombre a un directorio o carpeta existente cuando tanto 'el nombre de la ruta antigua' como 'el nombre de la ruta nueva' están ubicados en la misma unidad.
Importante: Name no puede crear un nuevo archivo, directorio o carpeta!!!.

Por último, un par de limitaciones más...
si utilizamos la instrucción Name con un archivo abierto se producirá un error.
Los argumentos de la instrucción Name no pueden incluir caracteres comodín múltiples (*) ni sencillos (?).

La gran ventaja de esta instrucción es que con ésta podemos cambiar nombres de archivos sin abrirlos (como requeriría, por ejemplo, el método Workbook.SaveAs)

Conocidas entonces las ventajas e inconvenientes podemos trabajar con ella

La idea del ejercicio de hoy es renombrar los ficheros contenidos en una carpeta/directorio ('E:\excelforo\Cambios'):

Tenemos en la Hoja1 de nuestro fichero 'base' (un tercer fichero independiente), donde hemos incluido nuestra macro, la correspondencia entre los viejos y nuevos nombres:

Con esta hipótesis de trabajo, podemos disponer de nuestro procedimiento, dentro del proyecto de VBA de nuestro Libro de trabajo, dentro del Editor de VB, en un módulo, insertamos el siguiente código:

Sub CambiarNombres()
Dim NombreViejo As String
Dim NombreNuevo As String

'recorremos las celdas A2:A5 con los viejos nombres
For Each fichero In Range("A2:A5")
    'asignamos valores a las variables, con el viejo y nuevo nombre
    NombreViejo = "E:\excelforo\Cambios\" & fichero.Value & ".xlsx"
    NombreNuevo = "E:\excelforo\Cambios\" & fichero.Offset(0, 1).Value & ".xlsx"
    'con la instrucción Name procedemos al cambio de nombre de un archivo, directorio o carpeta.
    Name NombreViejo As NombreNuevo
Next fichero
End Sub

Tras ejecutarlo, conseguimos nuestro objetivo...

Los nombres de los ficheros han sido reemplazados por los nuevos.

Seguir leyendo...

Un gráfico dinámico con la función INDICE.

En una entrada anterior expuse algunos usos de la función INDICE (ver).
Hoy aplicaremos uno de esos usos, en concreto el que al omitir el argumento número_fila o número_columna la función INDICE trabajaba sobre toda la columna o fila, para generar un gráfico dinámico.

Partimos de estos datos...

En la celda C11 he insertado una Validación de datos tipo lista con los meses; y sobre el rango completo B2:N8 he generado un gráfico de columnas.
el siguiente paso es crear un Nombre definido:
GraficoColumna =INDICE(Hoja1!$C$3:$N$8;0;COINCIDIR(Hoja1!$C$11;Hoja1!$C$2:$N$2;0))
que emplea la función INDICE siguiendo los criterios comentados, en este caso el argumento número_fila tiene el valor cero (también podría estar vacío). Así conseguimos que Excel comprenda que debe tomar todos los valores de la columna del mes...

El último paso es simple, accedemos al gráfico y en concreto entramos en la ventana de Seleccionar datos..., para modificar la Serie de datos:

haz clic en la imagen

En el cuadro de Valores de la serie: escribimos:
=FxINDICE_2da.xlsx!GraficoColumna
es decir, el nombre de nuestro Libro de trabajo seguido de ! y del Nombre definido.

Listo.. basta que cambies el valor de C11 para ver cómo el gráfico se adapta a los datos del mes elegido.

Hace tiempo mostré una forma similar de conseguir el mismo efecto (ver ejemplo) pero empleando otra técnica.

Seguir leyendo...