Visual Basic for Applications en ejemplos(V)

Serie de ejemplos de código en VBA. Código para abrir Hojas, agregar Hojas, seleccionar celdas, etc.

	Sub celdaActiva()
	Dim celda As String
	celda = Application.InputBox(prompt:="Ingrese una celda de la forma Columna Fila; para activarla: ", Type:=2)
	Application.ActiveSheet.Range(celda).Select
	End Sub
	'Ejemplo. El usuario quiere activar la celda F y la fila 3
	'Escribe: F3 u f3

view raw activaSheet.vba hosted with ❤ by GitHub

	' Ir a la Hoja 2
	Sub activaData()
	Worksheets(2).Activate
	End Sub
	'Ir a la Hoja 3
	Sub activaGrafico()
	Worksheets(3).Activate
	End Sub
	'Salir de la aplicación
	Sub salir()
	r = MsgBox("Esta seguro de salir?", vbYesNo + vbInformation, "Salir")
	If r = 6 Then
	Me.Close
	End If
	End Sub
	'Regeresar a la Hoja 1
	Sub retornarPrincipal()
	Worksheets(1).Activate
	End Sub

view raw activate.vba hosted with ❤ by GitHub

	'Añadir Hoja
	Sub agregaHoja()
	Dim nombre As String
	nombre = InputBox("Ingrese el nombre de la hoja nueva: ")
	Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = nombre
	Worksheets(1).Activate
	End Sub

view raw addSheet.vba hosted with ❤ by GitHub

	Sub getResult()
	Estado = ""
	Contador = 0
	Const MAX = 9
	For Contador = 2 To MAX
	If Range("B" & Contador) > 50 And Range("C" & Contador) > 50 Then
	Estado = "Pass"
	Range("E" & Contador) = Estado
	Else
	Estado = "Fail"
	Range("E" & Contador) = Estado
	End If
	Next Contador
	End Sub
	'otra forma
	Sub multiplecond_AND()
	For N = 2 To 9
	If Cells(N, 2) > 50 And Cells(N, 3) > 50 Then
	Cells(N, 5) = "Pass"
	Else
	Cells(N, 5) = "Fail"
	End If
	Next N
	End Sub

view raw andOr.vba hosted with ❤ by GitHub

	Option Explicit
	Dim nombre As String
	Dim arreglo() As String
	Sub calc1()
	nombre = InputBox("Introduce tu nombre y apellidos", "Entrada de datos")
	'ActiveSheet.Range("E2") = nombre
	arreglo = Split(nombre, " ")
	ActiveSheet.Range("E2") = arreglo(0)
	ActiveSheet.Range("F2") = arreglo(1)
	End Sub

view raw arr.vba hosted with ❤ by GitHub

	'Abre una nueva Hoja de Excel
	Sub agregaLibros()
	Workbooks.Add
	End Sub

view raw añadirSheet.vba hosted with ❤ by GitHub

	' Visto en https://es.stackoverflow.com/questions/448934/c%c3%b3mo-puedo-cambiar-de-nombre-a-un-checkbox-en-vba
	Private Sub CheckBoxtodo_Click()
	Dim ctrl As OLEObject
	Dim check As MSForms.CheckBox
	If Me.CheckBoxtodo = True Then
	For Each ctrl In Worksheets(2).OLEObjects
	If TypeOf ctrl.Object Is MSForms.CheckBox Then
	Set check = ctrl.Object
	For i = 1 To Hoja2.Range("B3").Value
	If LCase(check.Caption) = "" & i Then
	check.Enabled = True
	ctrl.Visible = True
	check.Value = True
	End If
	Next i
	End If
	Next
	Else
	For Each ctrl In Worksheets(2).OLEObjects
	If TypeOf ctrl.Object Is MSForms.CheckBox Then
	Set check = ctrl.Object
	For i = 1 To 28
	If LCase(check.Caption) = "" & i Then
	check.Enabled = True
	ctrl.Visible = True
	check.Value = False
	End If
	Next i
	End If
	Next
	End If
	Range("H3").Select
	End Sub

view raw cambiaNombreCheck.vba hosted with ❤ by GitHub

	'Cerrar Hoja "listado.xlsx"
	Sub cerrarLibro()
	Workbook("listado.xlsx").Close
	End Sub

view raw close.vba hosted with ❤ by GitHub

	Sub numeroHojas()
	MsgBox "La cantidad de hojas es: " & Worksheets.Count
	End Sub

view raw countSheet.vba hosted with ❤ by GitHub

	Sub eliminaHoja()
	Dim numero As Integer
	numero = InputBox("Ingrese número de hoja a eliminar: ")
	Application.DisplayAlerts = False
	Worksheets(numero).Delete
	End Sub

view raw deleteSheet.vba hosted with ❤ by GitHub

	Sub primeraFila()
	Range("B5").End(xlUp).Offset(1, 0).Select
	End Sub
	Sub ultimaFila()
	Range("B5").End(xlDown).Select
	End Sub
	Sub primeraColumna()
	Range("B5").End(xlToLeft).Offset(0, 1).Select
	End Sub
	Sub ultimaColumna()
	Range("B5").End(xlToRight).Select
	End Sub
	Sub ultimaFilaCompleta()
	Range("B20", Range("B20").End(xlToRight)).Select
	End Sub

view raw filaColumna.vba hosted with ❤ by GitHub

	Sub calculaPago()
	Range("M12").FormulaLocal = "=SUMA(D12:L12)"
	Range("M12").Select
	Selection.AutoFill Destination:=Range("M12:M42")
	Range("M43").FormulaLocal = "=SUMA(M12:M42)"
	End Sub

view raw formulaLocal.vba hosted with ❤ by GitHub

	'Este se activa al abrir la Hoja
	Sub Macro1()
	'
	' Macro1 Macro
	'
	ActiveCell.FormulaR1C1 = _
	"=LOWER(MID(R[-3]C[1],SEARCH("" "",R[-3]C[1],SEARCH("" "",R[-3]C[1])+1),1+1)&MID(R[-3]C[1],RIGHT(1),RIGHT(SEARCH("" "",R[-3]C[1])-1)))&""@cibertec.com"""
	Range("B23").Select
	End Sub
	'Botón 1
	Sub determinaValores()
	Range("D10").FormulaR1C1 = "=RIGHT(RC[-2])"
	Range("D10").Select
	Selection.AutoFill Destination:=Range("D10:D20")
	Range("E10").FormulaR1C1 = "=MID(RC[-3],6,1)"
	Range("E10").Select
	Selection.AutoFill Destination:=Range("E10:E20")
	Range("F10").FormulaR1C1 = "=LOWER(MID(RC[-3], SEARCH("" "", RC[-3], SEARCH("" "", RC[-3]) + 1), 1 + 1) & MID(RC[-3], RIGHT(1), RIGHT(SEARCH("" "", RC[-3]) - 1))) & ""@editorialmacro.com"""
	Range("F10").Select
	Selection.AutoFill Destination:=Range("F10:F20")
	End Sub
	'Limpiar
	Sub limpiarCeldas()
	Range("D10:F20").ClearContents
	End Sub

view raw formulaLocalInicio.vba hosted with ❤ by GitHub

	Sub generaCalculos()
	Range("D23").Value = ""
	Range("E23").Value = ""
	Range("D9").End(xlDown).Offset(1, 0).Select
	ActiveCell.Value = "=sum(D9:D22)"
	Range("E9").End(xlDown).Offset(1, 0).Select
	ActiveCell.Value = "=sum(E9:E22)"
	End Sub

view raw functionExcel.vba hosted with ❤ by GitHub

	Sub generaHojas()
	For i = 1 To 20
	nombre = Worksheets(1).Cells(4 + i, 2).Value
	If nombre <> "" Then
	Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = nombre
	Else
	Exit Sub
	End If
	Next
	End Sub

view raw generateSheet.vba hosted with ❤ by GitHub

	Sub celdaActiva()
	Dim celda As String
	celda = Application.ActiveCell.Address
	MsgBox "La dirección de la celda activa es: " & celda & " , su Valor=" & Application.ActiveCell.Value
	End Sub

view raw getAddress.vba hosted with ❤ by GitHub

	Sub nombreLibro()
	MsgBox "El nombre del libro es: " & ThisWorkbook.Name
	End Sub

view raw getName.vba hosted with ❤ by GitHub

	Sub indice()
	MsgBox "El número de hoja es: " & ActiveSheet.Index
	End Sub

view raw getNumeroHojas.vba hosted with ❤ by GitHub

	Sub obtener()
	Estado = ""
	Contador = 0
	Const MAX = 9
	For Contador = 2 To MAX
	If Range("D" & Contador) > 120 Then
	Estado = "Good"
	Range("E" & Contador) = Estado
	ElseIf Range("D" & Contador) > 100 Then
	Estado = "Average"
	Range("E" & Contador) = Estado
	Else
	Estado = "Fail"
	Range("E" & Contador) = Estado
	End If
	Next Contador
	End Sub
	'otra forma
	Sub multipleoutcomes()
	For N = 2 To 9
	If Cells(N, 4) > 120 Then
	Cells(N, 5) = "Good"
	Else
	If Cells(N, 4) > 100 Then
	Cells(N, 5) = "Average"
	Else
	Cells(N, 5) = "Fail"
	End If
	End If
	Next N
	End Sub

view raw goodAverageFail.vba hosted with ❤ by GitHub

	Sub hojaActual()
	'MsgBox "El nombre de la hoja es: " & Worksheets(1).Name
	'MsgBox "El nombre de la hoja es: " & ActiveSheet.Name
	'MsgBox "El nombre de la hoja es: " & Hoja1.Name
	MsgBox "El nombre de la hoja es: " & Worksheets(Worksheets.Count).Name
	End Sub

view raw hojaActual.vba hosted with ❤ by GitHub

	Sub myError()
	On Error GoTo X
	N = Range("A7")
	Sheets(N).Delete
	Exit Sub
	X:
	MsgBox "Ha ocurrido un error"
	End Sub

view raw onError.vba hosted with ❤ by GitHub

	Sub abrirArchivo()
	Workbooks.Open "D:\Mis Documentos\Ejercicios Libro Excel con Macros 2013\Capitulo 1\path-workbook.xlsm"
	End Sub

view raw openSheet.vba hosted with ❤ by GitHub

	Public Sub calcular()
	Estado = ""
	Contador = 0
	Const MAX = 9
	For Contador = 2 To MAX
	If Range("D" & Contador) > 120 Then
	Estado = "Pass"
	Range("E" & Contador) = Estado
	Else
	Estado = "Fail"
	Range("E" & Contador) = Estado
	End If
	Next Contador
	End Sub
	'otra forma
	Sub myifclause()
	r = 2
	Do Until Cells(r, 1) = ""
	If Cells(r, 4) > 120 Then
	Cells(r, 5) = "PASS"
	Else
	Cells(r, 5) = "FAIL"
	End If
	r = r + 1
	Loop
	End Sub
	'Otra forma
	Sub pass_fail()
	For N = 2 To 9
	If Cells(N, 4) > 120 Then
	Cells(N, 5) = "Pass"
	Else
	Cells(N, 5) = "Fail"
	End If
	Next N
	End Sub

view raw passFail.vba hosted with ❤ by GitHub

	Sub RutaArchivo()
	MsgBox "La ruta del archivo es: " & ThisWorkbook.Path
	End Sub

view raw pathSheet.vba hosted with ❤ by GitHub

	Sub crearCopia()
	ActiveWorkbook.SaveCopyAs "D:\Mis Documentos\Ejercicios Libro Excel con Macros 2013\Capitulo 1\Copia de Seguridad.xlsm"
	'Workbooks.Open "D:\Mis Documentos\Ejercicios Libro Excel con Macros 2013\Capitulo 1\Save As - WorkBook.xlsm"
	'Workbooks("Save As - WorkBook.xlsm").Activate
	End Sub

view raw saveAsCopy.vba hosted with ❤ by GitHub

	Sub guardaLibro()
	ActiveWorkbook.SaveAs "D:\Mis Documentos\Ejercicios Libro Excel con Macros 2013\Capitulo 1\Copia de Seguridad.xlsm"
	Workbooks.Open "D:\Mis Documentos\Ejercicios Libro Excel con Macros 2013\Capitulo 1\Save As - WorkBook.xlsm"
	Workbooks("Save As - WorkBook.xlsm").Activate
	End Sub

view raw saveAsSheet.vba hosted with ❤ by GitHub

	Sub crearCopia()
	ActiveWorkbook.SaveCopyAs "D:\Mis Documentos\Ejercicios Libro Excel con Macros 2013\Capitulo 1\Copia de Seguridad.xlsm"
	End Sub

view raw saveCopyAs.vba hosted with ❤ by GitHub

	Sub guardaLibro()
	Workbooks(1).Save
	End Sub

view raw saveSheet.vba hosted with ❤ by GitHub

	Sub seleccionaCeldas()
	Range(Cells(6, 2), Cells(15, 4)).Select
	End Sub
	' B | C | D
	' 6
	' 7
	' 8
	' 9
	' 10
	' 11
	' 12
	' 13
	' 14
	' 15

view raw seleccionaCeldas.vba hosted with ❤ by GitHub

	Sub selectCaseFunc()
	Estado = ""
	Contador = 0
	Const MAX = 9
	For Contador = 2 To MAX
	Select Case Range("D" & Contador)
	Case 100 To 119
	Range("E" & Contador) = "Good"
	Case 120 To 500
	Range("E" & Contador) = "Average"
	Case Else
	Range("E" & Contador) = "Fail"
	End Select
	Next Contador
	End Sub
	'otra forma
	Sub myselectcase2()
	For N = 2 To WorksheetFunction.Count(Columns(1))
	Select Case Cells(N, 4)
	Case Is > 120
	Cells(N, 5) = "Good"
	Case Is > 100
	Cells(N, 5) = "Average"
	Case Else
	Cells(N, 5) = "Fail"
	End Select
	Next N
	End Sub
	'otra forma
	Sub myselectcase()
	For N = 2 To 9
	Select Case Cells(N, 4)
	Case Is > 120
	Cells(N, 5) = "Good"
	Case Is > 100
	Cells(N, 5) = "Average"
	Case Else
	Cells(N, 5) = "Fail"
	End Select
	Next N
	End Sub

view raw selectCase.vba hosted with ❤ by GitHub

	Sub muestraRango()
	Dim rango As String
	rango = Application.Selection.Address
	MsgBox "El rango seleccionado es: " & rango
	End Sub

view raw showRange.vba hosted with ❤ by GitHub

	Sub muestraHojas()
	Dim hojas As Worksheet
	i = 1
	For Each hojas In Sheets
	Sheets(1).Cells(6 + i, 2).Value = hojas.Name
	i = i + 1
	Next
	End Sub
	Sub limpiaCeldas()
	Sheets(1).Range("B7:B100").ClearContents
	End Sub
	Sub salir()
	r = MsgBox("Esta seguro de salir?", vbYesNo + vbInformation, "Salir")
	If r = 6 Then Me.Close
	End Sub

view raw showSheet.vba hosted with ❤ by GitHub

	Sub hojaSiguiente()
	ActiveSheet.Next.Activate
	End Sub
	Sub hojaAnterior()
	ActiveSheet.Previous.Activate
	End Sub

view raw siguienteAnterior.vba hosted with ❤ by GitHub

	'Usar funciones de Excel en VBA
	'En este caso se usa sum
	Sub generaCalculos()
	Range("D9").End(xlDown).Offset(1, 0).Select
	ActiveCell.Formula = "=sum(D9:D22)"
	Range("E9").End(xlDown).Offset(1, 0).Select
	ActiveCell.Formula = "=sum(E9:E22)"
	End Sub
	'Otra forma
	'Sub generaCalculos()
	' Range("D23").Formula = "=sum(D9:D22)"
	' Range("E23").Formula = "=sum(E9:E22)"
	'End Sub

view raw sumFunction.vba hosted with ❤ by GitHub

	Sub ubicacionCelda()
	MsgBox "La ubicación de la celda activa es: " & ActiveCell.Address
	End Sub

view raw ubicaCelda.vba hosted with ❤ by GitHub

VBA es un lenguaje de programación bastante útil para automatizar los procesos y operaciones de las hojas de Excel. Puede ahorrarnos horas y horas en la elaboración de reportes, gráficas, cálculos, etc. Es importante contar con la documentación oficial para poder entender y comprender mejor su uso.