Ediciones ENI Editorial | líder en informática
Ejemplo de aplicación VBA Excel
Presentación general
La empresa MobiBuro, especializada en la venta de material de oficina para profesionales, dispone de un "show-room" con un stock de material de exposición que desea prestar o vender a sus clientes.
Para ello, utiliza una aplicación VBA de Excel que le permite:
-
generar fichas de productos en formato PDF,
-
enviar esas fichas por email a sus clientes,
-
generar fichas de préstamo y gestionar los préstamos y las devoluciones de material,
-
obtener una situación del stock en una fecha determinada.
Esta aplicación le permitirá poner en práctica la mayor parte de lo aprendido en este libro. Entre otros elementos, incluye formularios, tablas, una cinta de opciones personalizada, procedimientos de eventos, una comunicación con Outlook (envío de correos electrónicos), hipervínculos y una administración de errores.
Todos los archivos necesarios para la aplicación se encuentran junto a los ejemplos del libro: el archivo StockShowRoom.xlsm contiene las hojas Excel, el código VBA y los formularios de la aplicación, las fotos de los productos se encuentran en la subcarpeta Fotos y el manual de usuario Ayuda.pdf está en la subcarpeta Ayuda.
La lectura del manual de usuario ayuda.pdf le permitirá tener una visión general de las diferentes funcionalidades de la aplicación.
1. Presentación de la interfaz...
Descripción del contenido de la aplicación StockShowRoom
Esta aplicación consta de los elementos siguientes:
-
Una cinta de opciones personalizada de Office creada con la utilidad Custom UI Editor: encontrará una descripción detallada de esta utilidad en el capítulo Mejoras en la interfaz de usuario.
-
Las hojas de Excel:
-
Inicio: página de inicio que se muestra al abrir la aplicación.
-
Stock: tabla de los artículos en stock.
-
Ficha_Producto: modelo de ficha de producto.
-
Bono_Prestamo: modelo de bono de préstamo.
-
Parametros: lista de los usuarios, texto del email dirigido a los clientes, tasas de depreciación del stock, etc.
-
Los formularios:
-
Frm_Envia_Email: formulario para el envío de fichas de productos a un cliente.
-
Frm_Ficha_Prestamo: formulario de introducción de datos del cliente para generar fichas de préstamo.
-
Frm_Ficha_Devolucion: formulario de introducción de la fecha de devolución del material.
-
Frm_Identificacion: ventana de conexión a la aplicación.
-
Frm_Situacion_Stock: formulario de introducción de la fecha de referencia para la valoración del stock.
-
Los módulos de código:
-
FichasPrestamo: código para generar las fichas de préstamo en formato PDF.
-
FichasProducto: código para generar fichas de productos en PDF.
-
HiperVinculos: código para crear un hipervínculo en la foto del producto....
Conexión a la aplicación
El código VBA que se ejecuta al abrir el libro StockShowRoom.xlsm se encuentra en el procedimiento Workbook_Open del módulo ThisWorkbook.
Private Sub Workbook_Open()
‘ Personalización del aspecto de Excel
Application.DisplayFormulaBar = False
‘ Formulario de conexión
Frm_Identificacion.Show
End Sub Este código permite ocultar la barra de formulario y muestra el formulario Frm_Identificacion siguiente:
1. Presentación del formulario Frm_Identificacion
Este formulario permite:
-
comprobar el nombre de usuario y la contraseña que se introducen en los campos mediante el grupo de celdas TAB_LOGIN que contiene la lista de usuarios;
-
cargar su perfil de usuario y ocultar y deshabilitar algunas celdas en función del perfil de usuario.
2. Lista de los controles del formulario Frm_Identificacion
|
N° |
Nombre del control |
Descripción |
|
1. |
txtLogin |
Zona de texto |
|
2. |
txtPassword |
Zona de texto: la propiedad PasswordChar debe tener el valor * para sustituir los caracteres introducidos por un * |
|
3. |
BtOK |
Botón de comando |
|
4. |
BtCancelar |
Botón de comando |
3. Código del formulario Frm_Identificacion
Option Explicit
Private Sub btCancelar_Click()
‘ Botón Cancelar: cierra el libro
ThisWorkbook.Close False
Unload Me
End Sub
Private Sub btOK_Click()
Dim oRngLogin As Range
Dim bOk As Boolean
Dim i As Integer
‘ Botón OK: controla la introducción de texto
If txtLogin = “” Then
MsgBox “Introduzca su nombre de usuario”, vbExclamation
txtLogin.SetFocus
Exit Sub
End If
If txtPassword = “” Then
MsgBox “Introduzca su contraseña”,
vbExclamation
txtPassword.SetFocus...Interfaz de la aplicación
Tras conectarse a la aplicación, puede acceder a las diferentes funcionalidades a través de la cinta de opciones personalizada de Office.
1. Código XML de la cinta de opciones personalizada
2. Código VBA de los procedimientos que se llaman con los botones de la cinta de opciones
Estos procedimiento se encuentran en el módulo ProcCinta. El procedimiento fbCtrl_Seleccion permite controlar si se han seleccionado fichas de la hoja Stock.
Option Explicit
Public objRibon As IRibbonUI
Sub RibbonOnLoad(Ribbon As IRibbonUI)
‘ Inicialización de la cinta de opciones
Set objRibon = Ribbon
End Sub
Sub Ini_Seleccion(control As IRibbonControl)
‘ Anulación de la selección actual
Suprime_Seleccion
End Sub
Sub Fichas_Productos(control As IRibbonControl)
‘ Generación de las fichas de productos
If Not fbCtrl_Seleccion(False) Then Exit Sub
Genera_Fichas_Productos True
End Sub
Sub Envio_Mail(control As IRibbonControl)
‘ Formulario para enviar un email al cliente
FRM_ENVIA_EMAIL.Show
End Sub
Sub Situacion_Stock(control As IRibbonControl) ...Generación de fichas de productos en formato PDF
1. Descripción del tratamiento
El módulo FichasProducto contiene el procedimiento Genera_Fichas_Productos que hace las acciones siguientes:
-
Crea un nuevo libro de Excel para almacenar las distintas fichas generadas.
-
Recorre las fichas seleccionadas en la hoja Stock y para cada ficha:
-
copia la hoja modelo Ficha_Producto en el nuevo libro de Excel;
-
actualiza las celdas nombradas de la ficha a partir de los datos de la hoja Stock;
-
inserta la foto, la redimensiona y la centra horizontal y verticalmente.
-
Exporta al formato PDF el conjunto de las fichas generadas.
-
Muestra el archivo PDF generado en función del argumento bVisualizacion.
Este argumento tiene el valor false si el procedimiento se llama desde el formulario de envío de email al cliente, el valor true si el procedimiento se llama con el botón Fichas de Productos de la cinta.
2. Código VBA del procedimiento Genera_Fichas_Productos
Option Explicit
Public Function Genera_Fichas_Productos(bVisualizacion As Boolean)
As Boolean
Dim oRngStock As Range
Dim sTabStock() As Variant
Dim iNumFichas As Integer
Dim iNumSeleccion As Integer
Dim i As Integer
Dim oWbkFichas As Workbook
Dim oShtFicha As Worksheet
Dim oShtFichaModelo As Worksheet
Dim iFicha1 As Integer
Dim sPath As String
Dim sFile As String
Dim sMensaje As String
Const iAlturaFoto = 220
Const iAnchuraFoto = 440
‘ Inicialización
Genera_Fichas_Productos = False
On Error GoTo Err
‘ Modelo de precio
Set oShtFichaModelo = ThisWorkbook.Sheets(“Ficha_Producto”)
‘ Introduce la tabla de Excel en una variable tabla
On Error GoTo Err
Application.ScreenUpdating = False
sTabStock = Range(“TAB_STOCK”).value
iNumFichas = Ubound(sTabStock)
‘ Generación de las fichas de productos en un nuevo libro
Set oWbkFichas = Application.Workbooks.Add
iFicha1 = oWbkFichas.Sheets.Count + 1
For i = 1 To iNumFichas
If sTabStock(i...Envío de las fichas de productos por email a los clientes
1. Formulario Frm_Envia_Email
El envío de las fichas de productos por email a los clientes se hace a través del formulario Frm_Envia_Email.
|
N° |
Nombre del control |
Descripción |
|
1. |
txtCliente |
Zona de texto |
|
2. |
txtEmail |
Zona de texto |
|
3. |
btEmail |
Botón de comando |
2. Código VBA del formulario Frm_Envia_Email
El código VBA se ejecuta después de pulsar el botón btEmail y efectúa las acciones siguientes:
-
Control de introducción: deben escribirse el nombre y la dirección de email del cliente.
-
Verificación de la validez de la dirección de email con la función IsEmailValido.
-
Llamada al procedimiento de generación de las fichas de productos Genera_Fichas_Productos.
-
Envío del email al cliente a través de Outlook con el archivo PDF de las fichas de productos.
El contenido del cuerpo del email se encuentra almacenado en la pestaña Parámetros, en las celdas llamadas ASUNTO_EMAIL y CUERPO_EMAIL. El texto [CLIENTE] se sustituirá por el nombre del cliente.
Option Explicit
Private Sub btEmail_Click()
‘ Control de introducción de texto
If txtCliente = “” Then
MsgBox “Nombre del cliente obligatorio”, vbExclamation
txtCliente.SetFocus
Exit Sub
End If
If txtEmail = “” Then
MsgBox “Dirección de email obligatoria”, vbExclamation
Exit Sub
Else
‘ Verificación de la dirección de email
...Generación de las fichas de préstamo
1. Formulario Frm_Ficha_Prestamo
El formulario Frm_Ficha_Prestamo permite:
-
informar de la fecha del préstamo y de los datos del cliente prestatario,
-
generar las fichas de préstamo en formato PDF (tratamiento similar a las fichas de producto, pero basándose en un modelo distinto),
-
indicar la fecha del préstamo y los datos del cliente para los productos seleccionados de la hoja de Stock.
|
N° |
Nombre del control |
Descripción |
|
1. |
txtFecha |
Zona de texto |
|
2. |
txtCliente |
Zona de texto |
|
3. |
txtDireccion1 |
Zona de texto |
|
4. |
txtDirección2 |
Zona de texto |
|
5. |
txtCpCiudad |
Zona de texto |
|
6. |
btFichasPrestamo |
Botón de comando |
2. Código VBA del formulario Frm_Ficha_Prestamo
Option Explicit
‘ Introducción de la fecha
Private Sub txtFecha_Cambio()
txtFecha = FormateaFecha(txtFecha)
End Sub
Private Sub txtFecha_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
KeyAscii = IntChar(KeyAscii)
End Sub
Private Sub btFichasPrestamo_Click()
‘ Control de introducción
If txtFecha & Space(0) = “” Then
MsgBox “Fecha del préstamo obligatorio”, vbExclamation
txtFecha.SetFocus
Exit Sub
End If
If txtCliente & Space(0) = “” Then
MsgBox “Nombre del cliente obligatorio “, vbExclamation
txtCliente.SetFocus
Exit Sub
End If
If txtDireccion1 & Space(0) = “” Or txtCpCiudad & Space(0) = “” Then
MsgBox “Dirección del cliente obligatoria”, vbExclamation
txtCliente.SetFocus
Exit Sub
End If
‘ Generación de las fichas de préstamo
pb_sCliente = txtCliente
If Not fAct_Tabla Then Exit Sub
Genera_Fichas_Prestamo_Devolucion (False)
Unload Me
End Sub
Private Function fAct_Tabla()...Administración de las devoluciones de préstamos
1. Formulario Frm_Ficha_Devolucion
|
N° |
Nombre del control |
Descripción |
|
1. |
txtFecha |
Zona de texto |
|
2. |
btFichasDevolucion |
Botón de comando |
El formulario Frm_Ficha_Devolucion permite:
-
controlar las fichas seleccionadas mediante el comando fbCtrl_Seleccion (con el argumento bFichasDevolucion en True): todas las fichas seleccionadas deben tener el mismo nombre de cliente prestatario,
-
informar de la fecha de devolución de los productos seleccionados en la hoja de Stock,
-
generar las fichas de devolución en formato PDF: llamada al procedimiento Genera_Fichas_Prestamo_Devolucion,
-
indicar la fecha de devolución para los productos seleccionados en la hoja de Stock.
2. Código VBA del formulario Frm_Ficha_Devolucion
Option Explicit
Private Sub btFichasDevolucion_Click()
‘ Control de introducción
If txtFecha & Space(0) = “” Then
MsgBox “Fecha de devolución obligatoria”, vbExclamation
txtFecha.SetFocus
Exit Sub
End If
‘ Generación de las fichas de devolución
If Not fAct_Tabla Then Exit Sub
Genera_Fichas_Prestamo_Devolucion (True)
Unload Me
End Sub
...Situación del stock en una fecha determinada
1. Formulario Frm_Situacion_Stock
Este formulario permite:
-
introducir una fecha de referencia: fecha a la que deseamos calcular el valor del stock,
-
copiar el conjunto de las fichas de la pestaña Stock en una nueva pestaña,
-
suprimir las fichas sin fecha de venta o con una fecha de compra anterior a la fecha introducida y formatear la tabla,
-
calcular el valor del stock correspondiente a la fecha de referencia (modificando la fórmula de cálculo del número de años de depreciación) y mostrarlo en la parte inferior de la tabla.
|
N° |
Nombre del control |
Descripción |
|
1. |
txtFecha |
Zona de texto |
|
2. |
btSituStock |
Botón de comando |
2. Código VBA del formulario Frm_Situacion_Stock
Option Explicit
‘ Introducción de la fecha de referencia
Private Sub txtFecha_Cambio()
txtFecha = FormateaFecha(txtFecha)
End Sub
Private Sub txtFecha_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
KeyAscii = IntChar(KeyAscii)
End Sub
Private Sub btSituStock_Click()
‘ Control de la fecha
If Not IsDate(txtFecha) Then
MsgBox “Fecha incorrecta”
txtFecha.SetFocus
Exit Sub
End If
‘ Generación de la tabla de situación
Tabla_Situacion_Stock (txtFecha)
End Sub
Private Sub Tabla_Situacion_Stock(zFecha As Date)
Dim oShtStock As Worksheet
Dim oShtSitu As Worksheet
Dim i, j As Integer
Dim bOk As Boolean
Dim dFechaRef As Date
Dim sTitulo As String
Dim sTabName As String
‘ Generación de la tabla situación del stock
‘ Fecha de referencia
On Error GoTo Err
dFechaRef = DateValue(zFecha)
‘ Situación ya existente
Application.ScreenUpdating = False
Application.DisplayAlerts...Actualización de los hipervínculos de las fotos
1. Presentación
El procedimiento Act_VinculosFotos del módulo HiperVinculos permite asociar automáticamente un hipervínculo al archivo de la foto del producto.
El nombre del archivo de imagen está escrito en la columna F de la hoja de Stock.
2. Código del procedimiento Act_VinculosFotos
Option Explicit
Public Sub Act_Vinculos()
Dim oRngStock As Range
Dim iNumFichas As Integer
Dim i As Integer
Dim sPathFotos As String
Dim sPathSAV As String
Dim sVinculo As String
' Ruta
sPathFotos = ThisWorkbook.Path & "\Fotos\"
' Actualización de los hipervínculos
On Error GoTo Err
Application.ScreenUpdating = False
Set oRngStock = Range("TAB_STOCK")
iNumFichas = oRngStock.Rows.Count
' Vínculos de las Fotos y el SAV
For i = 1 To iNumFichas
' Vínculos de las Fotos
If oRngStock(i, iColFoto) <> "" And oRngStock(i, iColFoto)
<> "TOMAR FOTO" Then
sVinculo = sPathFotos &...Procedimientos generales
1. El módulo ProcGene
El módulo ProcGene contiene los procedimientos y variables públicas que pueden llamarse desde los diferentes módulos de la aplicación.
Las columnas de la tabla de la hoja de Stock son constantes públicas. Por ejemplo, la declaración "iColPrecioVenta = 15" indica que el precio de venta está en la columna 15. Así, si resulta necesario insertar columnas en la tabla, basta con cargar el valor de las constantes sin modificar ninguna línea de código.
2. Lista de los procedimientos de ProcGene
|
Procedimiento o función |
Descripción |
|
Espera |
Permite detener el tratamiento durante el número de milisegundos que se pasan como parámetro |
|
Proteccion |
Protege la hoja de Stock en función del perfil de usuario |
|
AbreArchivo |
Abre un archivo con la aplicación asociada a su extensión |
|
IntChar |
Función que se llama cuando se introduce una fecha: impide que se escriban valores no numéricos |
|
FormateaNombre |
Función que se utiliza para los nombres de fichas: suprime los caracteres « / » que contiene un texto |
|
FormateaFecha |
Función que se llama cuando se introduce una fecha : muestra automáticamente « / » después de introducir el día o el mes |
3. Código VBA del módulo ProcGene
' Esta función API busca un archivo ejecutable
Private Declare PtrSafe Function FindExecutable Lib "shell32.dll" _
Alias "FindExecutableA" (ByVal lpFile As String, _
ByVal lpDirectory As String, ByVal lpResult As String) As Long
Const MAX_FILENAME_LEN = 260
' Envío...
