100 Deliverables - Hands on MS Excel Macros & VBA for Excel

---

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    

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

    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 

        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      

    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    

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, 

     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    

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

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

77. Print contiguous and non-contiguous ranges

78. Dash Boards 

79. Create Charts

80.Controls and Alerts Sheet

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

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