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...