Create Excel Macro Without Having Programming Knowledge

http://sumsulislam.wetpaint.com, [email protected], Please be a member of anushilony groups of YahooGroups.

1. Sub FormattingMacro () With Selection.Font .Name = “Arial” .FontStyle = “Bold” .Size = 16 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 3 End With End Sub 2. Sub FormattingMacro() With Selection.Font .Name = “Arial” .FontStyle = “Bold” .Size = 14 .ColorIndex = 3 End With End Sub 3. Sub ConvertFormulas() Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End Sub Tags Range(“B1:B10”).Select ActiveCell.Offset(0, 1).Range(“A1:A10”).Select A single statement can be as long as needed. However, you may want to break the statement into two or more lines. To do so, insert a space followed by an underscore(_). The following code, although written as two lines, is actually a single VBA statement: Sheets(“Sheet1”).Range(“B1”).Value = _ Sheets(“Sheet1”).Range(“A1”).Value You can insert comments freely into your VBA code. The comment indicator is an apostrophe single quote character (‘). Any text that follows a single quote is ignored. A comment can be a line by itself or can be inserted after a statement. The following examples show two comments: ‘Assign the values to the variables Rate = .085 ‘Rate as of November 16 4. A VBA module consists of procedures. A procedure is basically computer code that performs some action. The following is an example of a simple Sub procedure S M Sumsul Islam, Sr Training Executive-IT, ACI Limited, 245 Tejgaon I/A, Dhaka -1208. Cell: +8801730028467

http://sumsulislam.wetpaint.com, [email protected], Please be a member of anushilony groups of YahooGroups.

called ShowSum (it adds 1 + 1 and displays the result): Sub ShowSum() Sum = 1 + 1 MsgBox “The answer is “ & Sum End Sub 5. Function AddTwo(arg1, arg2) AddTwo = arg1 + arg2 End Function Tags

You refer to an object in your VBA code by specifying its position in the object hierarchy, using a period as a separator Application.Workbooks(“Book1”) Application.Workbooks(“Book1”).Worksheets(“Sheet1”) Application.Workbooks(“Book1”).Worksheets(“Sheet1”).Range(“A1”) Worksheets(“Sheet1”).Range(“A1”) Range(“A1”) S M Sumsul Islam, Sr Training Executive-IT, ACI Limited, 245 Tejgaon I/A, Dhaka -1208. Cell: +8801730028467

http://sumsulislam.wetpaint.com, [email protected], Please be a member of anushilony groups of YahooGroups.

You refer to properties by combining the object with the property, separated by a period. For example, you can refer to the value in cell A1 on Sheet1 as follows: Worksheets(“Sheet1”).Range(“A1”).Value Interest = Worksheets(“Sheet1”).Range(“A1”).Value Worksheets(“Sheet1”).Range(“A1:C12”).ClearContents You refer to a property in your VBA code by placing a period and the property name after the object’s name. For example, the following VBA statement sets the Value property of a range named Frequency to 15. (That is, the statement causes the number 15 to appear in the range’s cells.) Range(“frequency”).Value = 15 A Range object also has a Formula property, which is not read-only; that is, you can insert a formula into a cell by changing its Formula property. The following statement inserts a formula into cell A1 by changing the cell’s Formula property: Range(“A1”).Formula = “=SUM(A1:A10)” ActiveCell.Row Workbooks(“Sales.xls”).Worksheets(“Summary”) Workbooks(1).Worksheets(1) Workbooks(1).Sheets(1) Application.ActiveWorkbook.ActiveSheet ActiveWorkbook.ActiveSheet ActiveSheet Methods Objects also have methods. You can think of a method as an action taken with an object. For example, Range objects have a Clear method. The following VBA statement clears a Range, an action that is equivalent to selecting the Range and then choosing Edit? Clear? All: Range(“A1:C12”).Clear rate = Worksheets(“Sheet1”).Range(“A1”).Value 6. Sub CheckCell() If ActiveCell.Value < 0 Then ActiveCell.Font.ColorIndex = 3 End Sub 7. Sub SumSquared() Total = 0 For Num = 1 To 10 Total = Total + (Num ^ 2) Next Num MsgBox Total End Sub 8. Sub AlignCells() With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False S M Sumsul Islam, Sr Training Executive-IT, ACI Limited, 245 Tejgaon I/A, Dhaka -1208. Cell: +8801730028467

http://sumsulislam.wetpaint.com, [email protected], Please be a member of anushilony groups of YahooGroups.

.Orientation = xlHorizontal End With End Sub 9. Sub AlignCells() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.WrapText = False Selection.Orientation = xlHorizontal End Sub 10. Sub CheckCell() Select Case ActiveCell.Value Case Is < 0 ActiveCell.Font.ColorIndex = 3 ‘Red Case 0 ActiveCell.Font.ColorIndex = 5 ‘Blue Case Is > 0 ActiveCell.Font.ColorIndex = 1 ‘Black End Select End Sub 11. Sub SelectiveFormat() Message = “Change attributes of values greater than or equal_to...” Target = InputBox(Message) Target=Val(Target) ‘Evaluate each cell in the selection For Each Item In Selection If IsNumeric(Item) Then If Item.Value >= Target Then With Item .Font.Bold = True .Font.ColorIndex = 3 ‘Red End With End If End If Next Item End Sub 12. If TypeName(Selection) <> “Range” Then Exit Sub 13. If Target = “” then Exit Sub 14. Function NumSign(InVal) Select Case InVal Case Is < 0 NumSign = “Negative” Case 0 NumSign = “Zero” S M Sumsul Islam, Sr Training Executive-IT, ACI Limited, 245 Tejgaon I/A, Dhaka -1208. Cell: +8801730028467

http://sumsulislam.wetpaint.com, [email protected], Please be a member of anushilony groups of YahooGroups.

Case Is > 0 NumSign = “Positive” End Select End Function 15. Function User() User = Application.UserName End Function 16. Function Commission(Sales) ‘Calculates sales commissions Tier1 = 0.08 Tier2 = 0.105 Tier3 = 0.12 Tier4 = 0.14 Select Case Sales Case 0 To 9999.99 Commission = Sales * Tier1 Case 1000 To 19999.99 Commission = Sales * Tier2 Case 20000 To 39999.99 Commission = Sales * Tier3 Case Is >= 40000 Commission = Sales * Tier4 End Select End Function 17. Function Commission2(Sales, Years) ‘Calculates sales commissions based on years in service Tier1 = 0.08 Tier2 = 0.105 Tier3 = 0.12 Tier4 = 0.14 Select Case Sales Case 0 To 9999.99 Commission2 = Sales * Tier1 Case 1000 To 19999.99 Commission2 = Sales * Tier2 Case 20000 To 39999.99 Commission2 = Sales * Tier3 Case Is >= 40000 Commission2 = Sales * Tier4 End Select Commission2 = Commission2 + (Commission2 * Years / 100) End Function 18. Assume that you want to calculate the average of the five largest values in a range named Data. Excel doesn’t have a function that can do this, so you can write the following formula: =(LARGE(Data,1)+LARGE(Data,2)+LARGE(Data,3)+LARGE(Data,4)+LARGE(Data,5))/5 19. S M Sumsul Islam, Sr Training Executive-IT, ACI Limited, 245 Tejgaon I/A, Dhaka -1208. Cell: +8801730028467

http://sumsulislam.wetpaint.com, [email protected], Please be a member of anushilony groups of YahooGroups.

Function TopAvg(InRange, Num) ‘Returns the average of the highest Num values in InRange Sum = 0 For i = 1 To Num Sum = Sum + WorksheetFunction.Large(InRange, i) Next i TopAvg = Sum / Num End Functio 20. The following is an example of a simple macro that makes each cell in the selected range uppercase (but it skips cells that have a formula). The procedure uses VBA’s built-in StrConv function. Sub ChangeCase() For Each cell In Selection If Not cell.HasFormula Then cell.Value = StrConv(cell.Value, vbUpperCase) End If Next cell End Sub Tags InputBox(prompt[,title][,default]) CName = InputBox(“Customer name?”,”Customer Data”) MsgBox(prompt[,buttons][,title]) 21. Sub MsgBoxDemo() MsgBox “Click OK to continue” End Sub 22. Sub GetAnswer() Ans = MsgBox(“Continue?”, vbYesNo) Select Case Ans Case vbYes ‘...[code if Ans is Yes]... Case vbNo ‘...[code if Ans is No]... End Select End Sub

S M Sumsul Islam, Sr Training Executive-IT, ACI Limited, 245 Tejgaon I/A, Dhaka -1208. Cell: +8801730028467

http://sumsulislam.wetpaint.com, [email protected], Please be a member of anushilony groups of YahooGroups.

23. Sub GetAnswer() Config = vbYesNo + vbQuestion + vbDefaultButton2 Ans = MsgBox(“Process the monthly report?”, Config) If Ans = vbYes Then RunReport If Ans = vbNo Then Exit Sub End Sub 24. Sub GetAnswer2() Msg = “Do you want to process the monthly report?” Msg = Msg & vbNewLine & vbNewLine Msg = Msg & “Processing the monthly report will take approximately “ Msg = Msg & “15 minutes. It will generate a 30-page report for all “ Msg = Msg & “sales offices for the current month.” Title = “XYZ Marketing Company” Config = vbYesNo + vbQuestion Ans = MsgBox(Msg, Config, Title) If Ans = vbYes Then RunReport If Ans = vbNo Then Exit Sub End Sub 25.

S M Sumsul Islam, Sr Training Executive-IT, ACI Limited, 245 Tejgaon I/A, Dhaka -1208. Cell: +8801730028467

http://sumsulislam.wetpaint.com, [email protected], Please be a member of anushilony groups of YahooGroups.

Displaying a UserForm You also need to write a procedure to display the UserForm. You use the Show method of the UserForm object. The following procedure displays the UserForm named UserForm1: Sub ShowDialog() UserForm1.Show End Sub Private Sub OKButton_Click() Unload AboutBox End Sub

S M Sumsul Islam, Sr Training Executive-IT, ACI Limited, 245 Tejgaon I/A, Dhaka -1208. Cell: +8801730028467

http://sumsulislam.wetpaint.com, [email protected], Please be a member of anushilony groups of YahooGroups.

S M Sumsul Islam, Sr Training Executive-IT, ACI Limited, 245 Tejgaon I/A, Dhaka -1208. Cell: +8801730028467

http://sumsulislam.wetpaint.com, [email protected], Please be a member of anushilony groups of YahooGroups.

Private Sub Workbook_Open() If Weekday(Now) = 6 Then Msg = “Make sure you do your weekly backup!” MsgBox Msg, vbInformation End If End Sub Private Sub Workbook_Open() Application.WindowState = xlMaximized ActiveWindow.WindowState = xlMaximized Worksheets(“DataEntry”).Activate Range(“A1”).Select End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) On Error Resume Next Range(“A1”).Select End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) If TypeName(Sh) = “Worksheet” Then Range(“A1”).Select End Sub Private Sub Workbook_NewSheet(ByVal Sh As Object) If TypeName(Sh) = “Worksheet” Then _ Range(“A1”) = “Sheet added “ & Now() End Sub Private Sub Workbook_BeforeSave _ (ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI Then MsgBox “Click OK to display the Save As dialog box.” End If End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range) S M Sumsul Islam, Sr Training Executive-IT, ACI Limited, 245 Tejgaon I/A, Dhaka -1208. Cell: +8801730028467

http://sumsulislam.wetpaint.com, [email protected], Please be a member of anushilony groups of YahooGroups.

MsgBox “Range “ & Target.Address & “ was changed.” End Sub Monitoring a specific range for changes Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim VRange As Range Set VRange = Range(“InputRange”) If Union(Target, VRange).Address = VRange.Address Then Msgbox “The changed cell is in the input range.” End if End Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range) Set VRange = Range(“InputRange”) For Each cell In Target If Union(cell, VRange).Address = VRange.Address Then Msgbox “The changed cell is in the input range.” End if Next cell End Sub Using the SelectionChange event Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Cells.Interior.ColorIndex = xlNone With ActiveCell .EntireRow.Interior.ColorIndex = 36 .EntireColumn.Interior.ColorIndex = 36 End With End Sub Using the BeforeRightClick event Private Sub Worksheet_BeforeRightClick _ (ByVal Target As Excel.Range, Cancel As Boolean) Cancel = True MsgBox “The shortcut menu is not available.” End Sub Not worksheet or workbook event: The events discussed in this chapter are associated with an object (Application, Workbook, Sheet, and so on). Using the OnTime event Sub SetAlarm() Application.OnTime 0.625, “DisplayAlarm” End Sub Sub DisplayAlarm() Beep MsgBox “Wake up. It’s time for your afternoon break!” End Sub Copying a range Sub CopyRange() Range(“A1:A5”).Select Selection.Copy Range(“B1”).Select S M Sumsul Islam, Sr Training Executive-IT, ACI Limited, 245 Tejgaon I/A, Dhaka -1208. Cell: +8801730028467

http://sumsulislam.wetpaint.com, [email protected], Please be a member of anushilony groups of YahooGroups.

ActiveSheet.Paste Application.CutCopyMode = False End Sub

S M Sumsul Islam, Sr Training Executive-IT, ACI Limited, 245 Tejgaon I/A, Dhaka -1208. Cell: +8801730028467