create excel macro without having programming knowledge

8/14/2019 Create Excel Macro Without Having Programming Knowledge

1/12

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

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

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 = 3End WithEnd Sub

2.Sub FormattingMacro()With Selection.Font.Name = Arial

.FontStyle = Bold

.Size = 14

.ColorIndex = 3End WithEnd Sub

3.Sub ConvertFormulas()Selection.CopySelection.PasteSpecial Paste:=xlPasteValues, _Operation:=xlNone, SkipBlanks:=False, Transpose:=FalseApplication.CutCopyMode = False

End Sub

Tags

Range(B1:B10).SelectActiveCell.Offset(0, 1).Range(A1:A10).Select

A single statement can be as long as needed. However, you may want to break the statement intotwo 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 singlequote character (). Any text that follows a single quote is ignored. A comment can be a line by itselfor can be inserted after a statement. The following examples show two comments:

Assign the values to the variablesRate = .085 Rate as of November 16

4.A VBA module consists of procedures. A procedure is basically computer codethat performs some action. The following is an example of a simple Sub procedure


2/12



called ShowSum (it adds 1 + 1 and displays the result):

Sub ShowSum()Sum = 1 + 1MsgBox The answer is & SumEnd Sub

5.Function AddTwo(arg1, arg2)AddTwo = arg1 + arg2End Function

Tags

You refer to an object in your VBA code by specifying its position in the object hierarchy, using aperiod as a separator

Application.Workbooks(Book1)Application.Workbooks(Book1).Worksheets(Sheet1)Application.Workbooks(Book1).Worksheets(Sheet1).Range(A1)Worksheets(Sheet1).Range(A1)Range(A1)


3/12



You refer to properties by combining the object with the property, separated by a period. Forexample, you can refer to the value in cell A1 on Sheet1 as follows:

Worksheets(Sheet1).Range(A1).ValueInterest = Worksheets(Sheet1).Range(A1).ValueWorksheets(Sheet1).Range(A1:C12).ClearContents

You refer to a property in your VBA code by placing a period and the property name after theobjects name. For example, the following VBA statement sets the Value property of a range namedFrequency to 15. (That is, the statement causes the number 15 to appear in the ranges cells.)

Range(frequency).Value = 15

A Range object also has a Formula property, which is not read-only; that is, you can insert a formulainto a cell by changing its Formula property. The following statement inserts a formula into cell A1by changing the cells Formula property:

Range(A1).Formula = =SUM(A1:A10)

ActiveCell.RowWorkbooks(Sales.xls).Worksheets(Summary)Workbooks(1).Worksheets(1)Workbooks(1).Sheets(1)Application.ActiveWorkbook.ActiveSheetActiveWorkbook.ActiveSheetActiveSheet

MethodsObjects 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 isequivalent 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 = 3End Sub

7.Sub SumSquared()Total = 0For Num = 1 To 10

Total = Total + (Num ^ 2)Next NumMsgBox TotalEnd Sub

8.Sub AlignCells()With Selection.HorizontalAlignment = xlCenter.VerticalAlignment = xlCenter.WrapText = False


4/12



.Orientation = xlHorizontalEnd WithEnd Sub

9.Sub AlignCells()Selection.HorizontalAlignment = xlCenterSelection.VerticalAlignment = xlCenter

Selection.WrapText = FalseSelection.Orientation = xlHorizontalEnd Sub

10.Sub CheckCell()Select Case ActiveCell.ValueCase Is < 0ActiveCell.Font.ColorIndex = 3 RedCase 0ActiveCell.Font.ColorIndex = 5 BlueCase Is > 0

ActiveCell.Font.ColorIndex = 1 BlackEnd SelectEnd 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 selectionFor Each Item In SelectionIf IsNumeric(Item) Then

If Item.Value >= Target ThenWith Item.Font.Bold = True.Font.ColorIndex = 3 RedEnd WithEnd IfEnd IfNext ItemEnd Sub

12.If TypeName(Selection) Range Then Exit Sub

13.If Target = then Exit Sub

14.Function NumSign(InVal)Select Case InValCase Is < 0NumSign = NegativeCase 0NumSign = Zero


5/12



Case Is > 0NumSign = PositiveEnd SelectEnd Function

15.Function User()User = Application.UserName

End Function

16.Function Commission(Sales) Calculates sales commissionsTier1 = 0.08Tier2 = 0.105Tier3 = 0.12Tier4 = 0.14Select Case SalesCase 0 To 9999.99Commission = Sales * Tier1

Case 1000 To 19999.99Commission = Sales * Tier2Case 20000 To 39999.99Commission = Sales * Tier3Case Is >= 40000Commission = Sales * Tier4End SelectEnd Function

17.Function Commission2(Sales, Years) Calculates sales commissions based on years in service

Tier1 = 0.08Tier2 = 0.105Tier3 = 0.12Tier4 = 0.14Select Case SalesCase 0 To 9999.99Commission2 = Sales * Tier1Case 1000 To 19999.99Commission2 = Sales * Tier2Case 20000 To 39999.99Commission2 = Sales * Tier3Case Is >= 40000

Commission2 = Sales * Tier4End SelectCommission2 = 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 doesnt have a function that can do this, so you can write the following formula:

=(LARGE(Data,1)+LARGE(Data,2)+LARG E(Data,3)+LARGE(Data,4)+LARGE(Data,5))/5

19.


6/12



Function TopAvg(InRange, Num) Returns the average of the highest Num values in InRangeSum = 0For i = 1 To NumSum = Sum + WorksheetFunction.Large(InRange, i)Next iTopAvg = Sum /NumEnd 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 VBAs built-in StrConv function.

Sub ChangeCase()For Each cell In SelectionIf Not cell.HasFormula Thencell.Value = StrConv(cell.Value, vbUpperCase)End IfNext 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 continueEnd Sub

22.Sub GetAnswer()Ans = MsgBox(Continue?, vbYesNo)Select Case AnsCase vbYes ...[code if Ans is Yes]...Case vbNo ...[code if Ans is No]...End SelectEnd Sub


7/12



23.Sub GetAnswer()Config = vbYesNo + vbQuestion + vbDefaultButton2

Ans = MsgBox( Process the monthly report? , Config)If Ans = vbYes Then RunReportIf Ans = vbNo Then Exit SubEnd Sub

24.Sub GetAnswer2()Msg = Do you want to process the monthly report?Msg = Msg & vbNewLine & vbNewLineMsg = 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 CompanyConfig = vbYesNo + vbQuestionAns = MsgBox(Msg, Config, Title)If Ans = vbYes Then RunReportIf Ans = vbNo Then Exit SubEnd Sub

25.


8/12



Displaying a UserFormYou also need to write a procedure to display the UserForm. You use the Show method of theUserForm object. The following procedure displays the UserForm named UserForm1:

Sub ShowDialog()UserForm1.ShowEnd Sub

Private Sub OKButton_Click()Unload AboutBoxEnd Sub


9/12




10/12



Private Sub Workbook_Open()If Weekday(Now) = 6 ThenMsg = Make sure you do your weekly backup!MsgBox Msg, vbInformationEnd IfEnd Sub

Private Sub Workbook_Open()Application.WindowState = xlMaximizedActiveWindow.WindowState = xlMaximizedWorksheets(DataEntry).ActivateRange(A1).SelectEnd Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)On Error Resume NextRange(A1).SelectEnd Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)If TypeName(Sh) = Worksheet Then Range(A1).SelectEnd 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 IfEnd Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)


11/12



MsgBox Range & Target.Address & was changed.End SubMonitoring a specific range for changesPrivate Sub Worksheet_Change(ByVal Target As Excel.Range)Dim VRange As RangeSet VRange = Range(InputRange)If Union(Target, VRange).Address = VRange.Address ThenMsgbox The changed cell is in the input range.End ifEnd Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)Set VRange = Range(InputRange)For Each cell In TargetIf Union(cell, VRange).Address = VRange.Address ThenMsgbox The changed cell is in the input range.End ifNext cellEnd Sub

Using the SelectionChange eventPrivate Sub Worksheet_SelectionChange(ByVal TargetAs Excel.Range)Cells.Interior.ColorIndex = xlNoneWith ActiveCell.EntireRow.Interior.ColorIndex = 36.EntireColumn.Interior.ColorIndex = 36End WithEnd Sub

Using the BeforeRightClick eventPrivate Sub Worksheet_BeforeRightClick _(ByVal Target As Excel.Range, Cancel As Boolean)Cancel = TrueMsgBox The shortcut menu is not available.End Sub

Not worksheet or workbook event: The events discussed in this chapter are associated with anobject (Application, Workbook, Sheet, and so on).Using the OnTime eventSub SetAlarm()Application.OnTime 0.625, DisplayAlarm

End SubSub DisplayAlarm()BeepMsgBox Wake up. Its time for your afternoon break!End Sub

Copying a rangeSub CopyRange()Range(A1:A5).SelectSelection.CopyRange(B1).Select


12/12



ActiveSheet.PasteApplication.CutCopyMode = FalseEnd Sub

create excel macro without having programming knowledge

Documents