|
所在平台: Udemy |
课程主页: https://www.udemy.com/course/excel-vba-programming-by-examples/
课程评论：没有评论
课程名称：Excel VBA Bootcamp：VBA 编程与实用练习概述 课程概述： 本课程旨在帮助学员通过示范和逐步讲解，掌握 VBA 的使用。学员将学习 Excel VBA 的语法、表单和控件的用法，以及如何通过多个实例了解 VBA 在自动化方面的应用。 课程内容： 1. **基础入门**： - 学习如何使用 Excel 宏录制而不需要编程。 - 理解什么是 Excel 宏，何时使用。 - 如何录制宏、创建快捷键、运行录制的宏、录制相对宏，并了解其与一般宏的区别。 - 学习如何删除宏。 2. **VBA 开发环境**： - 如何进入 VBA 窗口及其组件的用途。 - 使用断点、属性窗口和编辑工具的技巧。 3. **VBA 语法**： - 定义和使用变量、编写 for 循环、在不同工作表中显示输出。 - 用户输入的获取与处理、记录宏以学习语法。 - 了解 If、If Else 和 Do While 循环的用法。 4. **实用练习**： - **练习 01**：使用表单进行用户输入和图表展示。 - **练习 02**：从 MS Access 获取数据并在 Excel 中处理。 - **练习 03**：为每个产品或代理创建单独的工作表。 - **练习 04**：使用 VBA 进行库存管理及客户沟通。 - **练习 05**：读取 Access 数据并写入文本文件。 - **练习 06**：在 Excel VBA 中设计调查表单。 - **练习 07**：插入 Excel VBA 表单数据到 MS Access 数据库。 - **练习 08**：使用数据透视表、VLOOKUP 等公式进行抽样分析。 - **练习 09**：基于 Windows 的用户身份验证及执行语音通知。 - **练习 10**：通过 VBA 执行 SQL 命令。 - **练习 11**：创建语音提醒工具，定时提醒用户。 - **练习 12**：通过 Windows 计划任务自动化运行 VBA 代码。 - **练习 13**：递归获取文件夹内文件及文件大小。 - **练习 14**：将文件夹内的所有相同数据布局文件合并。 通过本课程，学员将掌握 Excel VBA 的核心知识及实用技能，为日常工作中的数据处理和任务自动化打下坚实的基础。
Understand what you are going to achieve through the VBA by seeing demo and then see step by step explanation of VBA code. Learn about Excel VBA syntax, Excel VBA form and control, methods of using forms and controls, several workout examples to see usage of Excel VBA for automation.In this course, you will learn following stuff in step by step mannerLevel 01 - start without coding - Automate tasks using Excel Macro recording Demo of an excel macro What is excel macro When to use it How to record a macro/create a shortcut action How to run a recorded macro How to create a shortcut for a macro action How to run a recorded macro on a new dataset (excel workbook) How to record a relative macro What is the difference between a relative macro and a general macro. How to understand what was recorded as macro. How to delete a macro Level 02 A - Understand Excel VBA integrated development environmentHow to reach VBA window What are different component of the window What is use of those components How to use breakpoint, properties window, edit tools etc. Level 02 B - Understand Excel VBA syntax How to define a variable Different types of variables How to write a for loop How to display output in an interactive way How to write output in a different worksheet How to take user input through a prompt How to use user input How to use record macro to know VBA syntax How to use breakpoint How to run macro through click of a button When you need to write do while / do until loop Syntax of do while / until loop How to take input from excel sheet for program execution How to ensure variable names are correct before execution of program If else command, If elseif else command Using mod function (for remainder) Showing status bar Workout Examples 01 - Using Forms for user entry, chart display etc. See a worked out example of a VBA form Learn about various control, design aspects of Excel VBA form Learn about why will need form, and such controls Hide Data sheet and format other sheet to make it look professional Ensure proper data type Ensure value selection from combo box only Learn to define level, text, combobox and button command Learn to pass dropdown data in combobox Learn to use form entry into VBA Learn to write back on Excel form Learn how to load form while getting excel started Learn to change properties of control through VBA Workout Examples 02 - fetching data from MS Access using Excel VBA How to use Excel as front end and fetch data from microsoft Access database Where to use this à Greatly useful when many users have Excel but don't have MS access database in the PC Where Reference is needed Watch window - how to use it How to edit the code for many fields and different databases Workout Examples 03 - One sheet per product or agent How to use do while loop to let it run for as many records as it has got How to find block size (starting and ending row for each product) How to add sheet using VBA and give it a name How to ensure that the tool remains intact with multiple runs and even a mistake can't cause issue How to repeat header in each tab or worksheet Workout Examples 04 - Inventory management, coupon assignment and customer communication using Excel VBA Traverse through various sheets and workbooks using VBA Formatting date Writing derived information from one sheet to another Passing several parameters to VBA for conditional traversal 3 should mean three coupons to get reserved [Coupon code: Validity] will need comma if there are multiple vouchers Error handling: alert, if there are no coupons Protecting Excel tool for further usage Workout Examples 05 - Reading data from a microsoft access database and writing it into a text fileReading Microsoft Access data using VBAdirectly writing output into a text fileMaking the output comma separated Workout Example 06 - Designing survey form in Excel VBA with option buttons / list box etc.Workout Example 07 - Insert Excel VBA form data in MS Access database Workout Example 08 - Using pivot table, vlookup and several other formula for a sampling work Workout Example 09 - Windows based user authentication and Voice notification of execution of VBAWorkout Example 10 - Running SQL command through VBA. Read Excel sheet as a table inside VBAYou will learnReading complete worksheet data into a tableManaging named as well as unnamed headersRunning SQL command inside VBALittle advance SQL coding as well (by product)A-BB-AWorkout Example 11 - Voice based reminder tool You will learn how to remind speaker about how much time is left through automated announcement. Workout Example 12 - Running VBA code in automated mannerYou will learn what is takes to used Windows scheduler to schedule the VBA code to run in automated manner with desired frequency. Workout Example 13 - Recursive file and folder size within a folderMany a times we need a tool, which should recursively put file names and file sizes within a folder.I mean to say that, if you pass name of a folder and say in that folder, it has11 files2 foldersThen it should again go into the 2 folders and if it finds a folder again say in any of the folder, it should go within that folder too and should create result, with name and size of all the files & folders. Workout Example 14 - Combine all the files inside a folder through VBATasks1. Combine Multiple files - having same layout (same number of fields and fields are always in the same column) but not neessarily same number of rows2. Also put file name as first column in the final data setAssumption:All the data files are in the same folderThere is nothing other than data files in that folderUsually data files should have a way to know, which file was referredAll the data files are excel files only
