Teclas rápidas
WINDOWS-E abre el explorador de archivos
CTRL-SHIFT-N: Crea una nueva carpeta. en el explorador en vista se puede marcar que se vean los archivos con extensiones
CTRL-Q:Seleccionando varias celdas crea una tabla
ALT-=:Seleccionando varias celdas con números crea totales hacía todos lados
CTRL-S Guardar
F12 Guardar Como
Excel Basics 1
Celdas con fòrmulas se ponen en verde
Títulos de columnas con DarkBlue y blanco
Datos que sirvan como input de fórmulas en Rojo con Blanco su título
Cell = intersaction between row and column
Conjunto de celdas es un Worksheet
Se puede colorear las etiquetas de las hojas(sheet). Amarillo datos informativos, Azul para los datos de trabajo, rojo para resultados o reportes
Desplazarse entre hojas: Clic derecho sobre las flechas de desplazamiento y selecciono la hoja. CTRL-PgUp CTRL-PgDown Igual se desplaza entre las hojas
CTRL BackSpace: Regresa a la celda activa
CTRL DownArrow: Última celda con datos
CTRL Shift DownArrow: Selecciona todoas las celdas hacía abajo
CTRL-SCROLL para hacer Zoom IN, Zoom-Out
CTRL-ENTER Put something in the CELL and keep the CELL selected
Align numbers, fecha, horas to the right, text to the left, boolean centrados. Si los números son Texto no se suman
Fn-ESC: En la laptop nos permite accesar las teclas de función sin el Fn
TAB Desplazo hacia la derecha por las celdas
ENTERDesplazo hacía abajo en las celdas
#VALUE! Cuando un valor de la fórmula no es correcto "25+Jose"
table:Es el espacio que existe entre muchas columnas con encabezado hasta que no hay nada y un montòn de datos hacìa abajo hasta que no hay nada
CTRL - * (asterisk key)Selecciona datos en tabla
Celdas seleccionadas Me muevo con TAB y ENTER derecha y abajo por la selección
Mini toolbar Clic derecho sobre celdas seleccionadas
Angry Rabbit Cruz esquina inferior derecha, jalándola puedo generar consecutivos de meses, números
Bricklin and Frankstone Crearon el spreadsheet
=Primer carácter en una fórmula
Relative References Las que si copio la fórmula, cambiando dependiendo la posición
Absolute References No cambian y se crean con F4 que se pone antes de la celda que no debe cambiar
Number Formating Es diferente que Style Formating porque sólo afecta números
Number Formating Una cosa es el contenido de la celda y otra es como se muestra por el formato (facade-fachada)
Decrease decimals Redondea
General Format Elimina cualquier formato de número
CTRL-P Print Preview
Page LAYOUT Toolbar de impresión, vista-diseño de página
ALT Se aparecen las teclas rápidas de los menues principales, luego lo presiono y aparecen las de los sub menúes. Además muestra
con número para accesar las opciones de la barra de herramientas
Configurar Página: Está en Ajustar área de impresión ALT-C-F, Se separan los rangos de las áreas de impresión por comas
Área de Impresión: Selecciono las celdas que quiera imprimir en una hoja.
Puedo agregar áreas de impresión e irán en hojas separadas.
Margenes personalizados Encabezado y pie de página personalizados esto se
puede ver en el Configurar Página. Se puede poner número de página, fecha, nombre
Sheet Se puede indicar el área de impresión, voy a ver todas las áreas de impresión que agregué.
Puedo agregar separando por comas
Repetir filas en el extremo superior Si quiero que los mismos renglones se repitan en diferentes hojas de impresión
CTRL Se deja presionada para hacer diferentes selecciones, marcar celda con fórmulas Selecionar renglones y columnas arrastrando el mouse
Move or Copy Sheet Seleccionar el sheet, clic derecho y copiar o mover
CTRL Si se deja presionado y se arrastra la hoja, la copia sin el CTRL la mueve
F2 Revisa las celdas que componen una fòrmula
Con el nombre de la fòrmula, me desoliegan y puedo ver el help de cada una
Excel Basics 2
DEL Para borrar lo que pone el autocompletar cuando tecleo valores parecidos en una fila
CTRL-1 Abre el cuadro de diálogo para formatear celdas
Center Across Selection (CTRL-1) En lugar de hacer merge en celdas
Borde Outline - contorno Para el que se centra (Selecciono celdas y CTRL-1)
Charts Selecciono el rengón de títulos y luego el renglón de montos para cada título y luego la gráfica (abcisas y ordenadas)
Graph Lines Las puedo seleccionar para eliminarlas con DEL
Título de gráfica Seleccionarlo y se le puede capturar, arriba en las´fórmulas, una fórmula para igualarlo a una celda
Si muevo la gráfica presionando la tecla ALT, se alinea a una celda
Chart Elements Le hago clic una vez, y selecciona los de un tipo y los puedo borrar, dos veces selecciona solo uno
Colores CTRL-1 Seleccionamos una barra y CTRL-1 y podemos ver el seleccionar colores entre punto o multiple colors
Excel Basics 3
COUNT:Cuenta las celdas que tienen números y que no son vacías
COUNTA:Cuenta las celdas que no son vacías, o sea que hasta los títulos
Se deben utilizar las funciones CONJUNTO
COUNTIFS:(Conjunto) Se puede poner más de un criterio para que cuenta
SUMIFS:(Conjunto) Se puede poner más de un criterio para que sume. Rango de suma, rango de comparación y criterio
Si le pones espacios a los extremos, EXCEL no hace TRIM y no lo cuenta
En la ayuda que aparece cuando tecleo la fórmula, puedo hacer clic sobre el parámetro para que me lo seleccione
Excel Basics 4
Data Analysys or business Inteligence:Ir del Raw Data a Información Útil (acomodarla)
Double Click para que se copien hacía abajo las fórmulas
Tabla dinámica (Pivot Table):En el menú Insert-Tabla Dinámica. Antes puedo seleccionar una celda de la tabla RAW para que seleccione ese rango
Elijo el campo que le voy a hacer distinct o unique list a mi tabla dinámica
Aparece el cuadro de configuración de fields, ahí elijo los campos que son valores o filas o columnas
Si me pongo sobre alguna de las celdas de la tabla aparece el menú de "Herramientas de Tabla Dinámica" (Pivot Table tools)
En diseño-diseño de informe (Report Layout)
Clic derecho sobre la celda de la columna que quiero formatear y le doy Number-Formatting, no le doy formato de celda porque no es para una PIVOT TABLE
Los campos se van arrastrando (Dragging) para configurar la tabla
Los campos con fòrmula se actualizan en automàtico cuando cambia cosas en la tabla raw, pero las tablas dinàmicas, se les debe hacer clic derecho y actualizar
ALT-B-BA Teclas rápidas
Across Tab:Tabla cruzada poniendo títulos en filas y renglones
Puedo estar cambiando entre filas y columnas y se va actualizando. Incluso hace las agrupaciones
Al igual que el formato, dando clic derecho sobre una celda, puedo cambiar la funciòn de agregación de esa celda
Si poner un campo texto en valores, entonces hace COUNT
Seleccionando la tabla, la puedo copiar y pegar
CTRL-HOME:Se va a la celda inicial
CTRL-ARROW (cualquier): Se va a la última celda con valor en esa dirección. Me puedo poner enmedio de las funciones de agregación
para señalar rangos
CTRL-Bakspace: Regreso a la primar celda de mi selección
Si agrego una columna de filtro, arriba se agrega un drop down
Insert Slicer (Insertar filtro visibles):En analizar, insertar segmentación de datos
Se les puede poner el diseño automático
Seleccionando el slicer, se pueden cambia el número de columnas
Excel Basics 6
CTRL-F1 Muestra la barra de herramientas
Customize Qucik Access Toolbar(QAT):Le puedo dar clic derecho a los íconos y las opciones y agregarlos a la barra de acceso rápido
Clic derecho hasta arriba y le digo si la quiero ver debajo o encima de la barra de herramientas la QAT
Le puedo dar restablecer
Pegar imagen vinculada
Escenario
Speak Cells on enter
Hay que quitar las que no sirven, como guardar, hacer y deshacer, porque ya sabemos las teclas rápidas
Puedo pegar la tabla como imagen vinculada y cuando cambio algo en la tabla origen la imagen se actualiza
Customiza Ribbon (Personalizar la cinta de opciones):Puedo agregar grupos
Excel Basics 6
En el grupo de estilos, clic derecho y puedo ver la definición, como si fuera su CSS
Por default:
Textos: Se alinean a la izquierda
Números: Se alinean a la derecha
Booleanos: Se alinean al centro
Errores: Se alinean al centro
Me pongo en la división entre los TAB de hojas y el SCROLL horizontal y puedo agrandar o reducir el SCROLL para que se vean más las hojas
La fórmulas no consideran como están formateados los números
Accounting Number format: Signo de pesos fijo, Negativos entre paréntesis, Ceros en guiones, los décimales alineados o sea que los
números no necesariamente en la extrema derecha
Revisar como alinea Número, Moneda y contabilidad
El formato general de número elimina todos los formatos cuando se cambia
Redondear:Nos puede servir para cuadrar porcentajes. Y en cantidades siempre hay que redondear
Ejercicio 1. Hacer macros
Activar la barra de desarrollador o programador
- Clic derecho sobre la barra de herramientas (También se puede contraer la cinta de opciones o se puede indicar lo contrario)
- Personalizar la cinta de opciones
- Mostrar la opción de programador
- En Datos Obtener datos externos
- Elegir fuente de datos (SQL Server)
Grabar una macro que importe desde un origen de datos
Puedo elegir en libro personalizado pero es raro para lar macro
Crear un gráfico, cambiarle el título y colores
Generar un reporte sumarizado
Mandarlo a imprimir
Generar una macro que lo haga
Revisar que al guardar el documento pulsar NO para guardar las macros en libro para macros
Puedo apoyarme de vistas
Para que no marque error al ejecutar dos veces cambiar al VBA las siguientes líneas aumentando un contador
Habría que ver si ya existe que se reuse
'Aumentar el contador (ActiveWorkbook.Queries.Add Name:="t01_csi_"+cstr(ActiveWorkbook.Queries.Count+1))
ActiveWorkbook.Queries.Add Name:="t01_csi_" + CStr(ActiveWorkbook.Queries.Count + 1), Formula:= _
'Quitar la siguiente línea
'ActiveWorkbook.Worksheets.Add
'Aumentar el contador
.ListObject.DisplayName = "t01_csi_" + CStr(ActiveWorkbook.Queries.Count + 1)
Macro que genere muestras
=SECUENCIA(10000)
=ALEATORIO.ENTRE(1,20)
Cells.Select 'Selecciona todo
Selection.ClearContents 'Elimina el contenido
ActiveCell.FormulaR1C1 = "Indice"
ActiveCell.Formula2R1C1 = "=SEQUENCE(20000)"
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(1,5)"
'Copia todas las celdas seleccionadas
Selection.Copy
'Pegar valores
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Ejercicio 2 - Centro de confianza
Si oprimo el botón de habilitar contenido, la macro queda habilitada para siempre si está habilitado el permitir documentos de confianza
Si cambio el nombre o ubicación del archivo pide de nuevo confirmar la confianza
Configuración macros desde desarrollador
- Entrar en seguridad de macros en programador (También opciones-opciones de Excel-centro de confianza)
- Opción de Documentos de confianza
- Deshabilitar documentos de confianza: Si lo marco siempre pide confirmar confianza
- Borrar todo los documentos de confianza para que dejen de serlo
- Opción de Configuración de macros
- Deshabilitar todas las macros sin notificación, ya no me doy cuenta que las macros están deshabilitadas
- Deshabilitar macros con notificación, por defecto, opción más indicada
- Deshabilitar todas las macros excepto firmadas digitalmente para certificados
- Habilitar todos las macros (no recomendado)
Si se cambia el nombre del documento vuelve a pedir se confirme la confianza
Ejercicio 3 Personalizar ejecución de macros
Clic derecho sobre la conta de opciones - Personalizar cinta
Ejecutar desde la cinta de opciones
Agrego Pestaña y grupo, renombro
Agregar macros al grupo
La pestaña va a seguir siempre disponible en todos los documentos
Hacer una con mis funcionbes más comúnes
Buscar cinta de EXCEL personalizada con XML y VBA
Para poner un botón que solo se active con el documento y no siempre
- Clic derecho en la barra superior de acceso rápido
- Más comenados
- Barra de herramientas de acceso rápido
- Del lado derecho arriba, pongo documento predeterminado o en cuál quiero que se vean los botones
- Elijo el comando o macro a agregar
Insertar la macro en un botón de formulario de hoja de EXCEL
- En desarrollador insertar control de formulario
- Controles de formulario son pobres es mejor ActiveX
- Insertar el botón de control de formulario
- Elegir macro y personalizarlo
- Posicionamiento de botón, para que quede fijo si elimino celdas
- En la lista de macros - opciones, se pone tecla rápida
- Utilizar letras mayúsculas que no se estén usando
Usar referencias relativas o absolutas
Cuando se graba la macro, podemos indicar que se usen referencias relativas en el botón, de lo
contrario por defecto se utilizan absolutas
Grabar una macro que inserte un renglón con títulos arriba de una matriz
Grabar la misma macro pero con celdas relativas
'Hace referencia absoluta
Range("A1")[.Select][.Value]
Sheets("Unidades Producidas").Select
'Para que no pida confirmaciones
Application.DisplayAlerts = False
'Elimina la hoja seleccionada
ActiveWindow.SelectedSheets.Delete
'Para que pida confirmaciones
Application.DisplayAlerts = True.
'Selecciona las columnas A y B
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
'Para que la macro no vaya actualizando la ventana mientras
'se ejecuta y así es más rápida. Puede quitar el parpadeo
Application.ScreenUpdating=False
'Ponerlo en true para activarlo
Application.ScreenUpdating=False
Formula bisiesto: =SI(O(Y(RESIDUO(K1,4)=0,RESIDUO(K1,100)<>0),RESIDUO(K1,400)=0),"Si","No")
'con celdas relativas
'Inserta un renglón donde este posicionado
Selection.EntireRow.Insert
'Escribe el texto
ActiveCell.FormulaR1C1 = "Nombre"
'Se mueve cero abajo, uno a la derecha
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Paterno"
'Se mueve cero abajo, uno a la derecha
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Materno"
'Se regresa a la izquierda los dos que avanzó
'Selecciona A1:C1 pero del rango seleccionado, no de la hoja
ActiveCell.Offset(0, -2).Range("A1:C1").Select
'Lo pone en negritas
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
A1 de la selección y A1 de la hoja, son cosas diferentes
Range("F5,C8:C10,F10,B13:F30").Select - Permite seleccionavarios rangos o celdas
Selection.ClearContents - Borra el contenido de las celdas seleccionadas
Programar en EXCEL (VBA)
ALT-F11 o botón de Visual Basic en desarrollador: Acceder al editor de código VB
Barra estandar y de edición, clic derecho y las puedo seleccionar
Los módulos son funciones o subrutinas generales
Si le hago doble clic sobre alguna hoja, es código solo de esa hoja o sobre el workbook,
es el ámbito
en el menú Insertar, un userForm
Se pueden insertar módulos de clase para las clases
Programación basada en objetos no programación a objetos
Las hojas de EXCEL tienen dos nombres, el interno y el externo.
El interno es el que ve el objeto y
el externo la descripción que le asignamos a la hoja (no es bueno cambiarlo)
Ver el tab de propiedades (F4)
xlSheetVisible, Permite ver la hoja
xlSheetHidden, no permite ver la hoja pero con la opción de mostrar de EXCEL se puede ver de nuevo
xlSheetVeryHidden, no deja que se muestra la hoja ni con la opción de mostrar de EXCEL
Procedimientos:Sub - End Sub
Funciones:function - End function
Agregar un módulo y cambiar nombre
Option Explicit para que pida declaración
Un procedimiento equivale a una macro
Existe el bloque con comentarios en la barra de edición
Se ejecuta desde la opción de macros
Sub RellenarNumeroAleatorio()
On Error GoTo SubError
' Con el icono de coimentar
' se puede de a varias
Dim Celda As Range
Dim iCuenta As Integer
For Each Celda In Selection
If Celda.Value = "" Then
iCuenta = iCuenta + 1
Celda.Value = iCuenta
Celda.Interior.Color = vbGreen
End If
Next
SubError:
Resume Next
End Sub
Insertar un botón ActiveX y cambiar sus propiedades en modo diseño
doble clic y evento del botón. La rutina en un módulo
clic derecho en el nombre del procedimiento y definición y me lleva a su código
Range("A0:A20").ClearContents
Range("F5").ClearContents
Range("F5").Select
Range("F5").Value Este Value es opcional porque es su propiedad de defecto
Range("F5").Value = Date
Condiciones
Elijo una hoja, doy doble clic y me aparece su evento por defecto. SelectionChange
Elegir el evento Change. Practicar con la ventana Immediate (CTRL-G)
Private Sub Worksheet_Change(ByVal Target As Range)
'Este evento se ejecuta cuando se cambia un valor en la hoja
'Para que solo funcione en la columna A a partir del renglón2
If Not (Target.Column = 1 And Target.Row >= 2) Then Exit Sub
'Target es de tipo Range, es la celda
If IsDate(Target.Value) Then
Target.Interior.Color = vbGreen
'En la celda de la derecha si es fecha escribe en formato
'2 a la derecha escribe el día
Target.Offset(0, 1).Value = Format(Target.Value, "yyyymmdd")
Target.Offset(0, 1).Interior.Color = vbYellow
Target.Offset(0, 2).Value = Format(Target, "dddd")
Target.Offset(0, 2).Interior.Color = vbBlue
'Si es sábado o domingo, probar immediate
If (Weekday(Target) = 7 Or Weekday(Target) = vbSunday) Then
Target.Interior.Color = vbMagenta
End If
Else
'Si no es fecha lo rellena en otro color y regresa el foco a la
'celda para que vuelva a capturar
Target.Interior.Color = RGB(100, 30, 100)
'Si no quiero color
'Target.Interior.Color = xlNone
'Vuelvo a seleccionar la celda
Target.Select
MsgBox "No es una fecha", vbCritical, "Error de Captura"
End If
End Sub
vbCritical, vbInformation, vbQuestion, vbExclamation
vbRed, vbCyan, vbBlue, vbBlack, vbGreen, vbYellow, vbMagenta, vbWhite, xlNone
activecell.Interior.Color
If Target.Address="$A$3" And (IsDate(Target)=False or Target>Date): Si capturo la A3 ya
no es fecha o la fecha es mayor a hoy
Range("A1").Address, esto regresa $A$1
Target<>"" Vemos si es vacío
En el Target debemos tener cuidado porque pueden llegar varias celdas en el evento Change.
Por ejemplo si hago un Range("A0:A20").ClearContents
Application.EnableEvents = False, desactivo los eventos
Con esto desactivo los eventos mientras ejecuto instrucciones de rango
Application.EnableEvents = True, activo los eventos. En caso de que se queden
desactivados por error, en la ventana immediate podemos ejecutar que se active y
corregir el código
Desactivar los eventos en los botones para que si tengo programación en el Change o algún
otro evento, no marque errores
If Then [ElseIf..] [Else] End If - Cuando se cumple una condición ya no se evalúa el resto
Cuando se cumple una de las condiciones ya no evalua el resto
Select Case x
Case 2:
Case 3 to 5:
Case 8,10,12 to 15:
Case is >1000:
Case is <= Range("A1"):
Case Else:
End Select
Variables
En el editor de Basic - Herramientas-Opciones-Requerir declaración de variables (option explicit) aparece a partir de que se activa
Usar el debugger y la ventana immediate
'Se pueden poner botones de varios tipos
'vbOkCancel, vbAbortRetryIgnore, vbOkOnly, vbRetryCancel, vbYesNoCancel, vbYesNo
Dim iRes As Integer
iRes = MsgBox("Está seguro de querer eliminar la hoja: " + ActiveCell, vbCritical + vbYesNo + vbDefaultButton2, "Cuidado.....!")
If iRes = vbYes Then
Application.DisplayAlerts = False
'Elimina la hoja con el nombre de la celda activa
Worksheets(ActiveCell.Value).Delete
Application.DisplayAlerts = True
ActiveCell.Delete
'Número de hojas en el libro
MsgBox "Sobran " + CStr(Worksheets.Count) + " hojas", vbInformation
'Selecciona la hoja 2
Worksheets("Hoja1").Select
End If
Trabajar con InputBox
Dim sPlanta as String * 10 De tamaño 10. Además va a rellenar de blancos al final cuando el string no tenga el tamaño
Resp = InputBox("Introduce la fecha", "Fecha de Consulta", Date) 'Date es el valor por default, Regresa un String
Cadena=Cadena & "Concatenando" & vbCrLf
vbCrLf - Constante de saldo de línea
Se posiciona en la Última fila de EXCEL. Con Worksheets("Hoja2") podría indicar en que hoja se guarda
Private Sub btnInsNombre_Click()
Dim Fila As Long
Fila = ActiveSheet.Cells(1048576, "B").End(xlUp).Row + 1
Cells(Fila, "B") = "Daniel José"
Cells(Fila, "C") = "Corona"
Cells(Fila, "D") = "Carvajal"
End Sub
Private Sub btnStatic_Click()
'La variable static se puede ver de todos lados
Static iFila As Long
'Todas tiene que tener el tipo de dato indicado o se convierten a variant
'curp es variable de tipo variant
Dim Nombre As String, Paterno As String, Materno As String, curp
If iFila = 0 Then
iFila = ActiveSheet.Cells(1048576, "E").End(xlUp).Row
Else
iFila = iFila + 1
End If
Cells(iFila, "E") = "José"
Cells(iFila, "F") = "Corona"
Cells(iFila, "G") = "Alcántara"
End Sub
Application.InputBox - Es de EXCEL en vez de VB
Application es el objeto de mayor presedencia en EXCEL
Al siguiente InputBox se le indica el tipo de dato que se quiere ingresar
Argumentos por posición
prA = Application.InputBox("Introduce la fecha de la producción", "Fecha.", Date, , , , , 2)
Format(Date,"dd/mm/yyyy") - Mandar en valor por default para que aparezca la
fecha en formato correcto:
prA = Application.InputBox("Introduce la fecha de la producción", "Fecha.", Format(Date,"dd/mm/yyyy"), , , , , 2)
Argumentos por nombre
prA = Application.InputBox(Prompt:="Introduce la fecha de la producción", Title:="Fecha.", Type:=2)
Set arrCeldas = Application.InputBox(Prompt:="Seleccione festivo", Title:="Seleccionar Festivos", Type:=8)
0-Fórmula
1-Número
2-Texto
4-Boolean
8-Referencias a Celdas
16-Error (#N/A por ejemplo)
64-Matriz
Las variables Object por default son nothing
Private Sub btnMarcarVacios_Click()
On Error GoTo subError
Dim arrCeldas As Range
Dim Celda As Range
'Set arrCeldas = Selection
Set arrCeldas = Application.InputBox(Prompt:="Seleccione las celdas", Title:="Pintar celdas vacías", Type:=8)
For Each Celda In arrCeldas
If Celda = "" Then Celda.Interior.Color = vbRed
Next
'Hay que terminar la subrutina para que no ejecute lo de error
Exit Sub
'Exit function
subError:
'Se presionó el botón cancelar
If Err.Number = 424 Then Exit Sub
Range("A1") = "Ocurrió un error " & vbCrLf & "Número: " & Err.Number & vbCrLf & "Mensaje: " & Err.Description
End Sub
Ambito de variables, locales y globales, constantes
Las variables de módulo son globales
Public (en vez de dim) y en el módulo para ser global
Public varGlobal as Interger. Si se utiliza Dim solo se usa dentro del módulo
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Se ejecuta antes de cerrar el documento
MsgBox "Hasta luego"
End Sub
public Const rojo As Long=8696052
public Const maxRen As Long=1048576
lastRen = ActiveSheet.Cells(maxRen, "A").End(xlUp).Row
Bucles (ciclos iterativos)
for to [step] next [variable]
select case - case [is] - end select
Cortar, pegar, eliminar y ocultar celdas
Private Sub btnColorear_Click()
Dim iRen As Integer
Dim iCol As Integer
Dim maxRen As Integer
maxRen = ActiveSheet.Cells(1048576, "A").End(xlUp).Row
'Para saltarme los títulos porque estoy usando offset
maxRen = maxRen - 1
LimpiarCeldas
For iRen = 1 To maxRen
For iCol = 2 To 5 'Step 1
Select Case Range("A1").Offset(iRen, iCol)
Case Is = Range("I3")
Range("A1").Offset(iRen, iCol).Interior.Color = Range("I3").Interior.Color
Case Is = Range("I2")
Range("A1").Offset(iRen, iCol).Interior.Color = Range("I2").Interior.Color
Case Is < Range("I4"):
Range("A1").Offset(iRen, iCol).Interior.Color = Range("I6").Interior.Color
Case Is > Range("I4"):
Range("A1").Offset(iRen, iCol).Interior.Color = Range("I5").Interior.Color
End Select
Next 'iCol
Next iRen
'Cortar y pegar información
'Corta el contenido de B2 a C2
'Range("B2:C2").Cut
'Selecciona E2
'Range("E2").Select
'Pega lo de B2:C2 a partir de E2
'ActiveSheet.Paste
'Elimina este renglón. Debo buscar los vacíos desde el último
'ActiveSheet.Rows(iRen).Delete
'Ocultar filas
'ActiveSheet.Rows(iRen).Hidden = True
'Activar y desactivar el boton
'btnColorear.Enabled = False
'Mostrar renglones
'ActiveSheet.Rows.Hidden = False
'Activar y desactivar parpadeos
'Application.ScreenUpdating=true
End Sub
Private Sub LimpiarCeldas()
Dim iRen As Integer
Dim iCol As Integer
Dim maxRen As Integer
maxRen = ActiveSheet.Cells(1048576, "A").End(xlUp).Row
For iRen = 2 To maxRen
For iCol = 3 To 6
Cells(iRen, iCol).Interior.Color = xlNone
Next iCol
Next iRen
End Sub
Private Sub btnLimpiar_Click()
LimpiarCeldas
End Sub
For each - Next para recorrer colecciones
Dim Celda as Range
'Range("B:B") 'Inserta solo una columna en la B
Range("B:D").Insert 'Inserta tres columnas a partir de la B. Lo que este en B se desplaza hasta la E
Range("B1")="Número"
Range("C1")="Fecha"
Range("D1")="País"
Range("B1:D1").Interior.Color=vbBlack
Range("B1:D1").Font.Bold=True
Range("B1:D1").Font.Color=vbwhite
Range("B1:D1").HorizontalAlignment=xlCenter
'De "A2" hacía la Última celda con datos en "A2" que encuentre una vacía
'Range("A2").End(xlDown) última celda con datos hasta encontrar la vacía
Range(Range("A2"), Range("A2").End(xlDown)).Select
For Each Celda in Selection 'Ya seleccionamos arriba
Celda.Offset(0,1) = "'" & Left(Celda,4) 'Parte izquierda
Celda.Offset(0,2) = "'" & Right(Celda,10) 'Parte derecha
Next
Mid(Celda,2,5) 'Extraer parte de una cadena
Len(Celda) 'Longitud de la cadena
Proteger hojas de EXCEL
'La siguiente recorre las hojas con texto
Dim Hoja as Worksheet
Application.ScreenUpdating=false
For Each Hoja in Worksheets
Hoja.Select 'Para situarme en la hoja
Hoja.Unprotect "MiPWD"
Hoja.Protect "MiPWD"
Next
Worksheets(1).Select 'Se situa en la hoja inicial
'Worksheets("Hoja1").Select 'Puedo hacerlo por nombre
'Activar y desactivar pantalla
Application.ScreenUpdating=true
Fila=ActiveSheet.Cells(ActiveSheet.Rows.Count,"A").End(xlUp).row+1
ActiveSheet.Rows.Count 'Número final de filas en lugar de poner una constante
Do Until (false) Loop
Do Loop Until (false)
Do While (true) Loop
Do Loop While (true)
CDate(expresión) para convertir a fecha
Cells(iRen,"A")=CDate(InputBox("Introduce la fecha", "Fecha", Date))
Exit For - Salimos del for
Y(B1>10,A2=20), usar O
Target.Address="$F$35" 'en el evento Worksheet_Change viene Target
Cuando cambio valores en eventos, los desactivo para no provocar acciones en cascada Application.EnableEvents=false[true]
Range(Target.Offset(0,1),Target.Offset(0,3)).ClearContents - borra el contenido en ese rango
Worksheets("Hoja1").Cells(fila,"A")=Target
VBA es Case Sensitive cuando compara
ucase("cadena") - Convierte a mayúsculas
Hacer un catálogo y capturar transacciones en la otra hoja y traiga sus datos
Usar funciones de EXCEL desde VB
Dim cMonto as Currency 'tipo de dato de moneda
'Hay que ver el tema de atributos que se ponen en las funciones viendo la ayuda de EXCEL y son en inglés
Cells(2, "C") = Application.WorksheetFunction.Sum(Range("A:A"))
Cells(3, "C") = Application.WorksheetFunction.Average(Range("A:A"))
Cells(4, "C") = Application.WorksheetFunction.Max(Range("A:A"))
Cells(5, "C") = Application.WorksheetFunction.Min(Range("A:A"))
'Podemos seguir poniendo rango y criterio
Cells(6, "D") = Application.WorksheetFunction.CountIfs(Range("A:A"), Cells(6, "C")[, Range("B:B"), Cells(6, "D")..])
'Coincidir
Cells(7, "D") = Application.WorksheetFunction.Match(Cells(7, "C"), Range("A:A"), 0)
'BuscarV - Valor que busco, columnas donde busco, columna que tiene el valor que regreso, falso coincidencia exacta, true conque contenga
'Regresa error si no existe, así que primero validar con un contarSI
'Parámetros . Valor a buscar
'matriz donde hay que buscar
'columna que tiene el valor a regresar en la matriz
'false para coincidencia exacta
Cells(8, "D") = Application.WorksheetFunction.VLookup(Cells(7, "C"), Range("E:F"), 2, False)
'vbCrLf - Salto de línea en una cadenax
'Format(monto,"#,##0.00"), no es necesario porque le puedo dar el formato en EXCEL
'=CONTAR.SI.CONJUNTO(A:A,A1) Buscar duplicado en la misma columna
Procedimientos
IsNumeric()
Sub miproc(r1 as Range, r2 as Range, op as Integer)
exit sub
End Sub
miproc r1:=Range("A1"), op:=2, r2:=Range("B2") 'Aquí mandamos los argumentos en desorden
Recordar que los argumentos se pasan por referencia cuando son de tipo objeto. Definir ByVal para que se pase por valor
ByRef por referencia pero esí por defecto
Optional - Para definir un argumento como opcional. Los argumentos opcionales van al final de las declaraciones
sub miProc(c1 as integer, c2 as date, optional c3 as long)
ebd sub
If Celda Is Nothing Then 'Si es nulo el opcional por ejemplo
set Celda=Range("A1")
End if
iRen=Celda.Row
Fecha=Fecha+1 'Incrementa en un día la fecha
Cells(NumeroFila, Celda.Column)=Fecha
Si no se indica nada, los procedimientos que se crean son públicos
En la ventana de macros, no se muestran los procedimiento privados ni tampoco los que tienen argumentos (Optional noSeUsa as String)
Cuando se tienen procedimientos con el mismo nombre, la prioridad la tendría la de la hoja,
después el módulo
Funciones
Los argumentos también son opcionales en las funciones
Los opcionales pordemos ver si están instanciados con IS nothing
Puedo recorrer un range así:
if matRango Is nothing then exit sub
For iRen=1 to matRango.Count
matRango(iRen).Interior.Color=vbBlue
next
Private Sub btnContar_Click()
Application.ScreenUpdating = False
'Si no tiene valores, recorrer toda la columa se tarda
Range("F3") = CONTARSICOLOR(Range(Range("A2"), Range("A2").End(xlDown)), Range("E3"))
Range("F4") = CONTARSICOLOR(Range("A1:A100"), Range("E4"))
Application.ScreenUpdating = True
End Sub
Option Explicit
Function CONTARSICOLOR(celdas As Range, CeldaColor As Range) As Long
On Error GoTo funError
'La función se ve desde EXCEL
Dim Celda As Range
Dim numCeldas As Long
numCeldas = 0
For Each Celda In celdas
If Celda.Interior.Color = CeldaColor.Interior.Color Then
numCeldas = numCeldas + 1
End If
Next
CONTARSICOLOR = numCeldas
Exit Function
funError:
End Function
Poner disponibles las funciones siempre
Le damos, guardar como-examinar-complementos de EXCEL y extensión xlam
Se guardan en una ruta fija de EXCEL
Luego en opciones Opciones - Complementos - Administrar Ir
Seleccionamos el nombre conque guarde el xlam
Ya deben quedar disponibles siempre
Colecciones Sheets, Worksheets y Charts
Application.ThisWorkbook.Worksheets("Marzo").Select
Application se refiere a EXCEL
ThisWorkbook - Libro abierto
Application.ThisWorkbook - Esta parte no hace falta a menos que quiera manejar varios libros abiertos
Worksheets solo para hojas con datos, Sheets para todas, Charts solo gráficos
Sheets("Marzo").Select 'Todas las hojas
Worksheets("Marzo").Select 'Solo con datos
Charts("Ventas Enero").Select 'Solo gráficos
Para acceder a la primera hoja con datos (no a hoja1):
Worksheets(1).Select
Puedo acceder a la última hoja del documento
Sheets(Sheets.Count).Select
Puedo acceder a la última hoja con datos:
Worksheets(Worksheets.Count).Select
Cambia el nombre de la hoja seleccionada
ActiveSheet.Name="Indice"
Cambio el nombre de una hoja sin que sea la activa:
Worksheets("Enero").Name="Enero 2021"
Agregar y eliminar hojas
Cuando se añade una hoja es la activa, ahí le puedo cambiar el nombre con Activesheet
Método .Add -Agregar hojas Sheet.Add type:=[xlWorksheet/xlChart]
Worksheet.Add count=3 ' Crea tres hojas para datos
Worksheet.Add after:=Worksheets("Datos") 'Crea una hoja de trabajo después de la hoja Datos
Activesheet.name="Despues Datos" 'Le pone este nombre a la hoja nueva
Worksheet.Add after:=Sheets(Sheets.Count) 'Se crea después de la última hoja
Método .Delete -Eliminar hojas
Application.DisplayAlerts=False 'Quita los mensajes de confirmaciòn
Sheets(Sheets.Count).Delete 'Elimina la última hoja
Application.DisplayAlerts=True
Solo va a recorrer las hojas que son de datos
For each HojaTrabajo in Worksheets
Solo va a recorrer las hojas que son de gráficos
For each HojaTrabajo in Charts
If Left(HojaTrabajo.Name,6)="Grafico" then
Por lo menos hay que dejar una hoja
Mover hojas
Sub MoverHoja()
On Error GoTo subError
Worksheets("HojaAMover").Move After:=Sheets(Sheets.Count) 'La mueve a la última hoja
Worksheets("HojaAMover").Move 'Sin argumentos mueve la hoja a un documento nuevo
Worksheets("HojaAMover").Copy 'Sin argumentos genera un documento nuevo con una copia de mi hoja
'Genera una copia al inicio y le pone el nombre de índice
Worksheets("HojaAMover").Copy before:=Sheets(1) 'Si no indico este nombre le pone la copia clásica numerada
ActiveSheet.Name = "Indice"
'En formato-visibilidad, la puedo ocultar o mostrar, si no es por código
'Se debe dejar al menos una hoja visible
Worksheets("Hoja1").Visible = xlSheetHidden 'Ocultar la hoja
Worksheets("Hoja1").Visible = xlSheetVisible 'Mostrar la hoja la hoja
Worksheets("Hoja1").Visible = xlSheetVeryHidden 'Solo se puede mostrar con codigo, no desde el menu de EXCEL
End Sub
Worksheet_Change para rangos de celdas
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Celda As Range
'Este método se elige seleccionando la hoja y arriba en el litado de objetos y eventos lo busco
'Con esto valido que solo funcione cuando cambio una celda
If Target.Count = 1 Then
End If
'Como trae todo el rango, tengo que programar para todas las celdas
For Each Celda In Target
If Celda.Column = 1 And Celda.Row = 2 Then
Celda.Offset(0, 1) = Format(Celda, "dddd")
Select Case Weekday(Celda)
Case 2: Celda.Interior.Color = 11854022
Case 3 To 4: Celda.Interior.Color = 15189684
Case 5 To 6: Celda.Interior.Color = 4127996
Case Else: Celda.Interior.Color = 8696052
End Select
End If
Next
End Sub
Gráficos Incrustados
Copiar gráficos desde otras hojas
ActiveSheet.ChartObjects: Todos los gráficos incrustrados que puede tener una hoja
ActiveSheet.ChartObjects.Count
ActiveSheet.ChartObjects.Delete: Borra todos los gráficos de esa hoja
Worksheets(Target.Value).ChartObjects(1).Copy 'Copia este gráfico
ActiveSheet.Paste
Crear el gráfico
Con la grabadora de macros puedo ver las instrucciones
Sub CreaGrafico()
'Donde están los datos del gráficos por renglones y columnas
ActiveSheet.Range("A1").CurrentRegion.Select
ActiveSheet.Shapes.AddChart(xl3DColumnClustered).Select
ActiveChart.PlotBy = xlRows
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.ChartStyle = 286
ActiveChart.ChartTitle.Text = "Prueba de Gráfica"
'Cut y paste para quitarlo y ponerlo en otra hoja
'ActiveSheet.ChartObjects(1).Cut
'Celda donde se va a crear el gráfico
ActiveSheet.Range("F6").Select
'ActiveSheet.Paste
End Sub
Nombre interno de la hoja es la propiedad Name
En vez de
Worksheets("Productos").Range("A:C")
usar
LibroProductos.Range("A:C")
Target.Count=1 'Para asegurarnos que solo trae una celda
Celdas
Acceder a una celda
Excel-Documento-Hoja-Celda
Application.ThisWorkbook.Worksheets("Datos").Range("E20").Select
Selection.Value=20 'Como seleccione la celda antes, es lo que trae Selection
Evitar seleccionar celdas
Range("A1:A10")
Range("10:10") 'Seleciona el renglón 10
Cells(20,1)=10 o Cells(20,"A")=10
Range(Cells(1,"A"), Cells(1,"F")).Select
ActiveCell.Value=20
Range("A2").CurrentRegion.Select 'Selecciona todas las celdas de una matriz donde A2 es la esquina superior izquierda
'La última con datos de A1 hacía abajo, se detiene si encuentra blanco
Range("A1").End(xlDown).Select
'Encuentra la primera con dato empezando en el último renglón
Range("A1048576").End(xlUp).Select
Cells(Rows.Count, "A").End(xlUp).Select
'Última columna con datos hacía la derecha, se detiene cuando encuentra vacías
Range("A1").End(xlToRight).Select
'Se situa en la siguiente fila sin datos, 1 abajo cero a la derecha
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
Copiar (Copy) y mover(Cut) celdas
Sub CopiarCeldas()
'Copia lo que tiene en C5 a la A1 de la Hoja que este parado, ya que no indico hoja
Worksheets("Hoja1").Range("C5").Copy Range("A1")
'Copia un rango al destino empezando en la A1
Worksheets("Hoja1").Range("C5:G5").Copy Range("A1")
'O también, pero no debe haber blancos
Worksheets("Hoja1").Range(Worksheets("Hoja1").Range("C5"), Worksheets("Hoja1").Range("C5").End(xlToRight)).Copy Worksheets("Hoja9").Range("G10")
'Copia todo el renglón, pero debe coincidir el inicio
Worksheets("Hoja1").Range("A10").EntireRow.Copy Worksheets("Hoja9").Range("A16")
'O con el renglón, de nuevo, siempre empezando en la columna A
Worksheets("Hoja1").Rows(12).Copy Range("A20")
End Sub
En lugar de Copy usar Cut para mover
Eliminar celdas de forma física
Cuando lo siguiente, todas las celdas a partir de A2 se desplazan hacía arriba para llenar
Range("A1").Delete
Si quiero que en lugar de desplazar de arriba a abajo lo haga de izquierda a derecha
Range("A1").Delete xlShiftToLeft
Eliminar el renglóin
Range("A5").EntireRow.Delete
Rows("5").Delete
Saber cuantas celdas hay con datos en el renglón 14.
Me puede ayudar a eliminar filas vacias si es igual a cero
Application.WorksheetFunction.CountA(Rows(14))
quitar duplicados
Sub QuitarDuplicados()
'La matriz de A1 va a quitar duplicados, columns dice que columna es la que valida duplicados
'Si no se pone columns, entonces los duplicados son por todas las columnas
Range("A1").CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
'Remueve duplicados basados en 3 solumns
Range("A1").CurrentRegion.RemoveDuplicates Array(1, 5, 8), Header:=xlYes
End Sub
Ordenar
Range("A1").Sort key1:=Range("A1"), order1:=xlAscending, key2:=Range("A2"), order2:=xlAscending, Header:=xlYes
HASTA AQUI VIDEO 86
Pivot tables