01 anexo 2 - vba en excel

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


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


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


16


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


22


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


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


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

01 anexo 2 - vba en excel

Documents