Página principal



Notas introductorias sobre vba

Descargar 52.37 Kb.

Notas introductorias sobre vba





Descargar 52.37 Kb.
Fecha de conversión22.05.2017
Tamaño52.37 Kb.





CITIUS

EXCEL – Lecciones de Perfeccionamiento

NOTAS INTRODUCTORIAS SOBRE VBA

(Estas notas no suponen, ni mucho menos, una guía para Excel-VBA, sino unos meros apuntes para una simple introducción al contexto de VBA)

PREVIOS

  • Añadir pestaña de Desarrollador (algunos procedimientos incluidos sin necesidad de editar BVA)

  • Puede ser necesario chequear (rebajar) el nivel de seguridad de Macros para ejecutar algunas macros (en la pestaña de desarrollador hay un icono de acceso a la seguridad de macros)

  • VBA permite automatizar procedimientos, crear formularios, crear nuevas funciones o configurar propiedades de libros y hojas

  • Los comandos básicos de BVA pueden aprenderse grabando macros de forma automática y editándolas después para observar algunas cuestiones

VENTANAS en el editor de VBA para Excel Alt+f11 (o con Icono de Visual Basic)

  • En esta ventana se definirán los procedimientos o funciones que después se invocarán, por su nombre, en la hoja de cálculo a través del menú de Macros o con una instrucción play desde el menú principal o desde el menú de macros

  • Además, servirá para editar macros grabadas previamente

Ventana de “secciones” (del Proyecto)

  • Los programas definidos en la sección de hojas concretas SÓLO funcionarán para esas hojas; si se quiere que funcionen en todas las hojas pueden definirse en la sección de Módulos o en la sección “thisWorkbook”

  • Los módulos pueden también exportarse o importarse si queremos utilizar alguna macro creada anteriormente

Ventana de comandos

  • Específica de cada “sección”

  • Aparece automáticamente al hacer doble click en cualquier sección de la ventana de proyecto

  • Permite albergar varios procedimientos (sub o function) (que aparecerán automáticamente divididos por una linea)

Ventana de propiedades

  • Define propiedades de cada sección

  • Pueden alterarse propiedades de hojas o del libro, relativamente más sofisticadas que en la configuración global del Excel, simplemente utilizando este menú (aunque no hagamos macros).

    • Por ejemplo, dado que las configuraciones afectan a todo el libro en general, es últil alterar algunas cosas a nivel de hoja como nombre “interno” de una hoja1, si se activa el cálculo o no2 ,….

    • A nivel de libro, hay docenas de opciones, como pedir que guarde o no información personal3, activar las opciones de envío del fichero por correo electrónico4 ……

EJEMPLO DE USO DE UNA MACRO GRABADA y EDITADA POSTERIORMENTE

  • Ejemplo 1:



Grabar una macro para copiar y pegar datos de otro fichero (por ejemplo los datos usado para la clase de Bases de Datos) en un fichero nuevo (fichero, abrir, localizamos fichero, abrimos, filtramos seleccionando sólo los datos de Alcobendas, marcamos el rango, copiamos, creamos un nuevo fichero, pegamos y paramos la macro).



  • Abrimos la macro en el editor de macros

  • Observamos cómo funciona el procedimiento de revisión de macros “paso a paso” (posicionados en cualquier punto de la macro pulsamos la tecla F8; sucesivas pulsaciones de F8 irán activando comando a comando)

  • Añadimos ahora un poco de código propio (al invocar la macro, el usuario recibirá un mensaje con “Esta macro importará los datos de Alcobendas”). Para ello:

    • Editamos la macro

    • Añadimos en la primera línea el comando “msgbox”, pulsamos espacio (para observar la ayuda del comando), escribimos el texto entre comillas y pulsamos enter. Veremos como el comando se reconoce al aparecer como “MsgBox” en lugar de “msgbox”. El comando es:

MsgBox "Traerá los datos de Alcobendas"

    • Añadimos ahora un condicional sencillo. Por ejemplo, vamos a decirle que, en la pregunta, muestre el botón Si/No y que si el usuario activa el botón NO salga del procedimiento.

If MsgBox("Traerá los datos de Alcobendas", vbYesNo + vbQuestion) = vbNo Then

Exit Sub

Else

(aquí el código de la macro)

EndIf

EnSub

  • ¿Es difícil añadir nuestras propias líneas?

    • Para empezar, podemos grabar procedimientos en nuevas macro y copiar el código dentro de otras macros. Por ejemplo, tratad de dar formato como una tabla al rango de datos importado. Grabad una macro con esa operación e insertar el código nuevo en la otra.

    • La ayuda de VBA de cualquier página más o menos seria nos servirá. Por ejemplo, ¿podéis buscar cómo cerrar el fichero fuente de los datos (el que hemos usado para importar) como orden final de la macro?.



  • Ejemplo 2:

Realizando un procedimiento iterativo / repetitivo

    • Imaginemos que queremos realizar una operación repetitiva como la siguiente. En la hoja tenemos una columna de datos con algunos huecos y queremos generar un total para cada sección de datos.





    • Lo primero es generar la macro grabada para un solo bloque de datos, por ejemplo para el que está más arriba en la columna. Empezamos situándonos en la celda C3, activamos las referencias relativas y grabamos la macro.

      • Asignamos a la macro una tecla de acceso rápido, por ejemplo Ctrl+M

      • Grabamos la secuencia de cálculo, incluyendo el formato rojo y negrilla para el subtotal

      • Procuramos que la grabación de la macro termine con nuestro cursor situado en un punto tal que la macro debería ejecutarse nuevamente (punto de iteración). En nuestra ilustración, ese punto de iteración sería la celda C14 que corresponde al inicio del segundo bloque de datos.

    • Una vez grabada la macro, comprobamos que funciona con cualquiera de los bloques de datos.

    • Si la macro funciona, las iteraciones pueden introducirse de manera sencilla con algún tipo de condicional / bucle, como por ejemplo:



Do While Not IsEmpty(ActiveCell)

(texto de la macro)

Loop



NOCIONES BÁSICAS DE SINTAXIS VBA

Generales:

  • Escribir en minúscula

  • Introducir comentarios (con ‘)

  • Las tareas se definen creando procedimientos Sub o funciones Function. Básicamente la diferencia es que:

    • la Función devuelve un resultado (por ejemplo tras sumar unas determinadas celdas)

    • y el procedimiento no (por ejemplo dar formato a unas celdas o suprimir automáticamente unas filas no devuelve ningún “resultado”).

  • Al final de cada tarea (sub o function) aparecerá (automáticamente) un End Sub o End Function



Variables

  • Se declaran con Dim seguido del nombre de variable y As seguido del tipo (“As” no es imprescindible, aunque sí recomendable)

Dim v1 As Integer

  • Pueden también declararse con Public o Private de modo que además identificamos si pueden usarse en todos los módulos (public) del libro o no

Sub()

  • Los paréntesis se usan para proveer, desde el inicio, valores / variables a los procedimientos

Sub Format_Centered_And_Sized (Optional iFontSize As Integer = 10)

La variable iFontSize se declara con un valor entero de 10 (más tarde se usará en el procedimiento)

Mensajes al usuario

  • El comando básico es MsgBox seguido del texto en comillas

MsgBox "Expected a Positive value of sVAT_Rate but Received " & sVAT_Rate

Condicionales

  • Hay dos commandos básicos

    • La cadena “if…then” – “elseif then” -“else” – “endif”



If ActiveCell.Value < 5 Then
      
ActiveCell.Interior.Color = 65280  ' Color cell interior green
  ElseIf ActiveCell.Value < 10 Then
      ActiveCell.Interior.Color = 49407  ' Color cell interior orange
  Else
      
ActiveCell.Interior.Color = 255    ' Color cell interior red
  End If



    • La cadena “select case” – “case is / case”

Select Case ActiveCell.Value

Case Is <= 5

ActiveCell.Interior.Color = 65280 ' Color cell interior green

Case 6, 7, 8, 9

ActiveCell.Interior.Color = 49407 ' Color cell interior orange

Case 10

ActiveCell.Interior.Color = 65535 ' Color cell interior yellow

Case Else

ActiveCell.Interior.Color = 255 ' Color cell interior red

End Select

Bucles



  • Son importantes porque son estructuras muy usadas para programar tareas automatizadas

  • Hay tres órdenes principales para activar bucles:

    • For …. Next (con o sin el modificador Step)



Dim i As Integer
For
i = 1 To 60
    Cells(i, i).Value = 100*i
Next
i



Otro ejemplo de un for…next triple



Dim c As Integer, i As Integer, j As Integer

For c = 1 To 6

For i = 1 To 6

For j = 1 To 2

Worksheets(c).Cells(i, j).Value = 100

Next j

Next i

Next c



    • “For Each …. Next” Es igual que el loop for-next pero en lugar de correr sobre todos los valores de una variable definida en el “for” corre sobre los objetos de una determinada lista (un rango de celdas de la hoja, por ejemplo)



(PONER UN RANGO CON CÓDIGOS CON GUIONES. LA MACRO ELIMINA LOS GUIONES)



For Each MyCell In Range("A2:A6")

MyCell.Value = Replace(MyCell.Value, "-", "")

Next MyCell



    • “Do … While” – “Loop” Ejecuta una sección de comando MIENTRAS se cumpla una determinada condición


Dim i As Integer
i = 1
Do While
i < 6
    Cells(
i, 1).Value = 20
    i = i + 1
Loop



    • “Do … Until” – “Loop” Similar al anterior, ejecuta un comando HASTA que se verifique una condición

Un ejemplo con IF y Case:

If NIF = 0 Or NIF > 99999999 Then

LetraNIF = "NIF incorrecto"

Else

'Dividimos el NIF entre 23, y nos quedamos con el resto +1

resto = (NIF Mod 23) + 1

Select Case resto

Case 1, 24

LetraNIF = "T"

Case 2

LetraNIF = "R"

Case 3

LetraNIF = "W"

Case 4

LetraNIF = "A"

Case 5

LetraNIF = "G"

Case 6

LetraNIF = "M"

Case 7

LetraNIF = "Y"

Case 8

LetraNIF = "F"

Case 9

LetraNIF = "P"

Case 10

LetraNIF = "D"

Case 11

LetraNIF = "X"

Case 12

LetraNIF = "B"

Case 13

LetraNIF = "N"

Case 14

LetraNIF = "J"

Case 15

LetraNIF = "Z"

Case 16

LetraNIF = "S"

Case 17

LetraNIF = "Q"

Case 18

LetraNIF = "V"

Case 19

LetraNIF = "H"

Case 20

LetraNIF = "L"

Case 21

LetraNIF = "C"

Case 22

LetraNIF = "K"

Case 23

LetraNIF = "E"

End Select

End If

End Function



Ejemplos con algunos COMANDOS BÁSICOS



  • Seleccionar una hoja, celda o rango (Select)



Normalmente las instrucciones básicas utilizan el comando “select” para activaR las hojas y los objetos (celdas, rangos,…) a los que después aplicará procedimientos. Una vez aplicado el comando “select”, la instrucción “selection” “selection” o “ActiveCell” devuelven el objeto activo sin necesidad de nombrarlo.

Cells(4,4).Select ‘ Selecciona (no copia!!!) la celda 4,4 (celda d,4)

Range("D4").Select



Sub Macro1() Escribe “Nombre” y “Apellidos” en las celdas A1 y B1

Sheets("Hoja1").Select

Range("A1").Select

ActiveCell = "Nombre"

Range("B1").Select

ActiveCell = "Apellidos"

Range("A1:B1").Select

Selection.Font.Bold = True

End Sub



  • Desplazarse con referencias relativas



El comando anterior “select” sirve para desplazarse a la celda que necesitemos pero implica una referencia absoluta. Si queremos indicar un desplazamiento relativo podemos utilizar el comando “offset” referido al objeto activo. Offset desplaza la selección utilizando dos argumentos (fila y columna) respecto a la celda activa:



Activecell.0ffset(0,0).select 'Selecciona como celda activa el lugar donde estamos

Activecell.0ffset(1,0).select 'ahora la celda activa se encuentra una fila por debajo

Activecell.0ffset(0,1).select 'ahora una columna por la derecha

Activecell.0ffset(0,-1).select 'ahora una columna por la izquierda



Por ejemplo, el sub siguiente calcula la tasa de variación de dos cifras colocadas una encima de otra (y nos colocamos a la derecha de la cifra final)



Sub tasa()



b = ActiveCell

a = ActiveCell.Offset(-1, 0)

If a = 0 Then

MsgBox "Error: Division por cero"

Else

ActiveCell.Offset(0, 1).Select

Selection = b / a - 1

Selection.Style = "Percent"

Selection.NumberFormat = "0.0%"

End If



End Sub





  • Fórmulas con referencias relativas:



Al hilo de lo anterior, si queremos “cargar” un objeto (celda, por ejemplo) con un cálculo (como en el caso del ejemplo de la tasa), podemos directamente utilizar el modo de referencia F1C1 con el comando FormulaR1C1



Por ejemplo, para el cálculo de la tasa podríamos haber usado:



Sub tasa()



ActiveCell.FormulaR1C1 = "=+RC[-1]/R[-1]C[-1]-1"



End Sub



Hacemos ahora un ejemplo con un bucle y una referencia de cálculo relativa (para calcular tasas para toda una serie)



Sub tasaserie()



Do While Not IsEmpty(ActiveCell)

ActiveCell.Offset(1, 0).Activate

ActiveCell.Offset(0, 1).FormulaR1C1 = "=+RC[-1]/R[-1]C[-1]-1"

Loop

ActiveCell.Offset(0, 1).ClearContents



End Sub



  • Copiar el contenido de una celda (2 alternativas)

Cells(1,1).Copy ‘Copia la celda 1,1 (fila 1, columna 1 = A1)

Range("A1").Copy ‘Copia la celda A1

  • Pegar (paste) Paste



Ejemplo:

Este programa copia y pega el contenido de la celda a1 en la celda d4

Sub otro()

Cells(1, 1).Copy

Cells(4, 4).Select

Sheets("Hoja1").Paste

End Sub



  • Bloques de procedimientos (comando With)



Muy utilizado porque permite acumular procedimientos para un mismo objeto sin tener que referirnos repetidamente a él.



Sub aver()

With Range("A2").Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 65438

.TintAndShade = 0

.PatternTintAndShade = 0

End With

End Sub







Sintaxis de prácticas elaboradas en clase

Ejemplo traer datos



Sub traerdatos()

'

' traerdatos Macro

'

'

If MsgBox("Traerá los datos de Alcobendas", vbYesNo + vbQuestion) = vbNo Then

Exit Sub

Else

ChDir "C:\Users\RAMON\Desktop"

Workbooks.Open Filename:="C:\Users\RAMON\Desktop\Copia de bases_datos2-2.xls"

Range("E12").Select

Selection.AutoFilter

ActiveSheet.Range("$A$11:$F$139").AutoFilter Field:=5, Criteria1:= _

"Alcobendas"

Range("A11").Select

Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Copy

Windows("Libro1").Activate

Range("A1").Select

ActiveSheet.Paste

Workbooks("Copia de bases_datos2-2.xls").Close SaveChanges:=False

End If

End Sub



Ejemplo macro repetitiva



Sub Macro1()

'

' Macro1 Macro

'

' Acceso directo: CTRL+m

'

Do While Not IsEmpty(ActiveCell)

Selection.End(xlDown).Select

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"

ActiveCell.Select

With Selection.Font

.Color = -16776961

.TintAndShade = 0

End With

Selection.Font.Bold = True

Selection.Cut

ActiveCell.Offset(0, 1).Range("A1").Select

ActiveSheet.Paste

ActiveCell.Offset(1, -1).Range("A1").Select

Loop



End Sub

1 El usuario podría haber cambiado el nombre de la hoja interna “hoja1” que nosotros llamamos en su día “datos” por el de “mis datos”, por ejemplo, y entonces una macro con el comando Sheets("Datos").Activate ya no funcionará. Sin embargo, si usamos “hoja1.Activate” siempre funcionará.


2 EnableCalculation=False

3 RemovePersonalInformation = True

4 EnvelopeVisible=True



Similar:

Notas introductorias sobre vba iconExcel vba avanzado
La programación de macros con lenguaje Visual Basic (vba) es apta para cualquier
Notas introductorias sobre vba iconPasos para crear formularios en excel con vba
Los formularios en vba son objetos contendores donde se pueden colocar controles que ayudarán
Notas introductorias sobre vba iconNotas explicativas sobre cómo rellenar el formulario de correcciones o adiciones a registros de manuscritos
Para indicaciones multilingües sobre el vocabulario codicológico, aunque con descripciones
Notas introductorias sobre vba iconBase de datos notas
Un profesor de Instituto desea llevar el control de notas de sus alumnos, nos ha hecho los siguientes comentarios
Notas introductorias sobre vba iconTurno de oficio y ald 1 Notas aclaratorias sobre la justificación de asuntos relacionados al turno de oficio
Notas aclaratorias sobre la justificación de asuntos relacionados al turno de oficio
Notas introductorias sobre vba iconDirbibmex
Un directorio y una guía bilingüe con notas que contiene información básica sobre una amplía gama de recursos sobre el campo de la...
Notas introductorias sobre vba iconEjemplo de Formulario vba en Excel En esta ocasión haremos un ejemplo básico de cómo utilizar un formulario vba
En esta ocasión haremos un ejemplo básico de cómo utilizar un formulario vba para desarrollar una mini-aplicación en Excel que realice...
Notas introductorias sobre vba iconB ibliografía y bioid
Notas explicativas sobre el formulario de correcciones o adiciones a fichas biográficas


Descargar 52.37 Kb.