excel code - 2013.net · excel code advanced controls subroutines simple controls (id 27) easy...
TRANSCRIPT
Excel CodeAdvanced Controls
Subroutines
EasySimple Controls (ID 27)
Private Sub CommandButton1_Click() MsgBox "Hello " & Application.UserNameEnd Sub
Private Sub OptionButton1_Click() 'blue If OptionButton1 Then Selection.Interior.Color = RGB(30, 50, 120)End Sub
Private Sub OptionButton2_Click() 'green If OptionButton2 Then ActiveCell.EntireColumn.Interior.Color = RGB(30, 120, 50)End Sub
Private Sub OptionButton3_Click() 'red If OptionButton3 Then Cells.Interior.ColorIndex = xlNone Cells.Interior.Color = RGB(120, 50, 30) End If End Sub
Private Sub ScrollBar1_Scroll() Call UpdateColorEnd Sub
Private Sub ScrollBar2_Scroll() Call UpdateColorEnd Sub
Private Sub ScrollBar3_Scroll() Call UpdateColorEnd Sub
Sub UpdateColor() ActiveSheet.Shapes("Rectangle1").Fill.ForeColor.RGB = RGB(Range("ScrollBar1"), Range("ScrollBar2"), Range("ScrollBar3"))End Sub
Page 1 of 92
Advanced Functions
Functions
EasyIs Workbook open? (ID 89)
Function IsWbOpen(wbName As String) As Boolean'' This function returns True if the given workbook <wbName> is open and False if it is not.' Dim wBook As Workbook IsWbOpen = False For Each wBook In Workbooks If StrComp(wBook.Name, wbName) = 0 Then IsWbOpen = True Exit For End If Next End Function
Page 2 of 92
EasyMultiple Substitute (ID 77)
Function BigSubstitute(CellToChange As Range, ParamArray NameNumber()) As String
Dim X As Long
If (UBound(NameNumber) ‐ LBound(NameNumber) + 1) Mod 2 Then BigSubstitute = "#MISMATCH!" Exit Function Else BigSubstitute = CellToChange.Value For X = LBound(NameNumber) To UBound(NameNumber) Step 2 BigSubstitute = Replace(BigSubstitute, NameNumber(X), _ NameNumber(X + 1), , , vbTextCompare) Next End If
End Function
Function BigSubstitute2(CellToChange As Range, NameNumber As String) As String
Dim X As LongDim Data() As String
Data = Split(NameNumber, ",") If (UBound(Data) + 1) Mod 2 Then BigSubstitute2 = "#MISMATCH!" Exit Function Else BigSubstitute2 = CellToChange.Value For X = 0 To UBound(Data) Step 2 BigSubstitute2 = Replace(BigSubstitute2, Data(X), _ Data(X + 1), , , vbTextCompare) Next End IfEnd Function
Page 3 of 92
Application
Functions
EasyGet user (ID 18)
Function User()' Returns the name of the current user User = Application.UserNameEnd Function
Subroutines
EasyLogon (ID 99)
Sub Logon()Dim UserName As String UserName = InputBox("Enter Your Name:") If UserName <> Application.UserName Then GoTo WrongName MsgBox "Welcome" & Application.UserName 'more code Exit SubWrongName:MsgBox "You're logged in with a different name?!..."End Sub
EasyShow and set default location (ID 35)
Sub showDefaultLocation()ActiveCell = Application.DefaultFilePathApplication.DefaultFilePath = "D:\Data\Courses\Excel"ActiveCell.Offset(1, 0).SelectActiveCell = Application.DefaultFilePathEnd Sub
Page 4 of 92
Arrays
Subroutines
EasySample Array (ID 10)
Sub assignArray() Dim Arr(5)
Arr(1) = "Jan" Arr(2) = "Feb" Arr(3) = "Mar" Arr(4) = "Apr" Arr(5) = "May"
MsgBox Arr(1) & "‐" & Arr(2) & "‐" & Arr(3) & "‐" & Arr(4) & "‐" & Arr(5)End Sub
Page 5 of 92
Calculations
Functions
EasyCalculate Volume (ID 17)
Function Volume(width, height, depth)
Dim resultresult = width * height * depth
Volume = resultEnd Function
Subroutines
EasyCalculate CDs with form (ID 42)
Private Sub btnEvaluate_Click() Dim Quantity As Integer Dim UnitPrice As Currency Dim TotalPrice As Currency
Quantity = CInt(txtQuantity.text)
' The price of one CD will depend on the number ordered' The more the customer orders, the lower value eachIf Quantity < 20 ThenUnitPrice = 20ElseIf Quantity < 50 ThenUnitPrice = 15ElseIf Quantity < 100 ThenUnitPrice = 12ElseIf Quantity < 500 ThenUnitPrice = 8ElseUnitPrice = 5End If
TotalPrice = Quantity * UnitPricetxtUnitPrice.text = CStr(UnitPrice)txtTotalPrice.text = CStr(TotalPrice)End Sub
Page 6 of 92
EasyCalculate mod (ID 39)
Sub CalcMod()Dim length As IntegerDim width As IntegerDim resultDim remainderlength = 22width = 5result = length \ widthremainder = length Mod widthDebug.Print result: Debug.Print remainderEnd Sub
EasyGet something with vlookup (ID 98)
Sub GetPrice() Dim PartNum As Variant Dim Price As Double PartNum = InputBox("Enter the Part Number") Sheets("Prices").Activate Price = WorksheetFunction.VLookup(PartNum, Range("PriceList"), 2, False) MsgBox PartNum & " costs " & PriceEnd Sub
EasyPMT calculation (ID 97)
Sub PmtCalc() Dim IntRate As Double Dim LoanAmt As Double Dim Periods As Integer IntRate = 0.0825 / 12 Periods = 30 * 12 LoanAmt = 150000 MsgBox WorksheetFunction.Pmt(IntRate, Periods, LoanAmt)End Sub
Page 7 of 92
Conditions ‐ Scenarios
Subroutines
EasyCheck and Test input and cell (ID 107)
Sub testInput()Dim strDate As StringstrDate = InputBox("Give a date", "Date")If strDate <> "" Then' if we fill in something If IsDate(strDate) Then 'if it's a date MsgBox "You entered a date: " & Day(strDate) & "/" & Month(strDate) & "/" & Year(strDate) Else ' if it's something else If IsNumeric(strDate) Then MsgBox "You filled in a number", vbExclamation, "Error" Else MsgBox "Wrong Input: this is no date or number", vbExclamation, "Error" End If End IfEnd If
End Sub
Sub CheckCell() Dim Msg As String Select Case IsEmpty(ActiveCell) Case True Msg = "is blank." Case Else Select Case ActiveCell.HasFormula Case True Msg = "has a formula" Case False Select Case IsNumeric(ActiveCell) Case True Msg = "has a number" Case Else Msg = "has text" End Select End Select End Select MsgBox "Cell " & ActiveCell.Address & " " & Msg ActiveWorkbook.Worksheets("loan").Range("C15") = ActiveCell.Address End Sub
Page 8 of 92
EasyControl Num Sign (ID 86)
Function NumSign(InVal) Select Case InVal Case Is < 0 NumSign = "Negative" Case 0 NumSign = "Zero" Case Is > 0 NumSign = "Positive" End SelectEnd Function
EasyDifferent scenarios (ID 52)
Sub Scaling()Dim Age As IntegerAge = 24 Select Case Age Case 0 To 17 MsgBox ("Teen") Case 18 To 55 MsgBox ("Adult") Case Else MsgBox ("Senior") End SelectEnd Sub
Sub Isit() Dim Number As Integer Number = ‐448 Select Case Number Case Is < 0 MsgBox ("The number is negative") Case Is > 0 MsgBox ("The number is positive") Case Else MsgBox ("0") End SelectEnd Sub
Page 9 of 92
EasyDifferent scenarios (ID 59)
Option Explicit
Public Sub TestBeslissing()'VariabelenDim intScore As Integer 'Variabele die de score bevatDim strTekst As String 'De tekst voor de msgbox
'Vraag aan de gebruiker een getal en stop de waarde in intScoreintScore = InputBox("Geef een getal.", "Getal?", 7)
'Check de inhoud van de variable intscoreSelect Case intScore Case Is > 15 'groter dan 15 strTekst = "groter dan 15." Case Is > 10 'groter dan 10 strTekst = "groter dan 10." Case Is > 5 'groter dan 5 strTekst = "groter dan 5." Case Else 'in alle andere gevallen strTekst = "kleiner dan of gelijk aan 5."End Select
'FeedbackMsgBox "Het getal is " & strTekst, vbOKOnly + vbInformation, "Feedback"
'Check de inhoud van intscoreIf intScore > 15 Then 'groter dan 15 strTekst = "groter dan 15."ElseIf intScore > 10 Then 'groter dan 10 strTekst = "groter dan 10."ElseIf intScore > 5 Then 'groter dan 5 strTekst = "groter dan 5."Else 'alle andere gevallen strTekst = "kleiner dan of gelijk aan 5."End If'FeedbackMsgBox "Het getal is " & strTekst, vbOKOnly + vbInformation, "Feedback"End Sub
Sub testInput()Dim strDate As StringstrDate = InputBox("Give a date", "Date")If strDate <> "" Then' if we fill in something If IsDate(strDate) Then 'if it's a date MsgBox "You entered a date: " & Day(strDate) & "/" & Month(strDate) & "/" & Year(strDate) Else ' if it's something else If IsNumeric(strDate) Then MsgBox "You filled in a number", vbExclamation, "Error" Else MsgBox "Wrong Input: this is no date or number", vbExclamation, "Error" End If End IfEnd If
End Sub
Page 10 of 92
EasyDiscount (ID 106)
Sub ShowDiscount() Dim Quantity As Integer Dim Discount As Double Quantity = InputBox("Enter Quantity:") If Quantity > 0 Then Discount = 0.1 If Quantity >= 25 Then Discount = 0.15 If Quantity >= 50 Then Discount = 0.2 If Quantity >= 75 Then Discount = 0.25 MsgBox "Discount: " & DiscountEnd Sub
Sub ShowDiscount2() Dim Quantity As Integer Dim Discount As Double Quantity = InputBox("Enter Quantity: ") If Quantity > 0 And Quantity < 25 Then Discount = 0.1 ElseIf Quantity >= 25 And Quantity < 50 Then Discount = 0.15 ElseIf Quantity >= 50 And Quantity < 75 Then Discount = 0.2 ElseIf Quantity >= 75 Then Discount = 0.25 End If MsgBox "Discount: " & DiscountEnd Sub
EasyEvaluate Age (ID 11)
Sub goVoting()
Dim Age As IntegerDim Answer As Variant
Answer = InputBox("what's your age please?", "Voting Topic")Age = CInt(Answer)
If Age >= 18 And Age < 22 Then MsgBox "You can vote" ElseIf Age >= 22 And Age < 62 Then MsgBox "You can drink and vote" ElseIf Age >= 62 Then MsgBox "You are eligible to apply for Social Security Benefit" Else MsgBox "You cannot drink or vote" End If
End Sub
Page 11 of 92
EasyGet prime numbers (ID 26)
Option Explicit
Sub NombrePrimaire()
'Dim selectieZone As RangeDim cell As Range
'selectieZone = Range("A1:J23")'selectie.Select'selectie.Interior.Color = vbYellow
Range("A1:J23").ClearFormats
For Each cell In Range("A1:J23")
If cell Mod 2 = 0 Then cell.Interior.Color = vbRed End If If cell Mod 3 = 0 Then cell.Interior.Color = vbYellow End If If cell Mod 5 = 0 Then cell.Interior.Color = vbGreen End If If cell Mod 7 = 0 Then cell.Interior.Color = vbMagenta End If If cell Mod 11 = 0 Then cell.Interior.Color = vbBlue End If If cell Mod 13 = 0 Then cell.Interior.Color = vbBlack End If Next
End Sub
Sub NombrePrimaire2()Dim i As Integeri = 0Dim cell As Range'Range("A1:J23").ClearFormatsRange("A1:J23").Clear
For Each cell In Range("A1:J23") cell.Value = i + 1 i = i + 1Next
For Each cell In Range("A1:J23")
If cell Mod 2 = 0 Then cell.Interior.Color = vbRed ElseIf cell Mod 3 = 0 Then cell.Interior.Color = vbYellow ElseIf cell Mod 5 = 0 Then
Page 12 of 92
cell.Interior.Color = vbGreen ElseIf cell Mod 7 = 0 Then cell.Interior.Color = vbMagenta ElseIf cell Mod 11 = 0 Then cell.Interior.Color = vbBlue ElseIf cell Mod 13 = 0 Then cell.Interior.Color = vbBlack End If Next
End Sub
EasyGet your grade (ID 12)
Sub getGrade()Dim LetterGrade As StringDim Grade As IntegerDim Answer As Variant
Answer = InputBox("what's your grade", "Grade ?")Grade = CInt(Answer)
Select Case Grade Case Is >= 90 LetterGrade = "A" Case Is >= 80 LetterGrade = "B" Case Is >= 70 LetterGrade = "C" Case Is >= 60 LetterGrade = "D" Case Else LetterGrade = "Sorry" End Select
MsgBox LetterGrade
End Sub
Page 13 of 92
EasyGo voting (ID 108)
Sub goVoting()
Dim Age As IntegerDim Answer As Variant
Answer = InputBox("what's your age please?", "Voting Topic")Age = CInt(Answer)
If Age >= 18 And Age < 22 Then MsgBox "You can vote" ElseIf Age >= 22 And Age < 62 Then MsgBox "You can drink and vote" ElseIf Age >= 62 Then MsgBox "You are eligible to apply for Social Security Benefit" Else MsgBox "You cannot drink or vote" End If
End Sub
EasyGreeting (ID 100)
Sub Auto_Open() MsgBox "Hi !" & vbCr & "Welcome" & vbCr & Date, vbInformationEnd Sub
Sub GreetMe4() Dim Msg As String If Time < 0.5 Then Msg = "Morning" Else If Time >= 0.5 And Time < 0.75 Then Msg = "Afternoon" Else Msg = "Evening" End If End If MsgBox "Good " & MsgEnd Sub
'for speed purpose: exit when true:Sub GreetMe5() Dim Msg As String If Time < 0.5 Then Msg = "Morning" ElseIf Time >= 0.5 And Time < 0.75 Then Msg = "Afternoon" Else Msg = "Evening" End IfMsgBox "Good " & MsgEnd Sub
Page 14 of 92
EasyLogon (ID 99)
Sub Logon()Dim UserName As String UserName = InputBox("Enter Your Name:") If UserName <> Application.UserName Then GoTo WrongName MsgBox "Welcome" & Application.UserName 'more code Exit SubWrongName:MsgBox "You're logged in with a different name?!..."End Sub
EasyType a Number (ID 74)
Sub aCase()
Dim Number As Integer
Number = InputBox("type a number")Number = CInt(Number)
Select Case Number Case 1 MsgBox ("Less than 2") Case 2 To 5 MsgBox ("Between 2 and 5") Case 6, 7, 8 MsgBox ("Between 6 and 8") Case 9 To 10 MsgBox ("Greater than 8") Case Else MsgBox ("Not between 1 and 10")End Select
Range("A1") = Number
MsgBox ("your input : " & Number & " has been written in cell A1")
End Sub
Page 15 of 92
EasyWeek day name (ID 75)
Sub aCondition()
Dim thisDay As String
thisDay = WeekdayName(Weekday(Date) ‐ 1)
If thisDay = "samedi" Or thisDay = "dimanche" Then MsgBox ("Happy Weekend!") Else MsgBox ("Happy not‐Weekend!")End If
End Sub
Page 16 of 92
Debugging
Subroutines
EasyCalculate mod (ID 39)
Sub CalcMod()Dim length As IntegerDim width As IntegerDim resultDim remainderlength = 22width = 5result = length \ widthremainder = length Mod widthDebug.Print result: Debug.Print remainderEnd Sub
Page 17 of 92
EasyDifferent scenarios (ID 59)
Option Explicit
Public Sub TestBeslissing()'VariabelenDim intScore As Integer 'Variabele die de score bevatDim strTekst As String 'De tekst voor de msgbox
'Vraag aan de gebruiker een getal en stop de waarde in intScoreintScore = InputBox("Geef een getal.", "Getal?", 7)
'Check de inhoud van de variable intscoreSelect Case intScore Case Is > 15 'groter dan 15 strTekst = "groter dan 15." Case Is > 10 'groter dan 10 strTekst = "groter dan 10." Case Is > 5 'groter dan 5 strTekst = "groter dan 5." Case Else 'in alle andere gevallen strTekst = "kleiner dan of gelijk aan 5."End Select
'FeedbackMsgBox "Het getal is " & strTekst, vbOKOnly + vbInformation, "Feedback"
'Check de inhoud van intscoreIf intScore > 15 Then 'groter dan 15 strTekst = "groter dan 15."ElseIf intScore > 10 Then 'groter dan 10 strTekst = "groter dan 10."ElseIf intScore > 5 Then 'groter dan 5 strTekst = "groter dan 5."Else 'alle andere gevallen strTekst = "kleiner dan of gelijk aan 5."End If'FeedbackMsgBox "Het getal is " & strTekst, vbOKOnly + vbInformation, "Feedback"End Sub
Sub testInput()Dim strDate As StringstrDate = InputBox("Give a date", "Date")If strDate <> "" Then' if we fill in something If IsDate(strDate) Then 'if it's a date MsgBox "You entered a date: " & Day(strDate) & "/" & Month(strDate) & "/" & Year(strDate) Else ' if it's something else If IsNumeric(strDate) Then MsgBox "You filled in a number", vbExclamation, "Error" Else MsgBox "Wrong Input: this is no date or number", vbExclamation, "Error" End If End IfEnd If
End Sub
Page 19 of 92
Events
Subroutines
EasyGreeting (ID 100)
Sub Auto_Open() MsgBox "Hi !" & vbCr & "Welcome" & vbCr & Date, vbInformationEnd Sub
Sub GreetMe4() Dim Msg As String If Time < 0.5 Then Msg = "Morning" Else If Time >= 0.5 And Time < 0.75 Then Msg = "Afternoon" Else Msg = "Evening" End If End If MsgBox "Good " & MsgEnd Sub
'for speed purpose: exit when true:Sub GreetMe5() Dim Msg As String If Time < 0.5 Then Msg = "Morning" ElseIf Time >= 0.5 And Time < 0.75 Then Msg = "Afternoon" Else Msg = "Evening" End IfMsgBox "Good " & MsgEnd Sub
EasyHighlight selected column and row (ID 28)
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Cells.Interior.ColorIndex = xlNone With ActiveCell .EntireRow.Interior.ColorIndex = 36 .EntireColumn.Interior.ColorIndex = 36 End WithEnd Sub
Page 20 of 92
EasyLocked area (ID 82)
Private Sub Worksheet_Activate()Me.ScrollArea = "A1:Z100"End Sub
Page 21 of 92
File management
Subroutines
EasyAdd a new workbook (ID 73)
Sub AddNew()
Dim NewBook As Object
Set NewBook = Workbooks.Add With NewBook .Title = "All Sales" .Subject = "Sales" .SaveAs Filename:="Allsales.xls" End WithEnd Sub
EasyShow and set default location (ID 35)
Sub showDefaultLocation()ActiveCell = Application.DefaultFilePathApplication.DefaultFilePath = "D:\Data\Courses\Excel"ActiveCell.Offset(1, 0).SelectActiveCell = Application.DefaultFilePathEnd Sub
Page 22 of 92
Forms
Subroutines
EasyCalculate CDs with form (ID 42)
Private Sub btnEvaluate_Click() Dim Quantity As Integer Dim UnitPrice As Currency Dim TotalPrice As Currency
Quantity = CInt(txtQuantity.text)
' The price of one CD will depend on the number ordered' The more the customer orders, the lower value eachIf Quantity < 20 ThenUnitPrice = 20ElseIf Quantity < 50 ThenUnitPrice = 15ElseIf Quantity < 100 ThenUnitPrice = 12ElseIf Quantity < 500 ThenUnitPrice = 8ElseUnitPrice = 5End If
TotalPrice = Quantity * UnitPricetxtUnitPrice.text = CStr(UnitPrice)txtTotalPrice.text = CStr(TotalPrice)End Sub
Page 23 of 92
Full Functionality
Functions
EasyMultiple Substitute (ID 77)
Function BigSubstitute(CellToChange As Range, ParamArray NameNumber()) As String
Dim X As Long
If (UBound(NameNumber) ‐ LBound(NameNumber) + 1) Mod 2 Then BigSubstitute = "#MISMATCH!" Exit Function Else BigSubstitute = CellToChange.Value For X = LBound(NameNumber) To UBound(NameNumber) Step 2 BigSubstitute = Replace(BigSubstitute, NameNumber(X), _ NameNumber(X + 1), , , vbTextCompare) Next End If
End Function
Function BigSubstitute2(CellToChange As Range, NameNumber As String) As String
Dim X As LongDim Data() As String
Data = Split(NameNumber, ",") If (UBound(Data) + 1) Mod 2 Then BigSubstitute2 = "#MISMATCH!" Exit Function Else BigSubstitute2 = CellToChange.Value For X = 0 To UBound(Data) Step 2 BigSubstitute2 = Replace(BigSubstitute2, Data(X), _ Data(X + 1), , , vbTextCompare) Next End IfEnd Function
Subroutines
Page 24 of 92
EasyRemove all Hyperlinks in sheet (ID 16)
Sub RemoveHyperlinks()
'Remove all hyperlinks from the active sheet ActiveSheet.Hyperlinks.Delete
End Sub
Page 25 of 92
Interactivity
Subroutines
EasyEnd of line (ID 49)
Sub messageBox() ActiveCell = MsgBox("Your logon credentials have been checked " & _ "and your application has been approved: Congratulations!" & _ vbCrLf & "Before leaving, would you like " & _ "to take our survey survey now?", _ vbYesNo Or vbQuestion, _ "Crofton Circle of Friends ‐ Membership Application")End Sub
EasyEnter a date of choice (ID 50)
Sub enterADate() Dim DateOfChoice As Date DateOfChoice = InputBox("Please enter your date of choice as mm/dd/yyyy", _ "Date of Choice", Date) MsgBox ("Date of Choice: " & DateOfChoice) ActiveCell = CDate(DateOfChoice)End Sub
Page 26 of 92
EasyGenerate a worksheet (ID 46)
Sub CreateWorksheet()Dim Answer As Variant' This macro is used to create a workbook for the' Georgetown Dry Cleaning Services' Keyboard Shortcut: Ctrl+Shift+W
Rem check whether there's something already on the sheetIf WorksheetFunction.CountA(Cells) > 0 Then Answer = MsgBox("There's content in this sheet " & ActiveSheet.Name & ", continue?", vbYesNo, "Warning")End If If Answer = vbYes Or Answer = "" Then Rem Just in case there is anything on the Rem worksheet, delete everything Range("A:K").Delete Range("1:20").Delete Rem Create the sections and headings of the worksheet Range("B2") = "Georgetown Dry Cleaning Services" Range("B2").Font.Name = "Rockwell Condensed" Range("B2").Font.Size = 24 Range("B2").Font.Bold = True Range("B2").Font.Color = RGB(200, 100, 50) Range("B3:J3").Interior.ThemeColor = xlThemeColorAccent3 Range("B5") = "Order Identification" Range("B5").Font.Name = "Cambria" Range("B5").Font.Size = 14 Range("B5").Font.Bold = True Range("B5").Font.ThemeColor = 8 Rem To draw a thick line, change the bottom Rem borders of the cells from B5 to J5 Range("B5:J5").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B5:J5").Borders(xlEdgeBottom).Weight = xlMedium Range("B5:J5").Borders(xlEdgeBottom).ThemeColor = 8 Range("B6") = "Receipt #:" Range("D6:F6").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D6:F6").Borders(xlEdgeBottom).Weight = xlHairline Range("G6") = "Order Status:" Range("I6:J6").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("I6:J6").Borders(xlEdgeBottom).Weight = xlHairline Range("B7") = "Customer Name:" Range("D7:F7").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D7:F7").Borders(xlEdgeBottom).Weight = xlHairline Range("G7") = "Customer Phone:" Range("I7:J7").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("I7:J7").Borders(xlEdgeBottom).Weight = xlHairline Rem To draw a thick line, change the bottom Rem borders of the cells from B5 to J5 Range("B8:J8").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B8:J8").Borders(xlEdgeBottom).Weight = xlThin Range("B9") = "Date Left:" Range("D9:F9").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D9:F9").Borders(xlEdgeBottom).Weight = xlHairline Range("G9") = "Time Left:" Range("I9:J9").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("I9:J9").Borders(xlEdgeBottom).Weight = xlHairline
Page 27 of 92
Range("B10") = "Date Expected:" Range("D10:F10").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D10:F10").Borders(xlEdgeBottom).Weight = xlHairline Range("G10") = "Time Expected:" Range("I10:J10").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("I10:J10").Borders(xlEdgeBottom).Weight = xlHairline Range("B11") = "Date Picked Up:" Range("D11:F11").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D11:F11").Borders(xlEdgeBottom).Weight = xlHairline Range("G11") = "Time Picked Up:" Range("I11:J11").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("I11:J11").Borders(xlEdgeBottom).Weight = xlHairline Rem To draw a thick line, change the bottom Rem borders of the cells from B5 to J5 Range("B12:J12").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B12:J12").Borders(xlEdgeBottom).Weight = xlMedium Range("B12:J12").Borders(xlEdgeBottom).ThemeColor = 8 Range("B13") = "Items to Clean" Range("B13").Font.Name = "Cambria" Range("B13").Font.Size = 14 Range("B13").Font.Bold = True Range("B13").Font.ThemeColor = 8 Range("B14") = "Item" Range("D14") = "Unit Price" Range("E14") = "Qty" Range("F14") = "Sub‐Total" Range("B14:F14").Borders(xlEdgeLeft).LineStyle = xlContinuous Range("B14:F14").Borders(xlEdgeLeft).Weight = xlThin Range("B14:F14").Borders(xlEdgeTop).LineStyle = xlContinuous Range("B14:F14").Borders(xlEdgeTop).Weight = xlThin Range("B14:F14").Borders(xlEdgeRight).LineStyle = xlContinuous Range("B14:F14").Borders(xlEdgeRight).Weight = xlThin Range("B14:F14").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B14:F14").Borders(xlEdgeBottom).Weight = xlThin Range("C14").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C14").Borders(xlEdgeRight).Weight = xlThin Range("D14").Borders(xlEdgeRight).LineStyle = xlContinuous Range("D14").Borders(xlEdgeRight).Weight = xlThin Range("E14").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E14").Borders(xlEdgeRight).Weight = xlThin Range("B15") = "Shirts" Range("B15").Borders(xlEdgeLeft).LineStyle = xlContinuous Range("B15").Borders(xlEdgeLeft).Weight = xlThin Range("H15") = "Order Summary" Range("H15").Font.Name = "Cambria" Range("H15").Font.Size = 14 Range("H15").Font.Bold = True Range("H15").Font.ThemeColor = 8 Range("B16") = "Pants" Range("B16").Borders(xlEdgeLeft).LineStyle = xlContinuous Range("B16").Borders(xlEdgeLeft).Weight = xlThin Range("B17") = "None" Range("B17").Borders(xlEdgeLeft).LineStyle = xlContinuous Range("B17").Borders(xlEdgeLeft).Weight = xlThin Range("H17") = "Cleaning Total:" Range("I17").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("I17").Borders(xlEdgeBottom).Weight = xlHairline Range("B18") = "None" Range("B18").Borders(xlEdgeLeft).LineStyle = xlContinuous Range("B18").Borders(xlEdgeLeft).Weight = xlThin Range("H18") = "Tax Rate:" Range("I18").Borders(xlEdgeBottom).LineStyle = xlContinuous
Page 28 of 92
Range("I18").Borders(xlEdgeBottom).Weight = xlHairline Range("I18") = "5.75" Range("J18") = "%" Range("B19") = "None" Range("B19").Borders(xlEdgeLeft).LineStyle = xlContinuous Range("B19").Borders(xlEdgeLeft).Weight = xlThin Range("H19") = "Tax Amount:" Range("I19").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("I19").Borders(xlEdgeBottom).Weight = xlHairline Range("B20") = "None" Range("B20").Borders(xlEdgeLeft).LineStyle = xlContinuous Range("B20").Borders(xlEdgeLeft).Weight = xlThin Range("C15").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C15").Borders(xlEdgeRight).Weight = xlThin Range("C16").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C16").Borders(xlEdgeRight).Weight = xlThin Range("C17").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C17").Borders(xlEdgeRight).Weight = xlThin Range("C18").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C18").Borders(xlEdgeRight).Weight = xlThin Range("C19").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C19").Borders(xlEdgeRight).Weight = xlThin Range("C20").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C20").Borders(xlEdgeRight).Weight = xlThin Range("B14:C14").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B14:C14").Borders(xlEdgeBottom).Weight = xlThin Range("B15:C15").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B15:C15").Borders(xlEdgeBottom).Weight = xlThin Range("D15:F15").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D15:F15").Borders(xlEdgeBottom).Weight = xlHairline Range("D15").Borders(xlEdgeRight).LineStyle = xlContinuous Range("D15").Borders(xlEdgeRight).Weight = xlHairline Range("E15").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E15").Borders(xlEdgeRight).Weight = xlHairline Range("F15").Borders(xlEdgeRight).LineStyle = xlContinuous Range("F15").Borders(xlEdgeRight).Weight = xlThin Range("B16:C16").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B16:C16").Borders(xlEdgeBottom).Weight = xlThin Range("D16:F16").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D16:F16").Borders(xlEdgeBottom).Weight = xlHairline Range("D16").Borders(xlEdgeRight).LineStyle = xlContinuous Range("D16").Borders(xlEdgeRight).Weight = xlHairline Range("E16").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E16").Borders(xlEdgeRight).Weight = xlHairline Range("F16").Borders(xlEdgeRight).LineStyle = xlContinuous Range("F16").Borders(xlEdgeRight).Weight = xlThin Range("B17:C17").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B17:C17").Borders(xlEdgeBottom).Weight = xlThin Range("D17:F17").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D17:F17").Borders(xlEdgeBottom).Weight = xlHairline Range("D17").Borders(xlEdgeRight).LineStyle = xlContinuous Range("D17").Borders(xlEdgeRight).Weight = xlHairline Range("E17").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E17").Borders(xlEdgeRight).Weight = xlHairline Range("F17").Borders(xlEdgeRight).LineStyle = xlContinuous Range("F17").Borders(xlEdgeRight).Weight = xlThin Range("B18:C18").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B18:C18").Borders(xlEdgeBottom).Weight = xlThin Range("D18:F18").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D18:F18").Borders(xlEdgeBottom).Weight = xlHairline Range("D18").Borders(xlEdgeRight).LineStyle = xlContinuous Range("D18").Borders(xlEdgeRight).Weight = xlHairline Range("E18").Borders(xlEdgeRight).LineStyle = xlContinuous
Page 29 of 92
Range("E18").Borders(xlEdgeRight).Weight = xlHairline Range("F18").Borders(xlEdgeRight).LineStyle = xlContinuous Range("F18").Borders(xlEdgeRight).Weight = xlThin Range("B19:C19").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B19:C19").Borders(xlEdgeBottom).Weight = xlThin Range("D19:F19").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D19:F19").Borders(xlEdgeBottom).Weight = xlHairline Range("D19").Borders(xlEdgeRight).LineStyle = xlContinuous Range("D19").Borders(xlEdgeRight).Weight = xlHairline Range("E19").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E19").Borders(xlEdgeRight).Weight = xlHairline Range("F19").Borders(xlEdgeRight).LineStyle = xlContinuous Range("F19").Borders(xlEdgeRight).Weight = xlThin Range("B20:F20").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B20:F20").Borders(xlEdgeBottom).Weight = xlThin Range("D20").Borders(xlEdgeRight).LineStyle = xlContinuous Range("D20").Borders(xlEdgeRight).Weight = xlHairline Range("E20").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E20").Borders(xlEdgeRight).Weight = xlHairline Range("F20").Borders(xlEdgeRight).LineStyle = xlContinuous Range("F20").Borders(xlEdgeRight).Weight = xlThin Range("H20") = "Order Total:" Range("I20").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("I20").Borders(xlEdgeBottom).Weight = xlHairline Rem Change the widths and heights of some columns and rows Rem In previous lessons, we learned all these things Range("E:E, G:G").ColumnWidth = 4 Columns("H").ColumnWidth = 14 Columns("J").ColumnWidth = 1.75 Rows("3").RowHeight = 2 Range("8:8, 12:12").RowHeight = 8 Rem Merge the cells H15, I15, H16, and I16 Range("H15:I16").MergeCells = True Rem Align the merged text to the left Range("H15:H16").VerticalAlignment = xlBottom Range("H16").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("H16:I16").Borders(xlEdgeBottom).Weight = xlMedium Range("H16:I16").Borders(xlEdgeBottom).ThemeColor = 8 Rem Hide the gridlines ActiveWindow.DisplayGridlines = False If bWorksheetExists("GTDS") Then MsgBox ("workbook exists") Else ActiveSheet.Name = "GTDS" Worksheets.Add after:=Worksheets("GTDS") Worksheets("GTDS").Select ActiveSheet.Next.Select End IfEnd IfEnd Sub
Page 30 of 92
EasyIs this your name? (ID 23)
Sub GuessName()
Dim msg As StringDim ans As String
msg = "Is your name " & Application.UserName & "?"ans = MsgBox(msg, vbYesNo)If ans = vbNo Then MsgBox "Oh, never mind."If ans = vbYes Then MsgBox "I must be clairvoyant!"End Sub
EasyLoan Dates (ID 55)
Public Sub LoanDate()
Dim LoanStartDate As DateDim DepositTime As Date 'LoanStartDate = #6/10/1998# LoanStartDate = Date 'DepositTime = TimeValue("7:14:00") DepositTime = Format(Now, "hh:mm:ss") MsgBox ("Loan Length: " & DateAdd("yyyy", 5, LoanStartDate)) MsgBox ("Time Ready: " & DateAdd("h", 8, DepositTime))
End Sub
Public Sub LoanDate2()
Dim LoanStartDate As DateDim LoanEndDate As DateDim Months As Long
LoanStartDate = DateLoanEndDate = #12/31/2020#
Months = DateDiff("m", LoanStartDate, LoanEndDate)MsgBox ("Loan Start Date: " & vbTab & LoanStartDate & vbCrLf & _"Loan End Date: " & vbTab & LoanEndDate & vbCrLf & _"Loan Length: " & vbTab & Months & " months")
End Sub
Page 31 of 92
EasySimple message boxes and input boxes (ID 57)
'‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐'Simple MessageBoxes'‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
Sub MessageBox1()' A simple message box MsgBox "Macro complete!"End Sub
Sub MessageBox_Info2()' A message box with a custom title and icon MsgBox "Macro complete!", vbInformation, "Information"End Sub
Sub MessageBox_Excl2()' A message box with a custom title and icon MsgBox "Macro complete!", vbExclamation, "Exclamation"End Sub
Sub MessageBox_Quest2()' A message box with a custom title and icon MsgBox "Macro complete!", vbQuestion, "Question"End Sub
Sub MessageBox_Crit2()' A message box with a custom title and icon MsgBox "Boom !", vbCritical, "Critical"End Sub
Sub MessageBox3()' A message box with a multi‐line message Dim strMessage As String strMessage = "This is the First Line" & vbCrLf & "Here's some more!" MsgBox strMessageEnd Sub
Sub MessageBox4()' This method compiles the message a line at a time for clarity Dim strMessage As String strMessage = "This is the First Line" strMessage = strMessage & vbCrLf & vbCrLf strMessage = strMessage & "Here's some more!" MsgBox strMessage, , "Multiple Lines"End Sub
Sub MessageBox5()' A message box with Yes/No buttons Dim strAnswer As VbMsgBoxResult strAnswer = MsgBox("Would you like to colour the cell?", vbQuestion + vbYesNo, "Decision time!") If strAnswer = vbYes Then Selection.Interior.ColorIndex = 3 End IfEnd Sub
'‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐'Simple InputBoxes'‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
Page 32 of 92
Sub InputBox1()' An input box used to place a value into a variable Dim strMessage As String strMessage = InputBox("Please enter your name:") strMessage = "Hello " & strMessage MsgBox strMessageEnd Sub
Sub InputBox2()' An input box whose value is used immediately MsgBox "Hello " & InputBox("Please enter your name:")End Sub
Sub InputBox3()' This macro's actions depend on the user's response. Dim strResponse As String strResponse = InputBox("Please type something...", "It's up to you...") If strResponse = "" Then MsgBox "You have chosen not to participate!" Exit Sub End If Range("A1").Value = strResponseEnd Sub
EasyVolume calculation (ID 7)
Sub Volume()
Dim width As VariantDim height As VariantDim depth As VariantDim result As VariantDim action As Integer
width = InputBox("Please provide width")height = InputBox("Please provide height")depth = InputBox("Please provide length")
result = width * height * depthaction = MsgBox("the result is:" & result & " m3" & vbNewLine & " ‐ Do you want result inserted into the cell?", vbYesNoCancel)If action = vbYes ThenActiveCell.FormulaR1C1 = resultElseIf action = vbCancel ThenRange("A1").Select
End If
End Sub
Page 33 of 92
EasyYes or No? (ID 72)
Sub YesNoMessageBox()Dim Answer As StringDim MyNote As String
'Place your text here MyNote = "Do you agree?"
'Display MessageBox Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "???")
If Answer = vbNo Then 'Code for No button Press MsgBox "You pressed NO!" Else 'Code for Yes button Press MsgBox "You pressed Yes!" End If
End Sub
Page 34 of 92
Loops
Subroutines
EasyChange Sign (ID 103)
Sub ChangeSign() Dim Cell As Range For Each Cell In Range("A1:E50") If IsNumeric(Cell.Value) Then Cell.Value = Cell.Value * ‐1 End IfNext CellEnd Sub
EasyCount workbooks (ID 79)
Sub CountBooks() Dim overview As String Dim wkbk As Workbook overview = "" For Each wkbk In Workbooks overview = overview & wkbk.Name & vbNewLine Next 'MsgBox Workbooks.Parent MsgBox "nr of workbooks:" & Workbooks.Count & vbCr _ & overview End Sub
Page 35 of 92
EasyDifferent ways to loop (ID 58)
Public Sub Lussen()'VariabelenDim intMinimum As Integer 'Het minimumDim intMaximum As Integer 'Het maximumDim intDeler As Integer 'de delerDim i As Integer 'variabele voor de lussen
'Initialiseren van variabelenintMinimum = Range("B1")intDeler = Range("B2")intMaximum = Range("B3")
'Is het minimum kleiner dan het maximum? Zo nee => exitIf intMinimum > intMaximum Then MsgBox "Minimum moet kleiner zijn dan maximum !", vbExclamation, "Verkeerde input" Exit SubEnd If
''''''''''''''''''''''''''VIA FOR NEXT ''''''''''''''''''''''''Start op het minimum en ga door tot het maximumFor i = intMinimum To intMaximum 'is de rest bij deling door de deler = 0 => gevonden => exit lus If i Mod intDeler = 0 Then Exit ForNext i
'Wegschrijven resultaatRange("B4") = i
''''''''''''''''''''''''''VIA DO LOOP WHILE '''''''''''''''''''''''i = intMinimumDo If i Mod intDeler = 0 Then Exit Do i = i + 1Loop While i <= intMaximumRange("C4") = i
''''''''''''''''''''''''''VIA DO LOOP UNTIL '''''''''''''''''''''''i = intMinimumDo If i Mod intDeler = 0 Then Exit Do i = i + 1Loop Until i > intMaximumRange("D4") = i
''''''''''''''''''''''''''VIA DO WHILE LOOP'''''''''''''''''''''''i = intMinimumDo While i <= intMaximum If i Mod intDeler = 0 Then Exit Do i = i + 1LoopRange("E4") = i
''''''''''''''''''''''''''VIA DO UNTIL LOOP '''''''''''''''''''''''i = intMinimumDo Until i > intMaximum If i Mod intDeler = 0 Then Exit Do i = i + 1LoopRange("F4") = iEnd Sub
Page 36 of 92
Public Sub LussenEnResultaatWegschrijven()'VariabelenDim intMinimum As Integer 'Het minimumDim intMaximum As Integer 'Het maximumDim intDeler As Integer 'de delerDim i As Integer 'variabele voor de lussenDim lngRijNummer As Long
'Initialiseren van variabelenintMinimum = Range("B1")intDeler = Range("B2")intMaximum = Range("B3")lngRijNummer = 4
'Is het minimum kleiner dan het maximum? Zo nee => exitIf intMinimum > intMaximum Then MsgBox "Minimum moet kleiner zijn dan maximum !", vbExclamation, "Verkeerde input" Exit SubEnd If
'Start op het minimum en ga door tot het maximumFor i = intMinimum To intMaximum 'is de rest bij deling door de deler = 0 => gevonden => exit lus If i Mod intDeler = 0 Then Range("B" & lngRijNummer) = i lngRijNummer = lngRijNummer + 1 End IfNext iEnd Sub
Page 37 of 92
EasyDifferent ways to loop it through (ID 53)
Sub ListItDown()
Dim text As StringDim i As IntegerDim counter As Integer
counter = CInt(InputBox("give a number below 20", "number from 1 to 20"))i = 0text = ""
Do i = i + 1 text = text & vbCrLf & "this is a line of text nr." & iLoop While i < counter
MsgBox text
End Sub
Sub ListItDown2()
Dim text As StringDim i As IntegerDim counter As Integer
counter = CInt(InputBox("give a number below 20", "number from 1 to 20"))text = ""
For i = 1 To counter Step 1 text = text & vbCrLf & "this is a line of text nr." & iNext
MsgBox text
End Sub
Sub counting()Dim Number As IntegerDo While Number < 46Number = CInt(InputBox("Enter a number"))Number = Number + 1LoopMsgBox ("Counting Stopped at: " & Number)End Sub
Sub Exercise()Dim Number As IntegerFor Number = 5 To 16MsgBox (Number)NextMsgBox ("Counting Stopped at: " & Number)End Sub
Page 38 of 92
Sub DoWhileDemo() Do While ActiveCell.Value <> Empty ActiveCell.Value = ActiveCell.Value * 2 ActiveCell.Offset(1, 0).Select LoopEnd Sub
Sub DoUntilDemo()Do Until IsEmpty(ActiveCell.Value)ActiveCell.Value = ActiveCell.Value * 2ActiveCell.Offset(1, 0).SelectLoopEnd Sub
EasyGenerate Some Dates (ID 25)
Sub GenerateDates()
Dim i As IntegerDim InvulDatum As Date
InvulDatum = Date
ActiveSheet.Columns("A").Delete
ActiveSheet.Range("A10").SelectActiveCell.Value = InvulDatum
For i = 1 To 30 ActiveCell.Offset(1, 0).Select ActiveCell.Value = InvulDatum + iNext i
ActiveSheet.Columns("A").AutoFit
End Sub
Page 39 of 92
EasyGet prime numbers (ID 26)
Option Explicit
Sub NombrePrimaire()
'Dim selectieZone As RangeDim cell As Range
'selectieZone = Range("A1:J23")'selectie.Select'selectie.Interior.Color = vbYellow
Range("A1:J23").ClearFormats
For Each cell In Range("A1:J23")
If cell Mod 2 = 0 Then cell.Interior.Color = vbRed End If If cell Mod 3 = 0 Then cell.Interior.Color = vbYellow End If If cell Mod 5 = 0 Then cell.Interior.Color = vbGreen End If If cell Mod 7 = 0 Then cell.Interior.Color = vbMagenta End If If cell Mod 11 = 0 Then cell.Interior.Color = vbBlue End If If cell Mod 13 = 0 Then cell.Interior.Color = vbBlack End If Next
End Sub
Sub NombrePrimaire2()Dim i As Integeri = 0Dim cell As Range'Range("A1:J23").ClearFormatsRange("A1:J23").Clear
For Each cell In Range("A1:J23") cell.Value = i + 1 i = i + 1Next
For Each cell In Range("A1:J23")
If cell Mod 2 = 0 Then cell.Interior.Color = vbRed ElseIf cell Mod 3 = 0 Then cell.Interior.Color = vbYellow ElseIf cell Mod 5 = 0 Then
Page 40 of 92
cell.Interior.Color = vbGreen ElseIf cell Mod 7 = 0 Then cell.Interior.Color = vbMagenta ElseIf cell Mod 11 = 0 Then cell.Interior.Color = vbBlue ElseIf cell Mod 13 = 0 Then cell.Interior.Color = vbBlack End If Next
End Sub
EasyLoop cells and columns (ID 13)
Sub loopColZ()
Dim i As Integer
For i = 1 To 10000 Cells(i, 26) = i Next i
End Sub
Sub loopColYpair()
Dim i As Integer
For i = 2 To 200 Step 2 Cells(i, 25) = i Next i
End Sub
Sub loopColW()Dim i As Integer
i = 1 Do While i <= 10 Cells(i, 23) = i i = i + 1 Loop
End Sub
Page 41 of 92
EasyMore simple loops (ID 104)
Sub CellsExample()
Dim i As IntegerDim j As Integer
For i = 1 To 5 For j = 1 To 5 Cells(i, j) = "Row " & i & " Col " & j Next j Next iEnd Sub
Sub loopColZ()
Dim i As Integer
For i = 1 To 10000 Cells(i, 26) = i Next i
End Sub
Sub loopColYpair()
Dim i As Integer
For i = 2 To 200 Step 2 Cells(i, 25) = i Next i
End Sub
Sub loopColW()Dim i As Integer
i = 1 Do While i <= 10 Cells(i, 23) = i i = i + 1 Loop
End Sub
Sub rndNo() Dim str As String Dim i As Integer Randomize For i = 1 To 5 str = str & CStr(Rnd) & vbCrLf Next i MsgBox strEnd Sub
Page 42 of 92
EasyRandomize numbers (ID 14)
Sub rndNo() Dim str As String Dim i As Integer Randomize For i = 1 To 5 str = str & CStr(Rnd) & vbCrLf Next i MsgBox strEnd Sub
EasyRemove Styles (ID 64)
Sub removeStyles()Dim sty As StyleFor Each sty In ActiveWorkbook.StylesIf sty.BuiltIn = False Thensty.DeleteEnd IfNextEnd Sub
Page 43 of 92
EasySheet number and name (ID 71)
Sub addSheetNumber() Dim n As Integer Dim Sheet As Object n = 0 For Each Sheet In Sheets() n = n + 1 Sheet.Activate Range("A1").Select ActiveCell.FormulaR1C1 = "Sheet" + Str(n) Next End Sub
Sub addSheetName() Dim n As Integer Dim Sheet As Object n = 0 For Each Sheet In Sheets() n = n + 1 Sheet.Activate Range("A2").Select ActiveCell.FormulaR1C1 = ActiveSheet.name Debug.Print ActiveSheet.name Next
End Sub
Page 44 of 92
EasyShow worksheet names (ID 2)
Sub renderWorksheetNames()
Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ActiveCell.Value = ws.Name ActiveCell.Offset(1, 0).Select Next
End Sub
Sub ShowWorkSheets()
Dim mySheet As Worksheet For Each mySheet In Worksheets MsgBox mySheet.Name Next mySheet
End Sub
Sub ShowWorkSheets2() Dim mySheet As Worksheet Dim result As String For Each mySheet In Worksheets result = result & mySheet.Name & vbCrLf Next mySheet MsgBox result Range("A10") = result
End Sub
Page 45 of 92
EasySimple Loops (ID 60)
Sub fillItUp()Dim x As IntegerDim y As Integerx = 1y = 0
For x = 1 To 30 ActiveCell.FormulaR1C1 = y + 1 'ActiveCell.Value = y + 1 ActiveCell.Offset(1, 0).Select y = y + 1Next
End Sub
Sub fillItUp2()Dim x As Integer
For x = 1 To 30 ActiveCell.FormulaR1C1 = x ActiveCell.Offset(1, 0).SelectNext
End Sub
Sub fillDatesUp()Dim x As IntegerDim y As Datex = 1y = Date
For x = 1 To 30 ActiveCell.FormulaR1C1 = y + x ‐ 1 ActiveCell.Offset(1, 0).Select Next
End Sub
Sub fillDatesUp2()
Dim x As IntegerDim y As DateDim z As Integerx = 1y = Datez = Day(WorksheetFunction.EoMonth(Date, 1))
For x = 1 To z ActiveCell.FormulaR1C1 = y + x ‐ 1 ActiveCell.Offset(1, 0).Select NextEnd Sub
Sub fillItUp3()Dim x As Integer
Page 46 of 92
x = 1
Do While x <= 30 ActiveCell.FormulaR1C1 = x ActiveCell.Offset(1, 0).Select x = x + 1Loop
End Sub
Sub fillItUp4()Dim x As Integerx = 1
Do Until x = 31 ActiveCell.FormulaR1C1 = x ActiveCell.Offset(1, 0).Select x = x + 1Loop
End Sub
Sub fillItUp5()Dim x As Integerx = 1
While x < 31 ActiveCell.FormulaR1C1 = x ActiveCell.Offset(1, 0).Select x = x + 1Wend
End Sub
Page 47 of 92
MacroRecorder
Subroutines
EasyChange Theme (ID 32)
Sub Change2FavTheme()
ActiveWorkbook.ApplyTheme ("F:\Program Files\Office\Office2010\Document Themes 14\Foundry.thmx") End Sub
Page 48 of 92
EasyDate Stamp (ID 1)
Sub mkDateNameStamp()'' mkDateNameStamp Macro'' Keyboard Shortcut: Ctrl+Shift+S' Range("A1").Select ActiveCell.FormulaR1C1 = "Danny Puype" Range("A2").Select ActiveCell.FormulaR1C1 = "Electrabel Ruien" Range("A3").Select ActiveCell.FormulaR1C1 = "=TODAY()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("A4").Select ActiveCell.FormulaR1C1 = "Tel. : 52436" Range("A1:A4").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Style = "Heading 4" Selection.Font.Bold = False Selection.Font.Italic = True Columns("A:A").EntireColumn.AutoFit Range("A6").SelectEnd Sub
EasyFormat Labels (ID 4)
Sub mkFormatLabelsGeneral()'' mkFormatLabelsGeneral Macro' Selection.Style = "Accent4" Selection.Font.Bold = True With Selection .HorizontalAlignment = xlLeft End With With Selection.Font .Name = "Garamond" .Size = 12 End WithEnd Sub
Page 49 of 92
EasyGenerate a worksheet (ID 46)
Sub CreateWorksheet()Dim Answer As Variant' This macro is used to create a workbook for the' Georgetown Dry Cleaning Services' Keyboard Shortcut: Ctrl+Shift+W
Rem check whether there's something already on the sheetIf WorksheetFunction.CountA(Cells) > 0 Then Answer = MsgBox("There's content in this sheet " & ActiveSheet.Name & ", continue?", vbYesNo, "Warning")End If If Answer = vbYes Or Answer = "" Then Rem Just in case there is anything on the Rem worksheet, delete everything Range("A:K").Delete Range("1:20").Delete Rem Create the sections and headings of the worksheet Range("B2") = "Georgetown Dry Cleaning Services" Range("B2").Font.Name = "Rockwell Condensed" Range("B2").Font.Size = 24 Range("B2").Font.Bold = True Range("B2").Font.Color = RGB(200, 100, 50) Range("B3:J3").Interior.ThemeColor = xlThemeColorAccent3 Range("B5") = "Order Identification" Range("B5").Font.Name = "Cambria" Range("B5").Font.Size = 14 Range("B5").Font.Bold = True Range("B5").Font.ThemeColor = 8 Rem To draw a thick line, change the bottom Rem borders of the cells from B5 to J5 Range("B5:J5").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B5:J5").Borders(xlEdgeBottom).Weight = xlMedium Range("B5:J5").Borders(xlEdgeBottom).ThemeColor = 8 Range("B6") = "Receipt #:" Range("D6:F6").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D6:F6").Borders(xlEdgeBottom).Weight = xlHairline Range("G6") = "Order Status:" Range("I6:J6").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("I6:J6").Borders(xlEdgeBottom).Weight = xlHairline Range("B7") = "Customer Name:" Range("D7:F7").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D7:F7").Borders(xlEdgeBottom).Weight = xlHairline Range("G7") = "Customer Phone:" Range("I7:J7").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("I7:J7").Borders(xlEdgeBottom).Weight = xlHairline Rem To draw a thick line, change the bottom Rem borders of the cells from B5 to J5 Range("B8:J8").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B8:J8").Borders(xlEdgeBottom).Weight = xlThin Range("B9") = "Date Left:" Range("D9:F9").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D9:F9").Borders(xlEdgeBottom).Weight = xlHairline Range("G9") = "Time Left:" Range("I9:J9").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("I9:J9").Borders(xlEdgeBottom).Weight = xlHairline
Page 50 of 92
Range("B10") = "Date Expected:" Range("D10:F10").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D10:F10").Borders(xlEdgeBottom).Weight = xlHairline Range("G10") = "Time Expected:" Range("I10:J10").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("I10:J10").Borders(xlEdgeBottom).Weight = xlHairline Range("B11") = "Date Picked Up:" Range("D11:F11").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D11:F11").Borders(xlEdgeBottom).Weight = xlHairline Range("G11") = "Time Picked Up:" Range("I11:J11").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("I11:J11").Borders(xlEdgeBottom).Weight = xlHairline Rem To draw a thick line, change the bottom Rem borders of the cells from B5 to J5 Range("B12:J12").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B12:J12").Borders(xlEdgeBottom).Weight = xlMedium Range("B12:J12").Borders(xlEdgeBottom).ThemeColor = 8 Range("B13") = "Items to Clean" Range("B13").Font.Name = "Cambria" Range("B13").Font.Size = 14 Range("B13").Font.Bold = True Range("B13").Font.ThemeColor = 8 Range("B14") = "Item" Range("D14") = "Unit Price" Range("E14") = "Qty" Range("F14") = "Sub‐Total" Range("B14:F14").Borders(xlEdgeLeft).LineStyle = xlContinuous Range("B14:F14").Borders(xlEdgeLeft).Weight = xlThin Range("B14:F14").Borders(xlEdgeTop).LineStyle = xlContinuous Range("B14:F14").Borders(xlEdgeTop).Weight = xlThin Range("B14:F14").Borders(xlEdgeRight).LineStyle = xlContinuous Range("B14:F14").Borders(xlEdgeRight).Weight = xlThin Range("B14:F14").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B14:F14").Borders(xlEdgeBottom).Weight = xlThin Range("C14").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C14").Borders(xlEdgeRight).Weight = xlThin Range("D14").Borders(xlEdgeRight).LineStyle = xlContinuous Range("D14").Borders(xlEdgeRight).Weight = xlThin Range("E14").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E14").Borders(xlEdgeRight).Weight = xlThin Range("B15") = "Shirts" Range("B15").Borders(xlEdgeLeft).LineStyle = xlContinuous Range("B15").Borders(xlEdgeLeft).Weight = xlThin Range("H15") = "Order Summary" Range("H15").Font.Name = "Cambria" Range("H15").Font.Size = 14 Range("H15").Font.Bold = True Range("H15").Font.ThemeColor = 8 Range("B16") = "Pants" Range("B16").Borders(xlEdgeLeft).LineStyle = xlContinuous Range("B16").Borders(xlEdgeLeft).Weight = xlThin Range("B17") = "None" Range("B17").Borders(xlEdgeLeft).LineStyle = xlContinuous Range("B17").Borders(xlEdgeLeft).Weight = xlThin Range("H17") = "Cleaning Total:" Range("I17").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("I17").Borders(xlEdgeBottom).Weight = xlHairline Range("B18") = "None" Range("B18").Borders(xlEdgeLeft).LineStyle = xlContinuous Range("B18").Borders(xlEdgeLeft).Weight = xlThin Range("H18") = "Tax Rate:" Range("I18").Borders(xlEdgeBottom).LineStyle = xlContinuous
Page 51 of 92
Range("I18").Borders(xlEdgeBottom).Weight = xlHairline Range("I18") = "5.75" Range("J18") = "%" Range("B19") = "None" Range("B19").Borders(xlEdgeLeft).LineStyle = xlContinuous Range("B19").Borders(xlEdgeLeft).Weight = xlThin Range("H19") = "Tax Amount:" Range("I19").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("I19").Borders(xlEdgeBottom).Weight = xlHairline Range("B20") = "None" Range("B20").Borders(xlEdgeLeft).LineStyle = xlContinuous Range("B20").Borders(xlEdgeLeft).Weight = xlThin Range("C15").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C15").Borders(xlEdgeRight).Weight = xlThin Range("C16").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C16").Borders(xlEdgeRight).Weight = xlThin Range("C17").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C17").Borders(xlEdgeRight).Weight = xlThin Range("C18").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C18").Borders(xlEdgeRight).Weight = xlThin Range("C19").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C19").Borders(xlEdgeRight).Weight = xlThin Range("C20").Borders(xlEdgeRight).LineStyle = xlContinuous Range("C20").Borders(xlEdgeRight).Weight = xlThin Range("B14:C14").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B14:C14").Borders(xlEdgeBottom).Weight = xlThin Range("B15:C15").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B15:C15").Borders(xlEdgeBottom).Weight = xlThin Range("D15:F15").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D15:F15").Borders(xlEdgeBottom).Weight = xlHairline Range("D15").Borders(xlEdgeRight).LineStyle = xlContinuous Range("D15").Borders(xlEdgeRight).Weight = xlHairline Range("E15").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E15").Borders(xlEdgeRight).Weight = xlHairline Range("F15").Borders(xlEdgeRight).LineStyle = xlContinuous Range("F15").Borders(xlEdgeRight).Weight = xlThin Range("B16:C16").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B16:C16").Borders(xlEdgeBottom).Weight = xlThin Range("D16:F16").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D16:F16").Borders(xlEdgeBottom).Weight = xlHairline Range("D16").Borders(xlEdgeRight).LineStyle = xlContinuous Range("D16").Borders(xlEdgeRight).Weight = xlHairline Range("E16").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E16").Borders(xlEdgeRight).Weight = xlHairline Range("F16").Borders(xlEdgeRight).LineStyle = xlContinuous Range("F16").Borders(xlEdgeRight).Weight = xlThin Range("B17:C17").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B17:C17").Borders(xlEdgeBottom).Weight = xlThin Range("D17:F17").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D17:F17").Borders(xlEdgeBottom).Weight = xlHairline Range("D17").Borders(xlEdgeRight).LineStyle = xlContinuous Range("D17").Borders(xlEdgeRight).Weight = xlHairline Range("E17").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E17").Borders(xlEdgeRight).Weight = xlHairline Range("F17").Borders(xlEdgeRight).LineStyle = xlContinuous Range("F17").Borders(xlEdgeRight).Weight = xlThin Range("B18:C18").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B18:C18").Borders(xlEdgeBottom).Weight = xlThin Range("D18:F18").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D18:F18").Borders(xlEdgeBottom).Weight = xlHairline Range("D18").Borders(xlEdgeRight).LineStyle = xlContinuous Range("D18").Borders(xlEdgeRight).Weight = xlHairline Range("E18").Borders(xlEdgeRight).LineStyle = xlContinuous
Page 52 of 92
Range("E18").Borders(xlEdgeRight).Weight = xlHairline Range("F18").Borders(xlEdgeRight).LineStyle = xlContinuous Range("F18").Borders(xlEdgeRight).Weight = xlThin Range("B19:C19").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B19:C19").Borders(xlEdgeBottom).Weight = xlThin Range("D19:F19").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("D19:F19").Borders(xlEdgeBottom).Weight = xlHairline Range("D19").Borders(xlEdgeRight).LineStyle = xlContinuous Range("D19").Borders(xlEdgeRight).Weight = xlHairline Range("E19").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E19").Borders(xlEdgeRight).Weight = xlHairline Range("F19").Borders(xlEdgeRight).LineStyle = xlContinuous Range("F19").Borders(xlEdgeRight).Weight = xlThin Range("B20:F20").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("B20:F20").Borders(xlEdgeBottom).Weight = xlThin Range("D20").Borders(xlEdgeRight).LineStyle = xlContinuous Range("D20").Borders(xlEdgeRight).Weight = xlHairline Range("E20").Borders(xlEdgeRight).LineStyle = xlContinuous Range("E20").Borders(xlEdgeRight).Weight = xlHairline Range("F20").Borders(xlEdgeRight).LineStyle = xlContinuous Range("F20").Borders(xlEdgeRight).Weight = xlThin Range("H20") = "Order Total:" Range("I20").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("I20").Borders(xlEdgeBottom).Weight = xlHairline Rem Change the widths and heights of some columns and rows Rem In previous lessons, we learned all these things Range("E:E, G:G").ColumnWidth = 4 Columns("H").ColumnWidth = 14 Columns("J").ColumnWidth = 1.75 Rows("3").RowHeight = 2 Range("8:8, 12:12").RowHeight = 8 Rem Merge the cells H15, I15, H16, and I16 Range("H15:I16").MergeCells = True Rem Align the merged text to the left Range("H15:H16").VerticalAlignment = xlBottom Range("H16").Borders(xlEdgeBottom).LineStyle = xlContinuous Range("H16:I16").Borders(xlEdgeBottom).Weight = xlMedium Range("H16:I16").Borders(xlEdgeBottom).ThemeColor = 8 Rem Hide the gridlines ActiveWindow.DisplayGridlines = False If bWorksheetExists("GTDS") Then MsgBox ("workbook exists") Else ActiveSheet.Name = "GTDS" Worksheets.Add after:=Worksheets("GTDS") Worksheets("GTDS").Select ActiveSheet.Next.Select End IfEnd IfEnd Sub
Page 53 of 92
EasyPage Breaks and Gridlines off (ID 33)
Sub PageBreaksOff()
ActiveSheet.DisplayPageBreaks = FalseEnd Sub
Sub windowPagebreaks()If ActiveSheet.DisplayPageBreaks = True ThenActiveSheet.DisplayPageBreaks = FalseElseActiveSheet.DisplayPageBreaks = TrueEnd IfEnd Sub
Sub windowGridlines()If ActiveWindow.DisplayGridlines = True ThenActiveWindow.DisplayGridlines = FalseElseActiveWindow.DisplayGridlines = TrueEnd IfEnd Sub
Page 54 of 92
Ranges
Subroutines
EasyChange Sign (ID 103)
Sub ChangeSign() Dim Cell As Range For Each Cell In Range("A1:E50") If IsNumeric(Cell.Value) Then Cell.Value = Cell.Value * ‐1 End IfNext CellEnd Sub
EasyDifferent Range properties (ID 47)
Sub MoreEntrees()
Dim Number As Double Range("B2").Formula = 24.5 * 42.5 Range("B3").FormulaR1C1Local = "Danny" Range("B4").Formula = "=today()" Range("A1").Offset(1, 0).Select Number = 7942.225 * 202.46 ActiveCell = MsgBox(Int(Number), vbOKOnly, "Exercise") ActiveCell.Offset(1, 0).Select Number = 20502.48 ActiveCell = Format(Number, "currency") Beep
End Sub
Page 55 of 92
EasyEnter values in a range (ID 8)
Sub EnterSomeValues()
Worksheets("EnterSomeValue").Range("A1:A5") = "AB" Worksheets("EnterSomeValue").Range("B1", "B5") = "AB" Worksheets("EnterSomeValue").Range("C1, C3, C5") = "AAA"
End Sub
Page 56 of 92
EasyGet prime numbers (ID 26)
Option Explicit
Sub NombrePrimaire()
'Dim selectieZone As RangeDim cell As Range
'selectieZone = Range("A1:J23")'selectie.Select'selectie.Interior.Color = vbYellow
Range("A1:J23").ClearFormats
For Each cell In Range("A1:J23")
If cell Mod 2 = 0 Then cell.Interior.Color = vbRed End If If cell Mod 3 = 0 Then cell.Interior.Color = vbYellow End If If cell Mod 5 = 0 Then cell.Interior.Color = vbGreen End If If cell Mod 7 = 0 Then cell.Interior.Color = vbMagenta End If If cell Mod 11 = 0 Then cell.Interior.Color = vbBlue End If If cell Mod 13 = 0 Then cell.Interior.Color = vbBlack End If Next
End Sub
Sub NombrePrimaire2()Dim i As Integeri = 0Dim cell As Range'Range("A1:J23").ClearFormatsRange("A1:J23").Clear
For Each cell In Range("A1:J23") cell.Value = i + 1 i = i + 1Next
For Each cell In Range("A1:J23")
If cell Mod 2 = 0 Then cell.Interior.Color = vbRed ElseIf cell Mod 3 = 0 Then cell.Interior.Color = vbYellow ElseIf cell Mod 5 = 0 Then
Page 57 of 92
cell.Interior.Color = vbGreen ElseIf cell Mod 7 = 0 Then cell.Interior.Color = vbMagenta ElseIf cell Mod 11 = 0 Then cell.Interior.Color = vbBlue ElseIf cell Mod 13 = 0 Then cell.Interior.Color = vbBlack End If Next
End Sub
EasySimple Copy (ID 95)
Sub CopyOne() Worksheets(1).Activate Range("A1").Copy Range("A10")End Sub
Page 58 of 92
Rows and Columns
Subroutines
EasyGenerate Some Dates (ID 25)
Sub GenerateDates()
Dim i As IntegerDim InvulDatum As Date
InvulDatum = Date
ActiveSheet.Columns("A").Delete
ActiveSheet.Range("A10").SelectActiveCell.Value = InvulDatum
For i = 1 To 30 ActiveCell.Offset(1, 0).Select ActiveCell.Value = InvulDatum + iNext i
ActiveSheet.Columns("A").AutoFit
End Sub
EasyHighlight selected column and row (ID 28)
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Cells.Interior.ColorIndex = xlNone With ActiveCell .EntireRow.Interior.ColorIndex = 36 .EntireColumn.Interior.ColorIndex = 36 End WithEnd Sub
Page 59 of 92
EasyLoop cells and columns (ID 13)
Sub loopColZ()
Dim i As Integer
For i = 1 To 10000 Cells(i, 26) = i Next i
End Sub
Sub loopColYpair()
Dim i As Integer
For i = 2 To 200 Step 2 Cells(i, 25) = i Next i
End Sub
Sub loopColW()Dim i As Integer
i = 1 Do While i <= 10 Cells(i, 23) = i i = i + 1 Loop
End Sub
Page 60 of 92
Simple Functions
Functions
EasyCalculate Volume (ID 17)
Function Volume(width, height, depth)
Dim resultresult = width * height * depth
Volume = resultEnd Function
EasyFunction to trigger of full name (ID 38)
Private Function GetFullName$(ByVal First As String, ByVal Last As String)Dim Fname As StringFname = First & LastGetFullName$ = FnameEnd Function
Sub Exercise()Dim FirstName As String, LastName As StringDim FullName As StringFirstName = "Raymond "LastName = "Kouma"FullName = GetFullName(FirstName, LastName)ActiveCell.FormulaR1C1 = FullNameEnd Sub
EasyGet the full name (ID 44)
Function GetFullName$(FirstName, LastName)'Dim FirstName$, LastName$'FirstName = "Raymond"'LastName = "Kouma" GetFullName$ = LastName & ", " & FirstNameEnd Function
Page 61 of 92
EasyGet user (ID 18)
Function User()' Returns the name of the current user User = Application.UserNameEnd Function
EasySome simple Functions (ID 85)
Function CubeRoot(number) CubeRoot = number ^ (1 / 3)End Function
Public Function SumItUp(ByRef myRange As Range) SumItUp = WorksheetFunction.Sum(myRange)End Function
Public Function cnvTh(NumberKWH) As Double Dim result result = NumberKWH / 29.30711 cnvTh = Format(result, "0,000.0000")End Function
Public Function cnvKWH(NumberTh) As Double Dim result result = NumberTh * 29.30711 cnvKWH = Format(result, "0,000.0000")End Function
Public Function CelToFahr(valueCelsius As Double) CelToFahr = 32 + (9 / 5) * valueCelsiusEnd Function
Public Function FahrToCel(valueFahr As Double) FahrToCel = (valueFahr ‐ 32) * 5 / 9End Function
Subroutines
Page 62 of 92
EasyControl Num Sign (ID 86)
Function NumSign(InVal) Select Case InVal Case Is < 0 NumSign = "Negative" Case 0 NumSign = "Zero" Case Is > 0 NumSign = "Positive" End SelectEnd Function
Page 63 of 92
Simple Sample
Functions
EasyGet user (ID 18)
Function User()' Returns the name of the current user User = Application.UserNameEnd Function
Subroutines
EasyChange Theme (ID 32)
Sub Change2FavTheme()
ActiveWorkbook.ApplyTheme ("F:\Program Files\Office\Office2010\Document Themes 14\Foundry.thmx") End Sub
Page 64 of 92
EasyDate Stamp (ID 1)
Sub mkDateNameStamp()'' mkDateNameStamp Macro'' Keyboard Shortcut: Ctrl+Shift+S' Range("A1").Select ActiveCell.FormulaR1C1 = "Danny Puype" Range("A2").Select ActiveCell.FormulaR1C1 = "Electrabel Ruien" Range("A3").Select ActiveCell.FormulaR1C1 = "=TODAY()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("A4").Select ActiveCell.FormulaR1C1 = "Tel. : 52436" Range("A1:A4").Select With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Style = "Heading 4" Selection.Font.Bold = False Selection.Font.Italic = True Columns("A:A").EntireColumn.AutoFit Range("A6").SelectEnd Sub
EasyEnd of line (ID 49)
Sub messageBox() ActiveCell = MsgBox("Your logon credentials have been checked " & _ "and your application has been approved: Congratulations!" & _ vbCrLf & "Before leaving, would you like " & _ "to take our survey survey now?", _ vbYesNo Or vbQuestion, _ "Crofton Circle of Friends ‐ Membership Application")End Sub
Page 65 of 92
EasyEnter values in a range (ID 8)
Sub EnterSomeValues()
Worksheets("EnterSomeValue").Range("A1:A5") = "AB" Worksheets("EnterSomeValue").Range("B1", "B5") = "AB" Worksheets("EnterSomeValue").Range("C1, C3, C5") = "AAA"
End Sub
EasyFormat Labels (ID 4)
Sub mkFormatLabelsGeneral()'' mkFormatLabelsGeneral Macro' Selection.Style = "Accent4" Selection.Font.Bold = True With Selection .HorizontalAlignment = xlLeft End With With Selection.Font .Name = "Garamond" .Size = 12 End WithEnd Sub
EasyGenerate Some Dates (ID 25)
Sub GenerateDates()
Dim i As IntegerDim InvulDatum As Date
InvulDatum = Date
ActiveSheet.Columns("A").Delete
ActiveSheet.Range("A10").SelectActiveCell.Value = InvulDatum
For i = 1 To 30 ActiveCell.Offset(1, 0).Select ActiveCell.Value = InvulDatum + iNext i
ActiveSheet.Columns("A").AutoFit
End Sub
Page 66 of 92
EasyGet something with vlookup (ID 98)
Sub GetPrice() Dim PartNum As Variant Dim Price As Double PartNum = InputBox("Enter the Part Number") Sheets("Prices").Activate Price = WorksheetFunction.VLookup(PartNum, Range("PriceList"), 2, False) MsgBox PartNum & " costs " & PriceEnd Sub
EasyGet your grade (ID 12)
Sub getGrade()Dim LetterGrade As StringDim Grade As IntegerDim Answer As Variant
Answer = InputBox("what's your grade", "Grade ?")Grade = CInt(Answer)
Select Case Grade Case Is >= 90 LetterGrade = "A" Case Is >= 80 LetterGrade = "B" Case Is >= 70 LetterGrade = "C" Case Is >= 60 LetterGrade = "D" Case Else LetterGrade = "Sorry" End Select
MsgBox LetterGrade
End Sub
Page 67 of 92
EasyGo voting (ID 108)
Sub goVoting()
Dim Age As IntegerDim Answer As Variant
Answer = InputBox("what's your age please?", "Voting Topic")Age = CInt(Answer)
If Age >= 18 And Age < 22 Then MsgBox "You can vote" ElseIf Age >= 22 And Age < 62 Then MsgBox "You can drink and vote" ElseIf Age >= 62 Then MsgBox "You are eligible to apply for Social Security Benefit" Else MsgBox "You cannot drink or vote" End If
End Sub
EasyIs this your name? (ID 23)
Sub GuessName()
Dim msg As StringDim ans As String
msg = "Is your name " & Application.UserName & "?"ans = MsgBox(msg, vbYesNo)If ans = vbNo Then MsgBox "Oh, never mind."If ans = vbYes Then MsgBox "I must be clairvoyant!"End Sub
Page 68 of 92
EasyLoop cells and columns (ID 13)
Sub loopColZ()
Dim i As Integer
For i = 1 To 10000 Cells(i, 26) = i Next i
End Sub
Sub loopColYpair()
Dim i As Integer
For i = 2 To 200 Step 2 Cells(i, 25) = i Next i
End Sub
Sub loopColW()Dim i As Integer
i = 1 Do While i <= 10 Cells(i, 23) = i i = i + 1 Loop
End Sub
EasyNr of executions in a not empty variable (ID 80)
Sub staticCounter()Static counter As IntegerDim Msg As String counter = counter + 1 Msg = "Number of executions: " & counter MsgBox MsgEnd Sub
Page 69 of 92
EasyPage Breaks and Gridlines off (ID 33)
Sub PageBreaksOff()
ActiveSheet.DisplayPageBreaks = FalseEnd Sub
Sub windowPagebreaks()If ActiveSheet.DisplayPageBreaks = True ThenActiveSheet.DisplayPageBreaks = FalseElseActiveSheet.DisplayPageBreaks = TrueEnd IfEnd Sub
Sub windowGridlines()If ActiveWindow.DisplayGridlines = True ThenActiveWindow.DisplayGridlines = FalseElseActiveWindow.DisplayGridlines = TrueEnd IfEnd Sub
EasyPMT calculation (ID 97)
Sub PmtCalc() Dim IntRate As Double Dim LoanAmt As Double Dim Periods As Integer IntRate = 0.0825 / 12 Periods = 30 * 12 LoanAmt = 150000 MsgBox WorksheetFunction.Pmt(IntRate, Periods, LoanAmt)End Sub
EasyRandomize numbers (ID 14)
Sub rndNo() Dim str As String Dim i As Integer Randomize For i = 1 To 5 str = str & CStr(Rnd) & vbCrLf Next i MsgBox strEnd Sub
Page 70 of 92
EasySheet number and name (ID 71)
Sub addSheetNumber() Dim n As Integer Dim Sheet As Object n = 0 For Each Sheet In Sheets() n = n + 1 Sheet.Activate Range("A1").Select ActiveCell.FormulaR1C1 = "Sheet" + Str(n) Next End Sub
Sub addSheetName() Dim n As Integer Dim Sheet As Object n = 0 For Each Sheet In Sheets() n = n + 1 Sheet.Activate Range("A2").Select ActiveCell.FormulaR1C1 = ActiveSheet.name Debug.Print ActiveSheet.name Next
End Sub
Page 71 of 92
EasyShow worksheet names (ID 2)
Sub renderWorksheetNames()
Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ActiveCell.Value = ws.Name ActiveCell.Offset(1, 0).Select Next
End Sub
Sub ShowWorkSheets()
Dim mySheet As Worksheet For Each mySheet In Worksheets MsgBox mySheet.Name Next mySheet
End Sub
Sub ShowWorkSheets2() Dim mySheet As Worksheet Dim result As String For Each mySheet In Worksheets result = result & mySheet.Name & vbCrLf Next mySheet MsgBox result Range("A10") = result
End Sub
EasySimple Copy (ID 95)
Sub CopyOne() Worksheets(1).Activate Range("A1").Copy Range("A10")End Sub
Page 72 of 92
EasySimple Loops (ID 60)
Sub fillItUp()Dim x As IntegerDim y As Integerx = 1y = 0
For x = 1 To 30 ActiveCell.FormulaR1C1 = y + 1 'ActiveCell.Value = y + 1 ActiveCell.Offset(1, 0).Select y = y + 1Next
End Sub
Sub fillItUp2()Dim x As Integer
For x = 1 To 30 ActiveCell.FormulaR1C1 = x ActiveCell.Offset(1, 0).SelectNext
End Sub
Sub fillDatesUp()Dim x As IntegerDim y As Datex = 1y = Date
For x = 1 To 30 ActiveCell.FormulaR1C1 = y + x ‐ 1 ActiveCell.Offset(1, 0).Select Next
End Sub
Sub fillDatesUp2()
Dim x As IntegerDim y As DateDim z As Integerx = 1y = Datez = Day(WorksheetFunction.EoMonth(Date, 1))
For x = 1 To z ActiveCell.FormulaR1C1 = y + x ‐ 1 ActiveCell.Offset(1, 0).Select NextEnd Sub
Sub fillItUp3()Dim x As Integer
Page 73 of 92
x = 1
Do While x <= 30 ActiveCell.FormulaR1C1 = x ActiveCell.Offset(1, 0).Select x = x + 1Loop
End Sub
Sub fillItUp4()Dim x As Integerx = 1
Do Until x = 31 ActiveCell.FormulaR1C1 = x ActiveCell.Offset(1, 0).Select x = x + 1Loop
End Sub
Sub fillItUp5()Dim x As Integerx = 1
While x < 31 ActiveCell.FormulaR1C1 = x ActiveCell.Offset(1, 0).Select x = x + 1Wend
End Sub
EasySimple text manipulations: Get the Ascii value and Reverse text (ID 4
Sub ChrValues() Dim Character As String Dim Number As Long Number = InputBox("Please type a number", "Input of number2ChrValue") Character = ChrW(Number) ActiveCell = "The ASCII character of " & Number & " is " & CharacterEnd Sub
Sub ReverseIt() Dim StrValue As String Dim StrRev As String StrValue = "République d'Afrique du Sud" StrRev = StrReverse(StrValue) ActiveCell = StrValue & vbCrLf & StrRevEnd Sub
Page 74 of 92
EasyType a Number (ID 74)
Sub aCase()
Dim Number As Integer
Number = InputBox("type a number")Number = CInt(Number)
Select Case Number Case 1 MsgBox ("Less than 2") Case 2 To 5 MsgBox ("Between 2 and 5") Case 6, 7, 8 MsgBox ("Between 6 and 8") Case 9 To 10 MsgBox ("Greater than 8") Case Else MsgBox ("Not between 1 and 10")End Select
Range("A1") = Number
MsgBox ("your input : " & Number & " has been written in cell A1")
End Sub
EasyVolume calculation (ID 7)
Sub Volume()
Dim width As VariantDim height As VariantDim depth As VariantDim result As VariantDim action As Integer
width = InputBox("Please provide width")height = InputBox("Please provide height")depth = InputBox("Please provide length")
result = width * height * depthaction = MsgBox("the result is:" & result & " m3" & vbNewLine & " ‐ Do you want result inserted into the cell?", vbYesNoCancel)If action = vbYes ThenActiveCell.FormulaR1C1 = resultElseIf action = vbCancel ThenRange("A1").Select
End If
End Sub
Page 75 of 92
EasyYes or No? (ID 72)
Sub YesNoMessageBox()Dim Answer As StringDim MyNote As String
'Place your text here MyNote = "Do you agree?"
'Display MessageBox Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "???")
If Answer = vbNo Then 'Code for No button Press MsgBox "You pressed NO!" Else 'Code for Yes button Press MsgBox "You pressed Yes!" End If
End Sub
Page 76 of 92
Variables
Functions
EasyCalculate Volume (ID 17)
Function Volume(width, height, depth)
Dim resultresult = width * height * depth
Volume = resultEnd Function
EasyGet the full name (ID 44)
Function GetFullName$(FirstName, LastName)'Dim FirstName$, LastName$'FirstName = "Raymond"'LastName = "Kouma" GetFullName$ = LastName & ", " & FirstNameEnd Function
Subroutines
EasyIs this your name? (ID 23)
Sub GuessName()
Dim msg As StringDim ans As String
msg = "Is your name " & Application.UserName & "?"ans = MsgBox(msg, vbYesNo)If ans = vbNo Then MsgBox "Oh, never mind."If ans = vbYes Then MsgBox "I must be clairvoyant!"End Sub
Page 77 of 92
EasyNr of executions in a not empty variable (ID 80)
Sub staticCounter()Static counter As IntegerDim Msg As String counter = counter + 1 Msg = "Number of executions: " & counter MsgBox MsgEnd Sub
EasyPMT calculation (ID 97)
Sub PmtCalc() Dim IntRate As Double Dim LoanAmt As Double Dim Periods As Integer IntRate = 0.0825 / 12 Periods = 30 * 12 LoanAmt = 150000 MsgBox WorksheetFunction.Pmt(IntRate, Periods, LoanAmt)End Sub
Page 78 of 92
VBA functions
Subroutines
EasyCalculate CDs with form (ID 42)
Private Sub btnEvaluate_Click() Dim Quantity As Integer Dim UnitPrice As Currency Dim TotalPrice As Currency
Quantity = CInt(txtQuantity.text)
' The price of one CD will depend on the number ordered' The more the customer orders, the lower value eachIf Quantity < 20 ThenUnitPrice = 20ElseIf Quantity < 50 ThenUnitPrice = 15ElseIf Quantity < 100 ThenUnitPrice = 12ElseIf Quantity < 500 ThenUnitPrice = 8ElseUnitPrice = 5End If
TotalPrice = Quantity * UnitPricetxtUnitPrice.text = CStr(UnitPrice)txtTotalPrice.text = CStr(TotalPrice)End Sub
EasyChange Sign (ID 103)
Sub ChangeSign() Dim Cell As Range For Each Cell In Range("A1:E50") If IsNumeric(Cell.Value) Then Cell.Value = Cell.Value * ‐1 End IfNext CellEnd Sub
Page 79 of 92
EasyCheck and Test input and cell (ID 107)
Sub testInput()Dim strDate As StringstrDate = InputBox("Give a date", "Date")If strDate <> "" Then' if we fill in something If IsDate(strDate) Then 'if it's a date MsgBox "You entered a date: " & Day(strDate) & "/" & Month(strDate) & "/" & Year(strDate) Else ' if it's something else If IsNumeric(strDate) Then MsgBox "You filled in a number", vbExclamation, "Error" Else MsgBox "Wrong Input: this is no date or number", vbExclamation, "Error" End If End IfEnd If
End Sub
Sub CheckCell() Dim Msg As String Select Case IsEmpty(ActiveCell) Case True Msg = "is blank." Case Else Select Case ActiveCell.HasFormula Case True Msg = "has a formula" Case False Select Case IsNumeric(ActiveCell) Case True Msg = "has a number" Case Else Msg = "has text" End Select End Select End Select MsgBox "Cell " & ActiveCell.Address & " " & Msg ActiveWorkbook.Worksheets("loan").Range("C15") = ActiveCell.Address End Sub
Page 80 of 92
EasyDifferent Range properties (ID 47)
Sub MoreEntrees()
Dim Number As Double Range("B2").Formula = 24.5 * 42.5 Range("B3").FormulaR1C1Local = "Danny" Range("B4").Formula = "=today()" Range("A1").Offset(1, 0).Select Number = 7942.225 * 202.46 ActiveCell = MsgBox(Int(Number), vbOKOnly, "Exercise") ActiveCell.Offset(1, 0).Select Number = 20502.48 ActiveCell = Format(Number, "currency") Beep
End Sub
Page 81 of 92
EasyDifferent ways to loop it through (ID 53)
Sub ListItDown()
Dim text As StringDim i As IntegerDim counter As Integer
counter = CInt(InputBox("give a number below 20", "number from 1 to 20"))i = 0text = ""
Do i = i + 1 text = text & vbCrLf & "this is a line of text nr." & iLoop While i < counter
MsgBox text
End Sub
Sub ListItDown2()
Dim text As StringDim i As IntegerDim counter As Integer
counter = CInt(InputBox("give a number below 20", "number from 1 to 20"))text = ""
For i = 1 To counter Step 1 text = text & vbCrLf & "this is a line of text nr." & iNext
MsgBox text
End Sub
Sub counting()Dim Number As IntegerDo While Number < 46Number = CInt(InputBox("Enter a number"))Number = Number + 1LoopMsgBox ("Counting Stopped at: " & Number)End Sub
Sub Exercise()Dim Number As IntegerFor Number = 5 To 16MsgBox (Number)NextMsgBox ("Counting Stopped at: " & Number)End Sub
Page 82 of 92
Sub DoWhileDemo() Do While ActiveCell.Value <> Empty ActiveCell.Value = ActiveCell.Value * 2 ActiveCell.Offset(1, 0).Select LoopEnd Sub
Sub DoUntilDemo()Do Until IsEmpty(ActiveCell.Value)ActiveCell.Value = ActiveCell.Value * 2ActiveCell.Offset(1, 0).SelectLoopEnd Sub
EasyEnter a date of choice (ID 50)
Sub enterADate() Dim DateOfChoice As Date DateOfChoice = InputBox("Please enter your date of choice as mm/dd/yyyy", _ "Date of Choice", Date) MsgBox ("Date of Choice: " & DateOfChoice) ActiveCell = CDate(DateOfChoice)End Sub
Page 83 of 92
EasyLoan Dates (ID 55)
Public Sub LoanDate()
Dim LoanStartDate As DateDim DepositTime As Date 'LoanStartDate = #6/10/1998# LoanStartDate = Date 'DepositTime = TimeValue("7:14:00") DepositTime = Format(Now, "hh:mm:ss") MsgBox ("Loan Length: " & DateAdd("yyyy", 5, LoanStartDate)) MsgBox ("Time Ready: " & DateAdd("h", 8, DepositTime))
End Sub
Public Sub LoanDate2()
Dim LoanStartDate As DateDim LoanEndDate As DateDim Months As Long
LoanStartDate = DateLoanEndDate = #12/31/2020#
Months = DateDiff("m", LoanStartDate, LoanEndDate)MsgBox ("Loan Start Date: " & vbTab & LoanStartDate & vbCrLf & _"Loan End Date: " & vbTab & LoanEndDate & vbCrLf & _"Loan Length: " & vbTab & Months & " months")
End Sub
EasySimple text manipulations: Get the Ascii value and Reverse text (ID 4
Sub ChrValues() Dim Character As String Dim Number As Long Number = InputBox("Please type a number", "Input of number2ChrValue") Character = ChrW(Number) ActiveCell = "The ASCII character of " & Number & " is " & CharacterEnd Sub
Sub ReverseIt() Dim StrValue As String Dim StrRev As String StrValue = "République d'Afrique du Sud" StrRev = StrReverse(StrValue) ActiveCell = StrValue & vbCrLf & StrRevEnd Sub
Page 84 of 92
Worksheets and Workbooks
Functions
EasyIs Workbook open? (ID 89)
Function IsWbOpen(wbName As String) As Boolean'' This function returns True if the given workbook <wbName> is open and False if it is not.' Dim wBook As Workbook IsWbOpen = False For Each wBook In Workbooks If StrComp(wBook.Name, wbName) = 0 Then IsWbOpen = True Exit For End If Next End Function
Subroutines
EasyAdd a new workbook (ID 73)
Sub AddNew()
Dim NewBook As Object
Set NewBook = Workbooks.Add With NewBook .Title = "All Sales" .Subject = "Sales" .SaveAs Filename:="Allsales.xls" End WithEnd Sub
Page 85 of 92
EasyChange Theme (ID 32)
Sub Change2FavTheme()
ActiveWorkbook.ApplyTheme ("F:\Program Files\Office\Office2010\Document Themes 14\Foundry.thmx") End Sub
EasyCount workbooks (ID 79)
Sub CountBooks() Dim overview As String Dim wkbk As Workbook overview = "" For Each wkbk In Workbooks overview = overview & wkbk.Name & vbNewLine Next 'MsgBox Workbooks.Parent MsgBox "nr of workbooks:" & Workbooks.Count & vbCr _ & overview End Sub
EasyHighlight selected column and row (ID 28)
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Cells.Interior.ColorIndex = xlNone With ActiveCell .EntireRow.Interior.ColorIndex = 36 .EntireColumn.Interior.ColorIndex = 36 End WithEnd Sub
Page 86 of 92
EasyLocked area (ID 82)
Private Sub Worksheet_Activate()Me.ScrollArea = "A1:Z100"End Sub
EasyPage Breaks and Gridlines off (ID 33)
Sub PageBreaksOff()
ActiveSheet.DisplayPageBreaks = FalseEnd Sub
Sub windowPagebreaks()If ActiveSheet.DisplayPageBreaks = True ThenActiveSheet.DisplayPageBreaks = FalseElseActiveSheet.DisplayPageBreaks = TrueEnd IfEnd Sub
Sub windowGridlines()If ActiveWindow.DisplayGridlines = True ThenActiveWindow.DisplayGridlines = FalseElseActiveWindow.DisplayGridlines = TrueEnd IfEnd Sub
EasyPage Setup (ID 96)
Sub PageSetupSettings()
With ActiveSheet.PageSetup .CenterFooter = "My Report" .RightFooter = "by Danny Puype" .Orientation = xlLandscape .FitToPagesWide = 1 .FitToPagesTall = 1End With
Range("A1:G250").PrintOut
End Sub
Page 87 of 92
EasyRemove all Hyperlinks in sheet (ID 16)
Sub RemoveHyperlinks()
'Remove all hyperlinks from the active sheet ActiveSheet.Hyperlinks.Delete
End Sub
EasyRemove Styles (ID 64)
Sub removeStyles()Dim sty As StyleFor Each sty In ActiveWorkbook.StylesIf sty.BuiltIn = False Thensty.DeleteEnd IfNextEnd Sub
Page 88 of 92
EasySheet number and name (ID 71)
Sub addSheetNumber() Dim n As Integer Dim Sheet As Object n = 0 For Each Sheet In Sheets() n = n + 1 Sheet.Activate Range("A1").Select ActiveCell.FormulaR1C1 = "Sheet" + Str(n) Next End Sub
Sub addSheetName() Dim n As Integer Dim Sheet As Object n = 0 For Each Sheet In Sheets() n = n + 1 Sheet.Activate Range("A2").Select ActiveCell.FormulaR1C1 = ActiveSheet.name Debug.Print ActiveSheet.name Next
End Sub
Page 89 of 92
EasyShow worksheet names (ID 2)
Sub renderWorksheetNames()
Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ActiveCell.Value = ws.Name ActiveCell.Offset(1, 0).Select Next
End Sub
Sub ShowWorkSheets()
Dim mySheet As Worksheet For Each mySheet In Worksheets MsgBox mySheet.Name Next mySheet
End Sub
Sub ShowWorkSheets2() Dim mySheet As Worksheet Dim result As String For Each mySheet In Worksheets result = result & mySheet.Name & vbCrLf Next mySheet MsgBox result Range("A10") = result
End Sub
Page 90 of 92
EasyZooming (ID 101)
Sub ZoomIn() Dim myZoom As Integer myZoom = ActiveWindow.Zoom + 10 If myZoom <= 400 Then ActiveWindow.Zoom = myZoom End IfEnd Sub
Sub ZoomOut() Dim myZoom As Integer myZoom = ActiveWindow.Zoom ‐ 10 If myZoom >= 10 Then ActiveWindow.Zoom = myZoom End IfEnd Sub
Page 91 of 92