project advance itt

KUMARSAMBHAVCRO-0407447BATCH NO. 2

MR. SANDEEP TYAGI(ADVANCE ITT

FACULTY)

SUBMITTED TO

SUBMITTED BY

GHAZIABAD BRANCH OF CIRC OF ICAI

ADVANCE ITT

TOPICQUERIES OF MS-EXCEL

ON MACROS

Question 1 How to Swap Numbers Entered in two different Cells?

Step 1 : Insert a BUTTON CONTROL from CONTROL S IN DEVELOPER TAB.Step 2 : Assign micro on click event of this Button.

Step 3: Macro Coding in Visual Basic Language Dim a, b, c As Integer a = Range("a2").Value b = Range("a3").Value c = a a = b b = c Range("a2").Value = a Range("a3").Value = b

On the Click event of

Button “SWAP NUMBERS”

the numbers entered in

Cell A2 and Cell A3 got

Swapped

Visual Basic Interface

Microsoft Excel Interface

Question 2How to ENTER NUMBER IN A CELL?

Step 1 : Insert a BUTTON CONTROL from CONTROLS IN DEVELOPER TAB.Step 2 : Assign micro on click event of this Button.

Step 3: Macro Coding in Visual Basic Language Dim a As Integer a = 33 Range("a9").Value = a

Visual Basic Interface

Microsoft Excel Interface

On Click event of Button

Value assigned through

Macro successfully

Entered in the Cell.

Question 3How to Calculate Total Marks and Show Result on that basis?

Step 1 : Insert a BUTTON CONTROL from CONTROL S IN DEVELOPER TAB.Step 2 : Assign micro on click event of this Button.

Step 3: Macro Coding in Visual Basic Language To calculate Total Marks Dim a, b, c, d As Integer a = Range("b19").Value b = Range("b20").Value c = Range("b21").Value d = a + b + c Range("b23").Value = d

CRITERIA Marks in each subject are out of 100 and student scoring total marks 150 is Passed irrespective of scoring in each subject.

Visual Basic Interface

Conti…Step 3: Macro Coding in Visual Basic Language To Show Result Dim a As Integer Dim b As String a = Range("b23").Value If a >= 150 Then b = "PASS" Range("b25").Value = b Else: b = "Fail" Range("b25").Value = b Range("b25").Value = b End If

Microsoft Excel Interface

On Click event of Buttons Total Marks got calculated and Result shown

Question 4How to add worksheets with name contained in each cell selected ?

Step 1 : Insert a BUTTON CONTROL from CONTROL S IN DEVELOPER TAB.Step 2 : Assign micro on click event of this Button.

Step 3: Macro Coding in Visual Basic Language For Each cell In Selection Worksheets.Add.Name = cell.Value Next

Visual Basic Interface

Microsoft Excel Interface

On Click event of Button Worksheets have been added with names contained in the cells selected

Question 5How to Change case of text entered to upper case , lower case and proper case?

Step 1 : Insert a BUTTON CONTROL from CONTROL S IN DEVELOPER TAB.Step 2 : Assign micro on click event of this Button.

Step 3: Macro Coding in Visual Basic Language For Upper Case Range("a37").Value = VBA.StrConv(Range("a37").Value, vbUpperCase) For Lower Case Range("a37").Value = VBA.StrConv(Range("a37").Value, vbLowerCase) For Proper Case Range("a37").Value = VBA.StrConv(Range("a37").Value, vbProperCase)

Visual Basic Interface

For Lower Case

For Upper Case

“Click” event of Button

For Proper Case

Microsoft Excel Interface

Question 6How to show greater number between two numbers?

Step 1 : Insert a BUTTON CONTROL from CONTROL S IN DEVELOPER TAB.Step 2 : Assign micro on click event of this Button.Step 3: Macro Coding in Visual Basic Language Dim a, b As Integer Dim c As String a = Range("b50").Value b = Range("b51").Value If a > b Then c = "First Number is Greater than Second" Range("b53").Value = c ElseIf b > a Then c = "Second Number is Greater than First" Range("b53").Value = c Else: c = "Both are Equal" Range("b53").Value = c End If

Visual Basic Interface

“Click” event of Button“Elseif” Argument to check

Second Number when result of first argument is false“If” argument

to check First Number

On Click event of Button Result is displayed after checking both argument mentioned in previous slide

Microsoft Excel Interface

If On Click event of Button Result of both argument (metioned previously) is false then above showed Result will be displayed

Microsoft Excel Interface

Question 7How to Check whether number entered in a cell is positive or negative?

Step 1 : Insert a BUTTON CONTROL from CONTROL S IN DEVELOPER TAB.Step 2 : Assign micro on click event of this Button.Step 3: Macro Coding in Visual Basic Language Dim a As Integer Dim b As String a = Range("B60").Value If a > 0 Then b = "Number Entered is Positive" Range("b61").Value = b ElseIf a < 0 Then b = "Number Entered is Negative" Range("b61").Value = b ElseIf a = 0 Then b = "Number Entered is Zero" Range("b61").Value = b Else: b = "" Range("b61").Value = b End If

Visual Basic Interface

Argument to check Positive NumberArgument to check Negative Number

Microsoft Excel Interface

Microsoft Excel Interface

Question 8How to Check whether number entered in a cell is Even or odd?

Step 1 : Insert a BUTTON CONTROL from CONTROL S IN DEVELOPER TAB.Step 2 : Assign micro on click event of this Button.Step 3: Macro Coding in Visual Basic Language Dim a As Integer Dim b As String a = Range("B69").Value If a Mod 2 = 0 Then b = "Number Entered is Even" Range("b70").Value = b ElseIf a Mod 2 <> 0 Then b = "Number Entered is Odd" Range("b70").Value = b Else: b = "" Range("b70").Value = b End If

Visual Basic Interface

“Click” event of Button

Argument to check Odd Number

Argument to Even Number

On Click event of Button different Result is displayed after checking both argument mentioned in previous slide

Microsoft Excel Interface

Question 9How to add , subtract, multiply and divide two numbers ?

Step 1 : Insert a BUTTON CONTROL from CONTROL S IN DEVELOPER TAB.Step 2 : Assign micro on click event of this Button.

Step 3: Macro Coding in Visual Basic Language For Addition Dim a, b, c As String a = Range("b78").Value b = Range("b79").Value c = Range("b81").Value c = a + b Range("b81").Value = c

Continue…Step 3: Macro Coding in Visual Basic Language To Subtract Dim a, b, c As String a = Range("b78").Value b = Range("b79").Value c = Range("b81").Value c = a - b Range("b81").Value = c

To Multiply Dim a, b, c As String a = Range("b78").Value b = Range("b79").Value c = Range("b81").Value c = a * b Range("b81").Value = c

Continue…Step 3: Macro Coding in Visual Basic Language To Divide Dim a, b, c As String a = Range("b78").Value b = Range("b79").Value c = Range("b81").Value c = a / b Range("b81").Value = c

Set of Arguments for AdditionSet of Arguments for SubtrationSet of Arguments for MultiplicationSet of Arguments for Division

Visual Basic Interface

On Click event of Buttons Different Operations are Performed as per Visual Basic Coding

Microsoft Excel Interface

On Click event of Buttons Different Operations are Performed as per Visual Basic Coding

Microsoft Excel Interface

Question 10How to print multiple s of number entered in a cell?

Step 1 : Insert a BUTTON CONTROL from CONTROL S IN DEVELOPER TAB.Step 2 : Assign micro on click event of this Button.

Step 3: Macro Coding in Visual Basic Language For Addition Dim i, a, b, row As Integer a = Range("b88").Value row = 93 For i = 1 To 10 b = i * a Cells(row, 2).Value = b row = row + 1 Next

Argument to set variable “a” Range where Number will Entered

Argument to Set Starting Row Number For Result

Argument of “FOR” loop using which this set of argument will continue to give result till loop reach its maximum value assinged which is 10 here

Visual Basic Interface

Number for which Multiple required is Entered

On Click Event of Button Loop get Executed and continued till multiple reached its 10th value

Microsoft Excel Interface

Step 3: Macro Coding in Visual Basic Language For Simple Interest Dim a, b, c, d As Integer a = Range("b106").Value b = Range("b107").Value c = Range("b108").Value d = (a * b * c) / 100 Range("b110").Value = d

Question 11How to calculate simple interest and compound interest?

Step 2 : Assign micro on click event of these Buttons .

Step 1 : Insert two BUTTON CONTROL from CONTROL S IN DEVELOPER TAB.

Continue…Step 3: Macro Coding in Visual Basic Language For Compound Interest Dim a, b, c, d As long a = Range("b106").Value b = Range("b107").Value c = Range("b108").Value d = a * (1 + b / 100) ^ c Range("b114").Value = d

Set of Arguments for Simple InterestSet of Arguments for Compound Interest

Visual Basic Interface

On Click event of Buttons Different Operations are Performed as per Visual Basic Coding

Microsoft Excel Interface

Step 3: Macro Coding in Visual Basic Language Dim i, row As Integer i = 1 row = 120 Do Cells(row, 2).Value = i row = row + 1 i = i + 1 Loop While (i <= 10)

Question 12How to use “do while loop” ?

Step 2 : Assign micro on click event of this Buttons.

Step 1 : Insert a BUTTON CONTROL from CONTROL S IN DEVELOPER TAB.

Do while is an Exit Level Loop

Visual Basic Interface

On Click event of the Buttons DO WHILE LOOP Executed

Microsoft Excel Interface

Step 3: Macro Coding in Visual Basic Language Dim i As Long Dim a As String i = 2 a = Range("b121").Value If Len(a) <> 10 Then MsgBox "Enter Ten Character Text" End If

Question 13How to restrict number of characters to be entered in a cell ?

Step 2 : Assign micro on click event of this Button .

Step 1 : Insert a BUTTON CONTROL from CONTROL S IN DEVELOPER TAB.

Argument to display Message Box if Characters entered are not equal to 10

Visual Basic Interface

“Len” and “If” are used in a combination to check the Number of Characters entered in a cell

Microsoft Excel InterfaceOn Click event of the Button Message is displayed if characters entered are not equal to 10

Step 3: Macro Coding in Visual Basic Language Dim i, a, b, d, row As Integer d = 1 a = 1 Range("b144").Value = a row = 145 For i = 2 To 10 b = a + (i - 1) * d Cells(row, 2).Value = b row = row + 1 Next

Question 14How to print arithematic series using for loop in macros ?

Step 2 : Assign micro on click event of this Button.

Step 1 : Insert a BUTTON CONTROL from CONTROL S IN DEVELOPER TAB.

Visual Basic Interface

Argument for Arithematic Mean

Use of “For” loop to display Series

Microsoft Excel Interface

On Click event of the Button Arithematic Series up to 10 Executed

Step 3: Macro Coding in Visual Basic Language Dim i, a, b, row As Integer b = 5 Range("b160").Value = b row = 161 For i = 1 To 9 a = b * (5) ^ i Cells(row, 2).Value = a row = row + 1 Next

Question 14How to print geometric series using for loop in macros ?

Step 1 : Insert a BUTTON CONTROL from CONTROL S IN DEVELOPER TAB.Step 2 : Assign micro on click event of this Button.

Visual Basic Interface

Argument for Geometric Mean

Use of “For” loop to display Series

Microsoft Excel Interface

On Click event of the Button Geometric Series Executed upto n=10

THANK YOU