Signed in as:
filler@godaddy.com
100 Deliverables - Hands on MS Excel Macros & VBA for Excel
1. MS Excel Macros
3, 4 & 5. Defining Data and handling Data Ranges
2. VBA Creating Structure & Concepts
1 Absolute Referencing
2 Relative Referencing 3 Assign to Button
4 Assign Macro to New Tab 5 Assign Macro to Quick Access Tool Bar 6 View Macros
7 Moving beyond Play ground
8 Edit Excel Macro 9 Calling a macro from within another macro 10 Use of Excel Macro as ChatGpt for VBA
2. VBA Creating Structure & Concepts
3, 4 & 5. Defining Data and handling Data Ranges
2. VBA Creating Structure & Concepts
11. Sub Procedure Types
12. Function Types
13. Creation of Shell Structure for 'Sub' and function
14. Creating Command Button for 'Sub'
15. VBA Architecture - Excel Objects, Methods & Properties
3, 4 & 5. Defining Data and handling Data Ranges
3, 4 & 5. Defining Data and handling Data Ranges
3, 4 & 5. Defining Data and handling Data Ranges
16. Data Type and Declaration of Variables
17. Selecting Ranges (Absoulte andd Range Name based)
18. Setting properties for selected cell /range 19. Adding range name
20. Selecting a contiguous range (ending row not known)
21. Defining dynamic range / print range
22. Setting properties for selected cell /range 23. Picking up and accessing range names individually from a list
24. Listing the Range Names
25. Creating hundreds of automatic hyperlinks in VBA
6. Handling Memory Variables
8,9 &10. Handling Input & Data, Validations and Excel Formulas
3, 4 & 5. Defining Data and handling Data Ranges
26. Obtain variable value via Input Box
27. Obtain variable value via Cell
28. Validation of input Parameters
29. Storing memory variable
30. Calculation and placement
31. Incrementing Memory Variable
32. Assigning Values to a series of cells in a relative manner
7. Block Statements
8,9 &10. Handling Input & Data, Validations and Excel Formulas
8,9 &10. Handling Input & Data, Validations and Excel Formulas
33. If..Exit Sub..End If
34. If..Else..End If..
35. While..Wend..
36. Do While…Loop
37. Traversing through each row (For Each Row in….. Next Row)
38. Traversing through each column (For Each Column in ….Next Column)
39. Traversing through each cell (For Each Cell in …. Next Cell)
40. with..end with
41. Coding for successive conditional statements
42. Based on Drop DownValue, give command lines
43. On meeting / not meeting a condition branch out
8,9 &10. Handling Input & Data, Validations and Excel Formulas
8,9 &10. Handling Input & Data, Validations and Excel Formulas
8,9 &10. Handling Input & Data, Validations and Excel Formulas
44. Selecting Rows based on Conditions 45. Applying Conditional Operators in VBA 46. Applying Control 'f' to find out and get the Row number of the first occurrence of a value in a series of sets of data
47. Enabling Data Validation via VBA
48. Select LastCell with data
49. Keeping track of number of entries on a dynamic basis
50. Sort Data
51. Using Key Excel formulas via VBA (Sumif, Countif, Sum, Counta,
11. Normal Edits
14. Create and maintain ledger / transaction / inventory details
12 & 13. Message Box & Error Handling
52. Select All cells in a Sheet
53. Convert text to formula
54. Copy and Paste, Special and in different work books
55. Inserting range / columns/ insert cells down - insert based on user input
56. Formatting (autofit, merge)
57. Using Concatenation inside VBA
58. Date format
12 & 13. Message Box & Error Handling
14. Create and maintain ledger / transaction / inventory details
12 & 13. Message Box & Error Handling
59. Show output in Message Box / Place in Cell
60. Customised Output Message Boxes for Customised Messages
61. Not wanting to display alerts when deleting
62. Error handling
63. ' 'Saving in progress' message via message box
14. Create and maintain ledger / transaction / inventory details
14. Create and maintain ledger / transaction / inventory details
14. Create and maintain ledger / transaction / inventory details
64. Check Existence of Ledger / Inventory / Transactional Sheet
65. Create a Ledger in specified format and allocate space
66. Copying each transaction for listing
67. Alert message for already recorded item / transaction
68. Inserting 'To Balance c/d' or 'By Balance c/d' depending upon the scenario
69. Counting dynamic number of debit / credit entries and storing in a fixed location in ledger
70. Getting a/c balance based on dates
71. Checking whether Trial Balance is Tallied
72. Identifying whether Sum of Debits is >= sum of Credits or vice versa on a dynamic basis
73. Assigning a range for ledger a/c
74. Checking for Space in Sheet / Data Entry in specified range
75. Picking up, date, month and year from value entered
76. Alert message when number of entries are to exceed space
15, 16 & 17. Output, Dash Boards & Controls and Alerts
18 & 19. Handling Workbooks, Worksheets and & Password Protection
14. Create and maintain ledger / transaction / inventory details
77. Print contiguous and non-contiguous ranges
78. Dash Boards
79. Create Charts
80.Controls and Alerts Sheet
18 & 19. Handling Workbooks, Worksheets and & Password Protection
18 & 19. Handling Workbooks, Worksheets and & Password Protection
18 & 19. Handling Workbooks, Worksheets and & Password Protection
81. Activate, Save and close Workbook / Select Worksheet / Range / Row / Cell (Incl Offset)
82. Saving a file in a different name (coded in or user input)
83. Adding Date Value to workbook or sheet name
84. Adding a new sheet ('set' property)
85. Open existing workbook, X Number of New Workbooks, Insert X number of Sheets, Count sheets, iterating through worksheets, Name Sheets and List names
86. Protect Work Sheet with Password
87. Get Password and Protect Work Sheet
88. Automatic Assigning of Password to Worksheets based on Worksheet Name
89. Declaring Proxy Names for Excel Objects such as WorkSheet
90. Opening an existing workbook if already not open
91. Picking up and accessing sheets individually from a list
92. Giving VBA access to worksheet with password
93. Locking of cells (based on memory variable)
94. Saving workbook at regular intervals during lengthy VBA operations
20. Other use case VBA aspects / coding
18 & 19. Handling Workbooks, Worksheets and & Password Protection
18 & 19. Handling Workbooks, Worksheets and & Password Protection
95. Setting properties for other OLE Objects
96. Using VbDefault button2
97. Fetching Invoice Details when recording payment
98. Assigning mixed / absolute referencing to memory variable via VBA
99. Calling a function from a procedure then passing values to another procedure
100. Computation of MACD
101. Identification of Trade Signals (Technical Analysis)
102. Embedding Excel financial & statistical functions in VBA
Illustration of Creating Macro in a Personal Workbook
Illustration of How to Create a MS Excel macro in a Personal Workbook. The macro is created in order to open a new workbook, name it, insert sheets and name them.
A personal workbook macro is available to all workbooks we open in MS excel.
Dr Nala FinSys Consulting, Training and Academia
Marsiling Drive, Singapore
This website uses cookies.
We use cookies to analyze website traffic and optimize your website experience. By accepting our use of cookies, your data will be aggregated with all other user data.
To Go to Our Latest Additions.
Welcome! Check out our new announcement.