projectadvanceitt-150707174943-lva1-app6891.pdf

Itt

1. KUMARSAMBHAV
CRO-0407447
BATCH NO. 2
MR. SANDEEP
TYAGI
(ADVANCE ITT
FACULTY)
SUBMITTED TOSUBMITTED BY
GHAZIABAD BRANCH OF
CIRC OF ICAI
ADVANCE ITT

2. TOPIC
QUERIES OF MS-EXCEL
ON MACROS

3. 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

4. Visual BasicInterface
Microsoft Excel Interface

5. Question 2
How 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

6. Visual BasicInterface
Microsoft Excel Interface

7. Question 3
How 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

8. CRITERIA
Marks in each subject are
out of 100 and student
scoring total marks 150 is
Passed irrespective of
scoring in each subject.
Visual BasicInterface
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

9. Microsoft Excel Interface
On Click event of Buttons Total Marks
got calculated and Result shown

10. Question 4
How 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

11. Visual BasicInterface
Microsoft Excel Interface
On Click event of Button Worksheets
have been added with names contained
in the cells selected

12. Question 5
How 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)

13. Visual BasicInterface
For Lower
Case
For Upper
Case “Click” event of
Button
For Proper
Case

14. Microsoft Excel Interface

15. Question 6
How 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

16. Visual BasicInterface
“Click” event of
Button
“Elseif” Argument to check Second
Number when result of first
argument is false
“If” argument to
check First
Number

17. On Click event of Button Result is
displayed after checking both
argument mentioned in previous slide
Microsoft Excel Interface

18. If On Click event of Button Result of
both argument (metioned previously)
is false then above showed Result will
be displayed
Microsoft Excel Interface

19. Question 7
How 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

20. Visual BasicInterface
Argument to
check Positive
Number
Argument to check
Negative Number
Microsoft Excel Interface

21. Microsoft Excel Interface

22. Question 8
How 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

23. Visual BasicInterface
“Click” event of
Button
Argument to
check Odd
Number
Argument to
Even Number

24. On Click event of Button different
Result is displayed after checking both
argument mentioned in previous slide
Microsoft Excel Interface

25. Question 9
How 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

26. 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

27. 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
Addition
Set of
Arguments for
Subtration
Set of
Arguments for
Multiplication
Set of
Arguments for
Division
Visual BasicInterface

28. On Click event of Buttons Different
Operations are Performed as per Visual
Basic Coding
Microsoft Excel Interface

29. On Click event of Buttons Different
Operations are Performed as per Visual
Basic Coding
Microsoft Excel Interface

30. Question 10
How 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

31. 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 BasicInterface

32. Number for which Multiple
required is Entered
On Click Event of Button Loop
get Executed and continued till
multiple reached its 10
th
value
Microsoft Excel Interface

33. 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 11
How 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.

34. 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 Interest
Set of
Arguments for
Compound
Interest
Visual BasicInterface

35. On Click event of Buttons
Different Operations are
Performed as per Visual Basic
Coding
Microsoft Excel Interface

36. 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 12
How 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.

37. Do while is an
Exit Level Loop
Visual BasicInterface

38. On Click event of the Buttons DO
WHILE LOOP Executed
Microsoft Excel Interface

39. 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 13
How 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.

40. Argument to
display Message
Box if
Characters
entered are not
equal to 10
Visual BasicInterface
“Len” and “If” are
used in a combination
to check the Number
of Characters entered
in a cell

41. Microsoft Excel Interface
On Click event of the Button
Message is displayed if
characters entered are not equal
to 10

42. 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 14
How 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.

43. Visual BasicInterface
Argument for
Arithematic Mean
Use of “For” loop to
display Series

44. Microsoft Excel Interface
On Click event of the Button
Arithematic Series up to 10
Executed

45. 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 14
How 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.

46. Visual BasicInterface
Argument for
Geometric Mean
Use of “For” loop to
display Series

47. Microsoft Excel Interface
On Click event of the Button
Geometric Series Executed upto
n=10

48. THANK YOU