|
|||
|
|
|
|
|
|
VBA for Excel (Macros)VBA stands for Visual Basic for Applications. It is the simple programming language that can be used within Excel to develop macros and complex programs: 10 Lessons on the VBA Programming Tools 13 Lessons on Excel Macros Vocabulary (VBA) 10 Lessons on Excel VBA Userforms (GUI) Section 1: Building macros in ExcelLesson 1: Visual Basic Editor (VBE) in Excel The most user friendly programming environment. You will create your procedures (macros) and userforms in the VBE. You will modify and test these components and even make changes while you are testing your procedures step by step. Lesson 2: The Project Window in the Visual Basic Editor of Excel Where you see all the open workbooks, where you add modules and userforms Lesson 3: The Properties Window in the Visual Basic Editor of Excel The properties of the workbooks, the names of the modules and the properties of all the components of the userforms. Lesson 4: The Code Window in the Visual Basic Editor of Excel Where you write and test all your code Lesson 5: Developing Macros in the VBE You are becoming so good that you don't need the macro recorder sometimes. Here is how you create a new VBA procedure from scratch. Lesson 6: Testing Macros in the VBE Split your screen, use the F8* key and see what you procedure does in Excel step by step. Back up, correct and re-test. Lesson 7: Excel Macro Recorder You can use the recorder to develop macros rapidly. If you want to do more the Macro Recorder is the best teacher and will be a great assistant even when you become an expert at programming in VBA. It WRITES the code for you. In this chapter you will learn about and try the Macro Recorder. You will run the macro that you have recorded. Lesson 8: Modifying Macros in the VBE The macro recorder is a great helper and teacher but it sometimes makes it complicated. Here is a recorded macro simplified. Lesson 9: VBA Security and Protection Protect you code, your sheets and your computer. For a procedure to start it must be triggered, you click on a button, you close a workbook, the value of a cell changes. Find the right event.
Section 2: Words and Sentences to Build Macros in ExcelLesson 11: Code in VBA for Excel A few tips to make things easier when you start coding. Lesson 12: Working with the Application The Application is EXCEL ITSELF. Master 15 new VBA words: Application, Calculation, Cursor, CutCopyMode, Dialogs, DisplayAlerts, EnableEvents, GoTo, Quit, ScreenUpdating, Timer, ChDir, ChDrive Lesson 13: Working with the Workbooks Working with one workbook, with THISWORKBOOK, with many workbooks and even with all the workbooks in a directory. Lesson 14: Working with the Worksheets Moving from one to the other, copy/paste from one to the other, adding and deleting worksheets and even doing something on all of them. Leeson 15: Moving Around the Worksheet Improve your VBA vocabulary with 40 more words to work within the worksheet: Lesson 16: Working with Message and Input Boxes Interacting with the user with minimal effort. Lesson 17: Working with Excel Database and Filters Most of your data in the form of a database. Learn how to sort and filter your data. Lesson 18: Dealing with Errors VBA tells you when the code is wrong but what if the logic is wrong or what if the user gives a wrong answer. Lesson 19: Working with Variables When you don't want to hard code things and when the value of something changes put it in a variable. Discover the VARIANT type variable that allows you to execute 1,000,000 calculations in less than 5 seconds. Lesson 20: Working with Statements They are the KILL, the IF_THEN_ELSE_END, the SELECT-CASE, the DO_LOOP, the FOR_NEXT.... Lesson 21: Working with Functions Creating new Excel functions, using the Excel functions in VBA and a few VBA functions. Lesson 22: Working with external data and SQL When you connect to outside sources of data (large databases, text files, other Excel workbooks, Access, etc.) the computer is using SQL (Structured Query Language) a specialized language to work with data. You can use Excel's functionalities to connect and extract data but you can also use directly the SQL language. It is much faster. Lesson 23: Working with Windows and other Microsoft Programs FROM Excel With VBA for Excel you can develop VBA procedures (macros) to work within in Excel while calling other Microsoft programs like Access, Notepad, Word, Project and even Windows
Section 3:UserForms and Controls (Command Buttons, Combo Boxes...) in ExcelLesson 24: Forms (Userforms) in VBA for Excel To require information from the user or tell him something you have used message boxes and input boxes. When these can't do the job anymore you need to develop useforms. Lesson 25: Userforms Properties and VBA Code Then you set the properties including the important ones "On Activate" and "On Close" Lesson 26: Properties and VBA code for Command Buttons The "CLICK " control where most of the code resides in a userform Lesson 27: Properties and VBA code for Labels The user just sees it and cannot submit any information with it. YOU are talking to the user Lesson 28: Properties and VBA code for Text Boxes The user is now talking to you. I have rarely seen a userform without text boxes. The format is the challenge. Lesson 29: Properties and VBA code for Combo Boxes The BIG control. It is a drop-Down list. Lesson 30: Properties and VBA code for List Boxes When multiple choice is a necessity Lesson 31: Properties and VBA code for Check Boxes, Option Buttons and Frames The "True/False" controls to be used as a group with a frame Lesson 32: Properties and VBA code for Spin Buttons They are sexier than text boxes and sometimes make a difference in the clients' opinion of your talent. Lesson 33: Excel VBA Controls' Properties and VBA Code They are the controls to build catalogs and shopping carts.
|
|
|
|
|
