Ms excel vba basic concepts. Excel VBA Training

This is a ppt which helps you to understand basic concepts of excel vba.

1. Ms excel vba Lbsti gautam nagar Basic Theory & Concepts 1

2. Microsoft Excel:- MS excel is still the most dominant application on the market when it comes to spreadsheets. Excel allows people who are not programmers to manipulate and analyze data. What if you want a form to prompt a new user to enter data? What if a user needs to ensure the data goes into the correct fields? LBSTI GAUTAM NAGAR 2 # HarshSir 7503412537

3. What is VBA? VBA stands for visual basic for applications. VBA is a way to automate the tasks you do at work. It takes the programming language of visual basic and given the user an easy way to use the language with Microsoft office applications. VBA can use in all office versions, from MS-office 97 to MS-office 2021 and with any available latest versions. Among VBA, excel VBA is the popular one. Why VBA? Knowing VBA can make you better at the job you have now. You can use it to do your job faster, make tasks smoother, and make your work more efficient. VBA enhance the functionality of excel by allowing you to make excel behave according to your need. Learning VBA can be an entry point into VBA programming, finance, data analytics, and related fields. LBSTI GAUTAM NAGAR 3 # HarshSir 7503412537

4. Applications of VBA. VBA in excel as MS-excel itself provides loads of inbuilt functions. MS-excel provides only essential inbuilt functions that may not be sufficient to perform complex calculations. Under such circumstances, VBA becomes the most powerful solution. What all you can do with excel VBA? You can create custom function or UDF (user defined function). You can create a custom A ddin . You can repeat a set of instruction multiple times. Prerequisite It is desirable if you know the basic of Excel and how the function in Excel Work. LBSTI GAUTAM NAGAR 4 # HarshSir 7503412537

5. What is VBA used for in Excel? VBA is used to write macros, which automate simple and complex tasks in Excel. Are VB and VBA the same? VB is a programming language that allow you to create standalone executable applications and do not require Office or Excel loaded into computers. VBA is a programming language also that cannot create standalone applications, and it exists within a host application such as Excel. VBE (Visual Basic Editor)? The Visual Basic Editor is a user friendly development environment. It is the place where you keep the VB code. LBSTI GAUTAM NAGAR 5 # HarshSir 7503412537

6. What is the difference between a macro and VBA? VBA is the programming language to create Macros while Macros are programming codes that run on Excel environment to perform automatic routine tasks. What is the difference between a macro and script? Macros are stored inside excel workbook files and can only be run from inside Excel, It is used to automate various Excel functions and add extra functionality to workbooks. Scripts are stored in text files using the, VBS extension and can be run from inside Windows or executed by other program, It is used to automate certain administrative tasks on a computer or network. LBSTI GAUTAM NAGAR 6 # HarshSir 7503412537

7. Why Macros is required? To do repetitive task. To develop new formulas Synonyms of Macros Subroutine Program Code Procedure What does a VBA Developer do? People who create macros aren’t developers- they’re analysts. People who focus on VBA development are able to create Add-ins, user interfaces with forms, interactive dashboards, report generators, and more. LBSTI GAUTAM NAGAR 7 # HarshSir 7503412537

8. IS VBA Object-Oriented? Visual basic for applications (VBA) is an object-oriented programming language for writing macros. How to Record a Macro 1. a . Click on the record macro button at the bottom left hand side b . Use Shortcut Alt + T + M + R c . Go to Developer Tab Record Macro d . Go to view tab-Record Macro 2 . Name your macro, assign a shortcut key & decide where to store it ( Existing Workbook , New Workbook or Personal macro workbook) 3 . Run through the steps the macro needs to do. 4 . Stop the Macro LBSTI GAUTAM NAGAR 8 # HarshSir 7503412537

9. Recording Your First Macro Create a macro that: (for ex-) Inserts a new worksheet Writes my first macro in A1 – increase font size to 20 and makes bold. Apply all border VBE (Visual Basic Editor) The visual basic editor is not exactly the same as excel. It is actually a separate application , even though you’ll usually open it through excel. In fact, in order for the VBE to be able to run, excel must be open . The main function of the VBE is to allow you to write and edit VBA code . The visual basic editor is sometimes referred to as the integrated development environment (IDE). LBSTI GAUTAM NAGAR 9 # HarshSir 7503412537

10. Opening the VB Editor Using a keyboard shortcut key alt + F11 (easiest and fastest) Using a developer tab. Using a worksheet. LBSTI GAUTAM NAGAR 10 # HarshSir 7503412537

11. VBE Windows Panel:- Project Explorer – Shows a hierarchical list of objects (workbook and worksheet code modules), Modules (standard), Forms (code and object), and Class Modules. Properties Windows – list of properties for the selected object, either alphabet or categorized. Code Window – Write, edit, insert, or delete code. Immediate Window – run one line code statements ( eg – Activecell.value =123 [Enter]), repeat individual statement – click in the statement and press [Enter], view code output from a module with the Debug, Print Statement Locals Window – monitor values and properties of active elements. Use Break and/or Step mode. Watch Window – monitor the values of selected module elements. LBSTI GAUTAM NAGAR 11 # HarshSir 7503412537