Visual Basic for Applicationsen MS Excel
1
¿Para qué sirve una macro en Excel? Una macro nos ayuda a automatizaraquellas tareas que hacemos repetidamente. Una macro es una serie deinstrucciones que son guardadas dentro de un archivo de Excel para poderser ejecutadas cuando lo necesitemos.
Las macros se escriben en un lenguaje de computadora especial que esconocido como Visual Basic for Applications (VBA). Este lenguaje permiteacceder a prácticamente todas las funcionalidades de Excel y con ellotambién ampliar la funcionalidad del programa.
Las Macros en MS Excel.
2
Visualizar la Ficha Programador en la Cinta de Opciones (1 de 2).
Botón derecho en una zona en blanco de la cinta.
3
4
Visualizar la Ficha Programador en la Cinta de Opciones (2 de 2).
La Ficha programador y la Grabadora de Macros
5
Grabar y Ejecutar una Macro
6
Visualizar el Código de una Macro
7
8
Visualizar el Código de una Macro
C:\Usuarios\[Usuario]\AppData\Roaming\Microsoft\Excel\XLSTART
Libro de Macros Personal
9
10
Libro de Macros Personal
• Cada elemento de Excel es representado en VBA como un objeto. Por ejemplo,existe el objeto Workbook que representa a un libro de Excel. También existe elobjeto Sheet que representa una hoja y el objeto Chart para un gráfico.
• Cada uno de estos objetos tiene propiedades ymétodos. Por ejemplo, el objetoWorkbook tiene propiedades como ActiveSheet (Hoja activa), Name (Nombre),ReadOnly (Solo Lectura), Saved (Guardado) y algunos de sus métodos son Save(Guardar), Close (Cerrar), PrintOut (Imprimir), Protect (Proteger), Unprotect(Desproteger).
Objetos, Propiedades y Métodos de VBA
11
12
Objetos, Propiedades y Métodos de VBA
Para acceder a las propiedades y métodos de un objeto lo hacemos a través de una nomenclatura especial. Justo después delnombre del objeto colocamos un punto seguido del nombre de la propiedad o del método. Observa este ejemplo dondehacemos uso de la propiedad Value para la celda A1:
Range("A1").Value = "Hola"
De esta manera asignamos una cadena de texto al valor de la celda A1. Ahora bien, si queremos borrar ese valor queacabamos de colocar en la celda podemos utilizar el método Clear de la siguiente manera:
Range("A1").Clear
Los objetos tienen muchas propiedades y métodos y a veces es difícil pensar que los llegaremos a memorizar todos porcompleto. Sin embargo, el Editor de Visual Basic es de gran ayuda porque justamente al momento de escribir nuestro códigonos proporciona la lista completa de propiedades y métodos para un objeto.
13
Objetos, Propiedades y Métodos de VBA
Para tener acceso a los objetos que están por debajo del objeto Applicationpodemos utilizar el punto. El punto nos ayuda a navegar por la jerarquía haciaun nivel inferior. Observa lo que se muestra en el Editor de Visual Basic alcolocar un punto después del objeto Application:
Por ejemplo, si deseamos poner en negrita el texto de la celda A1 debemosllegar al objeto Range el cual nos dará acceso a modificar la propiedad Bold dela siguiente manera:
14
Objetos, Propiedades y Métodos de VBA
Objetos predeterminados
Existe una funcionalidad intrínseca de VBA conocida como objetos predeterminados la cual nos permite omitir la escritura de algunos objetos y aun así tener un código funcional. Por ejemplo, en la sentencia mostrada previamente podemos omitir el objeto Application y tener nuestro código funcionando correctamente:
Inclusive podemos omitir los objetos ActiveWorkbook y ActiveSheet sabiendo que el código se ejecutará siempre sobre el libro activo y la hoja que esté activa al momento de la ejecución:
15
Objetos, Propiedades y Métodos de VBA
16
Objetos, Propiedades y Métodos de VBA
Algunos ejemplos de eventos en VBA son los siguientes:
WorkbookOpen: El usuario abre un libro de Excel.WorkbookActivate: El usuario activa un libro de Excel.SelectionChange: El usuario cambia la selección de celdas en una hoja.
Para descubrir los eventos que tiene un objeto es suficiente con abrir el Editor de Visual Basic y posteriormente el Examinador de objetos (F2). En el panel izquierdo se mostrarán los objetos y en el panel derecho las propiedades, métodos y eventos de dicho objeto. Podrás distinguir los eventos porque tienen un icono en forma de rayo (color amarillo):
Eventos en VBA
17
18
Eventos en VBA
19
Editor VBA
20
Ejemplos VBA en ExcelCadenas de Texto
21
Ejemplos VBA en ExcelCadenas de Texto
22
Ejemplos VBA en ExcelCadenas de Texto
Sub TablaMultiplicar()Dim n, i As IntegerDim s As StringDim r As Range
s = InputBox("Tabla de multiplicar del número: ", "Título")If s <> "" Thenn = Val(s)ActiveSheet.Range("C2").Value = "Tabla de multiplicar del " & nSet r = ActiveSheet.Range("C4")For i = 0 To 10r.Offset(i, 0).Value = ir.Offset(i, 1).Value = i * n
Next iEnd If
End Sub
23
Ejemplos VBA en ExcelTabla de Multiplicar
Sub Exercise()Dim Character As StringDim Number As Integer
Number = 114Character = Chr(Number)
ActiveCell = "The ASCII character of " & Number & " is " & CharacterEnd Sub
Sub Exercise()Dim Character As StringDim Number As Long
Number = 358Character = ChrW(Number)
ActiveCell = "The ASCII character of " & Number & " is " & CharacterEnd Sub
Sub Exercise()Dim Number As Integer
Number = 28645ActiveCell = Hex(Number)
End Sub24
Ejemplos VBA en ExcelTabla ASCII
25
Ejemplos VBA en ExcelExtraer números de una celda
Function EXTRAENUM(cadena As String)
'Variable numeros contendrá solo números de la cadenaDim numeros As Stringnumeros = ""
'Recorrer la cadenaFor i = 1 To Len(cadena)
'Evaluar SI el carácter actual es un númeroIf IsNumeric(Mid(cadena, i, 1)) Then
'Concatenar valor numérico a la variable numerosnumeros = numeros & Mid(cadena, i, 1)
End IfNext
'Devolver los números encontradosEXTRAENUM = numeros
End Function
26
Ejemplos VBA en ExcelExtraer números de una celda
Function OBTENERCOLOR(celda As Range) As Long
OBTENERCOLOR = celda.Interior.Color
End Function
Ejemplos VBA en ExcelOperaciones con Colores en Excel
27
Function SUMARPORCOLOR(celdaColor As Range, rango As Range)
'Variable resultado almacena la suma totalDim resultadoDim celda As Range
For Each celda In rango'Compara la propiedad Interior.ColorIf celda.Interior.Color = celdaColor.Interior.Color Thenresultado = resultado + celda.Value
End IfNext celda
SUMARPORCOLOR = resultado
End Function
28
Ejemplos VBA en ExcelOperaciones con Colores en Excel
Function CONTARPORCOLOR(celdaColor As Range, rango As Range)
'Variable resultado almacena la cuenta totalDim resultadoDim celda As Range
For Each celda In rango'Compara la propiedad Interior.ColorIf celda.Interior.Color = celdaColor.Interior.Color Thenresultado = resultado + 1
End IfNext celda
CONTARPORCOLOR = resultado
End Function
29
Ejemplos VBA en ExcelOperaciones con Colores en Excel
Worksheets.Add.Name = "Enero"
Worksheets.Add (After:=Worksheets("Hoja2")).Name = "Febrero"
Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = "Marzo"
30
Ejemplos VBA en ExcelCómo crear hojas de Excel
Private Sub Worksheet_Change(ByVal Target As Range)If Target.Column = 1 ThenWith Columns(1).Sort key1:=.Cells(1, 1)
End WithEnd If
End Sub
clic derecho sobre el nombre de la hoja y seleccionar la opción Ver código
31
Ejemplos VBA en ExcelMacro para ordenar datos automáticamente en Excel
Private Sub Worksheet_Change(ByVal Target As Range)If Target.Column = 2 ThenWith Range("A:B").Sort key1:=.Cells(1, 2), Header:=xlYes
End WithEnd If
End Sub
32
Ejemplos VBA en ExcelMacro para ordenar datos automáticamente en Excel
=CONCATENAR(B2, "; ", B3, "; ", B4, "; ", B5, "; ", C2, "; ", C3, "; ", C4, "; ", D2, "; ", D3, "; ", D4, "; ", D5, "; ", E2, "; ", E3, "; ", E4)
Function CONCATENARCELDAS(Rango As Range)
'Bucle para recorrer todas las celdas del rangoFor Each celda In Rango.Cells
'Si la celda NO está vacía, entonces concatenarlaIf celda.Value <> "" Thenresultado = resultado & "; " & celda.Value
End If
Next celda
'Se remueve el ; y espacio inicialresultado = Right(resultado, Len(resultado) ‐ 2)
CONCATENARCELDAS = resultado
End Function 33
Ejemplos VBA en ExcelConcatenar múltiples celdas
=ALEATORIO.ENTRE(‐50, 50)
Function AleatoriosUnicos(Inferior As Integer, Superior As Integer, Cantidad As Integer) As String
Dim iArr As VariantDim i As IntegerDim r As IntegerDim temp As Integer
Application.Volatile
ReDim iArr(Inferior To Superior)For i = Inferior To SuperioriArr(i) = i
Next i
For i = Superior To Inferior + 1 Step ‐1r = Int(Rnd() * (i ‐ Inferior + 1)) + Inferiortemp = iArr(r)iArr(r) = iArr(i)iArr(i) = temp
Next i
For i = Inferior To Inferior + Cantidad ‐ 1AleatoriosUnicos = AleatoriosUnicos & " " & iArr(i)
Next i
AleatoriosUnicos = Trim(AleatoriosUnicos)End Function
Private Sub CommandButton1_Click()Range("B4").Value = AleatoriosUnicos(Range("B1").Value, Range("B2").Value, Range("B3").Value)End Sub 34
Ejemplos VBA en ExcelGenerar Valores Aleatorios sin
Repetición
Private Sub CommandButton1_Click()
Dim fila As LongDim duplicados As Boolean
'Obtener la fila disponiblefila = Application.WorksheetFunction.CountA(Range("A:A")) + 1duplicados = False
'Validar si se han ingresado datos duplicadosFor i = 1 To filaIf Cells(i, 1).Value = UserForm1.TextBox1.Value ThenIf Cells(i, 2).Value = UserForm1.TextBox2.Value Then
'Se encontraron datos duplicadosMsgBox "Datos duplicados en la fila " & iduplicados = True
End IfEnd If
Next i
If Not duplicados Then'Insertar datos capturadosCells(fila, 1).Value = UserForm1.TextBox1.ValueCells(fila, 2).Value = UserForm1.TextBox2.Value
'Limpiar cajas de textoUserForm1.TextBox1.Value = ""UserForm1.TextBox2.Value = ""
'Notificar al usuarioMsgBox "Datos insertados en la fila " & fila
End If
End Sub
UserForm1.Show
Unload Me 35
Ejemplos VBA en ExcelFormulario para completar una
lista sin duplicados
Sub ValoresUnicos()Dim listaOrigen As Range
On Error Resume NextSet listaOrigen = Application.InputBox _(Prompt:="Rango de datos origen:", Title:="Seleccionar rango", Type:=8)
listaOrigen.AdvancedFilter _Action:=xlFilterCopy, CopyToRange:=ActiveCell, Unique:=True
Canceled:End Sub
36
Ejemplos VBA en ExcelCopiar valores únicos de un rango
Function APARICIONES(rango As Range, valor As Variant) As Integer
contador = 0
'Recorrer todas las celdas del rangoFor Each celda In rango.Cells
posicion = 1nuevoStr = celda.Value
'Mientras la posición encontrada por InStr sea diferente a ceroDoposicion = InStr(posicion, nuevoStr, valor, vbTextCompare)If (posicion = 0) ThenExit Do
Else'Aumentar contador de aparicionescontador = contador + 1'Cadena de texto restantenuevoStr = Mid(nuevoStr, posicion + Len(valor) + 1)
End IfLoop While posicion <> 0
Next
APARICIONES = contador
End Function37
Ejemplos VBA en ExcelContar apariciones de un valor en un rango
Sub ValorExiste()
'Definición de variablesDim rango As StringDim valor As StringDim resultado As RangeDim primerResultado As StringDim cont As Integer
'Solicitar información al usuariorango = InputBox("Ingresa el RANGO a buscar:")valor = InputBox("Ingresa el VALOR a buscar:")
'Inicializar contador de coincidenciascont = 0
'Primera búsqueda del valor dentro del rangoSet resultado = Range(rango).Find(What:=valor, _
LookIn:=xlValues, _LookAt:=xlWhole, _SearchOrder:=xlByRows, _SearchDirection:=xlNext, _MatchCase:=False, _SearchFormat:=False)
'Si el resultado de la búsqueda no es vacíoIf Not resultado Is Nothing ThenprimerResultado = resultado.Address
'Inicia bucle para hacer varias búsquedasDocont = cont + 1
'Cambia el color de fondo de la celdaresultado.Interior.ColorIndex = 6
'Vuelve a buscar el valorSet resultado = Range(rango).FindNext(resultado)
Loop While Not resultado Is Nothing And _resultado.Address <> primerResultado
End If
'Muestra un cuadro de diálogo con el número de coincidenciasMsgBox "Se encontraron " & cont & " coincidencias."
End Sub
38
Ejemplos VBA en ExcelContar y marcar apariciones de un valor
en un rango
Top Related