Please enable JavaScript.
Coggle requires JavaScript to display documents.
Excel VBA Programming - Coggle Diagram
Excel VBA Programming
"For...Next" statement
-
How to find the Last Row using VBA
- To find last row using VBA, we can write a line which would make it easier
- This line would be:
LastRow = Range (Column & Row). End (xlDown). row
-
-
"Do...Loop" statement
Structure fot "Do...While" statement
Do While Condition(s) (this line is to check if the conditions are true)
Statements to be executed multiple times
Loop
Do Until Condition(s) (this line is to check if the conditions have become true)
Statements to be executed multiple times
Loop
E.g.
Dim rowmum As Integer
rownum = 2 (important)
Do While rownum < 12 (Do Until rownum > 12)
Range ("C" & rownum).Value = _
Range ("A" 7 rownum).Value + Range ("B" & rownum).Value
rownum = rownum + 1 (this line will execute until it reaches the last row indicated)
Loop
-
Function
- A function procedure is a set of VBA statements that perform tasks and returns a value
- can write a function procedure to be
- used inside the Excel Workbook as a formula (e.g. =VLOOKUp)
- called inside a subroutine or another function
- helps to break large set of codes into separate subroutines and call them in a main subroutine to improve code readability
- reduces time and effort spent in rewriting code by offering code readability
- the main difference between a function and subroutine is that a function returns a value, whereas a subroutine does not
-can execute a subroutine in many ways whereas can only execute a function in 2 ways
When to use "For...Next" and when to use "Do...Loop"
Manual Steps:
- Start at row 2
- Go across columns C and D
- Count the number of X without yellow fill
- When count > 6, Fail = "Yes"
- Else, Fail = "No"
- Once done, repeat steps 2 to 5 for the remaining rows