精华内容
下载资源
问答
  • Excel VBA教程

    2012-02-29 09:44:43
    Excel VBA教程 教你怎么在excel里使用VBA编程
  • Excel VBA 教程

    2018-12-17 19:47:00
    https://www.w3cschool.cn/excelvba/Excel VBA 编程教程 https://www.yiibai.com/vba VBA教程 http://www.accessoft.com/article-show.asp?id=16502 关于VBA的0、""(空字符串)、Null、Empty、与 Nothing 的...

    https://www.w3cschool.cn/excelvba/  Excel VBA 编程教程

     

    https://www.yiibai.com/vba   VBA教程

     

    http://www.accessoft.com/article-show.asp?id=16502   关于VBA的0、""(空字符串)、Null、Empty、与 Nothing 的区别

     

    转载于:https://www.cnblogs.com/onelikeone/p/10133438.html

    展开全文
  • excelVBA教程

    2015-05-02 16:19:50
    vba是数据开发的基础,但是很多新手苦于找不相关的学习教程vba最基础学习教程,适合初学者。
  • excel VBA 教程

    2014-05-13 09:13:55
    是现有的一些VBA开发软件,可以提供大家直接运用和 学习,是一款比较好的软件
  • Excel vba教程

    2011-03-24 22:59:23
    关于Excel VBA基本编程,挺实用的,从最基本的开始,step by step
  • excel vba教程

    2013-04-20 13:23:53
    一、VBA 语言基础...................................................................................................................1 第一节标识符..........................................................
  • EXCEL VBA教程

    2010-04-21 22:33:54
    VBA(Visual Basic for Applications)是Microsoft Office 系列的内置编程语言,是非常流行的应用程序开发语言VB(Visual Basic) 的子集。它“寄生于”Office应用程序,是Office XP套装软件的一个十分重要的组件。它...
  • Excel VBA教程完全版

    2013-11-29 21:02:01
    Excel VBA教程完全版,深入浅出的介绍了Excel VBA的特点、功能和技巧,是excel进阶学习的最佳帮手,该教程内容全面,结构清晰,是一部学习Excel VBA不可多得的好教材。
  • EXCEL VBA教程完全版

    2013-04-17 11:16:53
    EXCEL VBA教程完全版 VBA语言基础 VBA程序设计
  • EXCEL VBA 教程 完全版

    2011-02-25 05:30:21
    EXCEL VBA 教程,适合入门者,自学者,很详细
  • excel vba教程完整版

    2015-10-30 18:17:12
    关于VBA中很好的书籍。从基础到提高逐步学习。
  • Excel VBA教程资料

    2009-07-06 20:29:53
    Excel中使用vba教程资料,RAR压缩,需要的赶紧下。希望对大家有所帮助,抛砖引玉,还望多多交流。谢谢!
  • EXCelVBA教程,汇集了EXCel所有宏函数的详细讲解、编程技巧、统计分析技巧等大量信息,这可是让你成为EXCEL专家级最好的资料,看后保证你要万分感谢我,给我加分!!!!给我评价!!!
  • 介绍 (Introduction) This is a tutorial about writing code in Excel spreadsheets using Visual ... 这是一个有关使用Visual Basic for Applications(VBA)在Excel电子表格中编写代码的教程Excel is one of...

    介绍 (Introduction)

    This is a tutorial about writing code in Excel spreadsheets using Visual Basic for Applications (VBA).

    这是一个有关使用Visual Basic for Applications(VBA)在Excel电子表格中编写代码的教程。

    Excel is one of Microsoft’s most popular products. In 2016, the CEO of Microsoft said  "Think about a world without Excel. That's just impossible for me.” Well, maybe the world can’t think without Excel.

    Excel是Microsoft最受欢迎的产品之一。 2016年,微软首席执行官说:“想想没有Excel的世界。对我而言,这简直是不可能的。” 好吧,也许没有Excel,世界将无法思考。

    • In 1996, there were over 30 million users of Microsoft Excel (source).

      在1996年,有超过3000万的Microsoft Excel用户( 来源 )。

    • Today, there are an estimated 750 million users of Microsoft Excel. That’s a little more than the population of Europe and 25x more users than there were in 1996.

      如今,估计有7.5亿Microsoft Excel用户。 这比欧洲人口多一点,用户比1996年多25倍。

    We’re one big happy family!

    我们是一个幸福的大家庭!

    In this tutorial, you’ll learn about VBA and how to write code in an Excel spreadsheet using Visual Basic.

    在本教程中,您将了解VBA以及如何使用Visual Basic在Excel电子表格中编写代码。

    先决条件 (Prerequisites)

    You don’t need any prior programming experience to understand this tutorial. However, you will need:

    您不需要任何编程经验即可了解本教程。 但是,您将需要:

    • Basic to intermediate familiarity with Microsoft Excel

      基本到中等熟悉Microsoft Excel
    • If you want to follow along with the VBA examples in this article, you will need access to Microsoft Excel, preferably the latest version (2019) but Excel 2016 and Excel 2013 will work just fine.

      如果要遵循本文中的VBA示例,则需要访问Microsoft Excel,最好是最新版本(2019),但Excel 2016和Excel 2013可以正常工作。
    • A willingness to try new things

      愿意尝试新事物

    学习目标 (Learning Objectives)

    Over the course of this article, you will learn:

    在本文中,您将学到:

    1. What VBA is

      什么是VBA
    2. Why you would use VBA

      为什么要使用VBA
    3. How to get set up in Excel to write VBA

      如何在Excel中进行设置以编写VBA
    4. How to solve some real-world problems with VBA

      如何使用VBA解决一些实际问题

    重要概念 (Important Concepts)

    Here are some important concepts that you should be familiar with to fully understand this tutorial.

    这里是一些重要概念,您应该熟悉这些概念才能完全理解本教程。

    Objects: Excel is object-oriented, which means everything is an object - the Excel window, the workbook, a sheet, a chart, a cell. VBA allows users to manipulate and perform actions with objects in Excel.

    对象 :Excel是面向对象的,这意味着一切都是对象-Excel窗口,工作簿,工作表,图表,单元格。 VBA允许用户对Excel中的对象进行操作和执行操作。

    If you don’t have any experience with object-oriented programming and this is a brand new concept, take a second to let that sink in!

    如果您没有任何面向对象编程的经验,而这是一个全新的概念,请花一点时间让它沉入其中!

    Procedures: a procedure is a chunk of VBA code, written in the Visual Basic Editor, that accomplishes a task. Sometimes, this is also referred to as a macro (more on macros below). There are two types of procedures:

    过程 :过程是用Visual Basic编辑器编写的,完成任务的VBA代码的一部分。 有时,这也称为宏(下面的更多宏)。 有两种类型的过程:

    • Subroutines: a group of VBA statements that performs one or more actions

      子例程:一组执行一个或多个动作的VBA语句
    • Functions: a group of VBA statements that performs one or more actions and returns one or more values

      函数:一组执行一个或多个动作并返回一个或多个值的VBA语句

    Note: you can have functions operating inside of subroutines. You’ll see later.

    注意:可以在子例程内部运行函数。 待会见。

    Macros: If you’ve spent any time learning more advanced Excel functionality, you’ve probably encountered the concept of a “macro.” Excel users can record macros, consisting of user commands/keystrokes/clicks, and play them back at lightning speed to accomplish repetitive tasks. Recorded macros generate VBA code, which you can then examine. It’s actually quite fun to record a simple macro and then look at the VBA code.

    :如果您花了很多时间学习更高级的Excel功能,则可能遇到了“宏”的概念。 Excel用户可以记录由用户命令/按键/单击组成的宏,并以闪电般的速度播放它们以完成重复的任务。 录制的宏会生成VBA代码,然后您可以对其进行检查。 录制一个简单的宏,然后查看VBA代码,实际上非常有趣。

    Please keep in mind that sometimes it may be easier and faster to record a macro rather than hand-code a VBA procedure.

    请记住,有时录制宏可能比手动编码VBA过程更容易,更快。

    For example, maybe you work in project management. Once a week, you have to turn a raw exported report from your project management system into a beautifully formatted, clean report for leadership. You need to format the names of the over-budget projects in bold red text. You could record the formatting changes as a macro and run that whenever you need to make the change.

    例如,也许您从事项目管理。 每周一次,您必须将来自项目管理系统的原始导出报告转换为格式精美,干净的报告,以进行领导。 您需要将预算外项目的名称设置为红色粗体。 您可以将格式更改记录为宏,并在需要进行更改时运行。

    什么是VBA? (What is VBA?)

    Visual Basic for Applications is a programming language developed by Microsoft. Each software program in the Microsoft Office suite is bundled with the VBA language at no extra cost. VBA allows Microsoft Office users to create small programs that operate within Microsoft Office software programs.

    Visual Basic for Applications是Microsoft开发的一种编程语言。 Microsoft Office套件中的每个软件程序都与VBA语言捆绑在一起,无需额外费用。 VBA允许Microsoft Office用户创建在Microsoft Office软件程序中运行的小程序。

    Think of VBA like a pizza oven within a restaurant. Excel is the restaurant. The kitchen comes with standard commercial appliances, like large refrigerators, stoves, and regular ole’ ovens - those are all of Excel’s standard features.

    可以将VBA视为餐厅中的披萨烤箱。 Excel是餐厅。 厨房配有标准的商用设备,例如大型冰箱,火炉和普通的烤箱-这些都是Excel的标准功能。

    But what if you want to make wood-fired pizza? Can’t do that in a standard commercial baking oven. VBA is the pizza oven.

    但是,如果您想做木柴比萨怎么办? 在标准的商用烤箱中无法做到这一点。 VBA是披萨烤箱。

    Yum.

    好吃

    为什么要在Excel中使用VBA? (Why use VBA in Excel?)

    Because wood-fired pizza is the best!

    因为木比萨饼是最好的!

    But seriously.

    不过实话说。

    A lot of people spend a lot of time in Excel as a part of their jobs. Time in Excel moves differently, too. Depending on the circumstances, 10 minutes in Excel can feel like eternity if you’re not able to do what you need, or 10 hours can go by very quickly if everything is going great. Which is when you should ask yourself, why on earth am I spending 10 hours in Excel?

    很多人在工作中花费大量时间在Excel中。 Excel中的时间也不同。 根据情况的不同,如果您无法完成所需的工作,则在Excel中10分钟可能感觉很长久,而如果一切顺利,则很快就会花费10个小时。 什么时候应该问自己, 到底为什么要在Excel中花费10个小时?

    Sometimes, those days are inevitable. But if you’re spending 8-10 hours everyday in Excel doing repetitive tasks, repeating a lot of the same processes, trying to clean up after other users of the file, or even updating other files after changes are made to the Excel file, a VBA procedure just might be the solution for you.

    有时,那些日子是不可避免的。 但是,如果您每天在Excel中花费8-10个小时来执行重复的任务,重复很多相同的过程,尝试在文件的其他用户之后进行清理,甚至在对Excel文件进​​行更改后甚至更新其他文件, VBA程序可能只是您的解决方案。

    You should consider using VBA if you need to:

    如果需要,您应该考虑使用VBA:

    • Automate repetitive tasks

      自动执行重复性任务
    • Create easy ways for users to interact with your spreadsheets

      为用户创建与电子表格进行交互的简便方法
    • Manipulate large amounts of data

      处理大量数据

    进行设置以在Excel中编写VBA (Getting Set Up to Write VBA in Excel)

    开发人员标签 (Developer Tab)

    To write VBA, you’ll need to add the Developer tab to the ribbon, so you’ll see the ribbon like this.

    要编写VBA,您需要在功能区中添加“开发人员”选项卡,这样您将看到功能区。

    To add the Developer tab to the ribbon:

    要将“开发人员”选项卡添加到功能区:

    1. On the File tab, go to Options > Customize Ribbon.

      在文件选项卡上,转到选项>自定义功能区。
    2. Under Customize the Ribbon and under Main Tabs, select the Developer check box.

      在“自定义功能区”下和“主选项卡”下,选中“开发人员”复选框。

    After you show the tab, the Developer tab stays visible, unless you clear the check box or have to reinstall Excel. For more information, see Microsoft help documentation.

    显示选项卡后,“开发人员”选项卡将保持可见状态,除非您清除复选框或必须重新安装Excel。 有关更多信息,请参见Microsoft帮助文档。

    VBA编辑器 (VBA Editor)

    Navigate to the Developer Tab, and click the Visual Basic button. A new window will pop up - this is the Visual Basic Editor. For the purposes of this tutorial, you just need to be familiar with the Project Explorer pane and the Property Properties pane.

    导航到“开发人员”选项卡,然后单击“ Visual Basic”按钮。 将会弹出一个新窗口-这是Visual Basic编辑器。 出于本教程的目的,您只需要熟悉Project Explorer窗格和Property Properties窗格。

    Excel VBA示例 (Excel VBA Examples)

    First, let’s create a file for us to play around in.

    首先,让我们创建一个文件供我们玩。

    1. Open a new Excel file

      打开一个新的Excel文件
    2. Save it as a macro-enabled workbook (. xlsm)

      将其另存为启用宏的工作簿(.xlsm)
    3. Select the Developer tab

      选择开发者标签
    4. Open the VBA Editor

      打开VBA编辑器

    Let’s rock and roll with some easy examples to get you writing code in a spreadsheet using Visual Basic.

    让我们摇摆一些简单的示例,让您使用Visual Basic在电子表格中编写代码。

    示例1:用户打开Excel工作簿时显示一条消息 (Example 1: Display a Message when Users Open the Excel Workbook)

    In the VBA Editor, select Insert -> New Module

    在VBA编辑器中,选择插入->新建模块

    Write this code in the Module window (don’t paste!):

    在“模块”窗口中编写以下代码(不要粘贴!):

    Sub Auto_Open() MsgBox ("Welcome to the XYZ Workbook.") End Sub

    Sub Auto_Open()MsgBox(“欢迎使用XYZ工作簿。”)End Sub

    Save, close the workbook, and reopen the workbook. This dialog should display.

    保存,关闭工作簿,然后重新打开工作簿。 该对话框应显示。

    Ta da!

    da!

    怎么样了 (How is it doing that?)

    Depending on your familiarity with programming, you may have some guesses. It’s not particularly complex, but there’s quite a lot going on:

    根据您对编程的熟悉程度,您可能会有一些猜测。 它不是特别复杂,但是有很多事情要做:

    • Sub (short for “Subroutine): remember from the beginning, “a group of VBA statements that performs one or more actions.”

      Sub(“ Subroutine”的缩写):从一开始就记住“一组执行一个或多个动作的VBA语句。”
    • Auto_Open: this is the specific subroutine. It automatically runs your code when the Excel file opens - this is the event that triggers the procedure. Auto_Open will only run when the workbook is opened manually; it will not run if the workbook is opened via code from another workbook (Workbook_Open will do that, learn more about the difference between the two).

      Auto_Open:这是特定的子例程。 当Excel文件打开时,它将自动运行您的代码-这是触发过程的事件。 Auto_Open仅在手动打开工作簿时运行; 如果通过另一个工作簿中的代码打开工作簿,它将无法运行(Workbook_Open将这样做,请详细了解两者之间的区别 )。

    • By default, a subroutine’s access is public. This means any other module can use this subroutine. All examples in this tutorial will be public subroutines. If needed, you can declare subroutines as private. This may be needed in some situations. Learn more about subroutine access modifiers.

      默认情况下,子例程的访问是公共的。 这意味着任何其他模块都可以使用此子例程。 本教程中的所有示例都是公共子例程。 如果需要,可以将子例程声明为私有。 在某些情况下可能需要这样做。 了解有关子例程访问修饰符的更多信息。

    • msgBox: this is a function - a group of VBA statements that performs one or more actions and returns a value. The returned value is the message “Welcome to the XYZ Workbook.”

      msgBox:这是一个函数-一组VBA语句,它们执行一个或多个操作并返回一个值。 返回的值是消息“欢迎使用XYZ工作簿”。

    In short, this is a simple subroutine that contains a function.

    简而言之,这是一个包含函数的简单子例程。

    我什么时候可以使用? (When could I use this?)

    Maybe you have a very important file that is accessed infrequently (say, once a quarter), but automatically updated daily by another VBA procedure. When it is accessed, it’s by many people in multiple departments, all across the company.

    也许您有一个非常重要的文件,该文件很少被访问(例如,每季度一次),但又由另一个VBA过程每天自动更新。 当它被访问时,整个公司中多个部门的许多人都可以访问它。

    • Problem: Most of the time when users access the file, they are confused about the purpose of this file (why it exists), how it is updated so often, who maintains it, and how they should interact with it. New hires always have tons of questions, and you have to field these questions over and over and over again.

      问题:大多数情况下,用户访问文件时,他们会对该文件的用途(为什么存在),更新频率如此频繁,由谁来维护文件以及如何与之交互感到困惑。 新员工总是有很多问题,您必须一遍又一遍地提出这些问题。
    • Solution: create a user message that contains a concise answer to each of these frequently answered questions.

      解决方案:创建一个用户消息,其中包含对这些常见问题的简要解答。

    现实世界的例子 (Real World Examples)

    • Use the MsgBox function to display a message when there is any event: user closes an Excel workbook, user prints, a new sheet is added to the workbook, etc.

      发生任何事件时,使用MsgBox函数显示消息:用户关闭Excel工作簿,用户打印,将新工作表添加到工作簿等。
    • Use the MsgBox function to display a message when a user needs to fulfill a condition before closing an Excel workbook

      当用户需要在关闭Excel工作簿之前满足条件时,使用MsgBox函数显示消息
    • Use the InputBox function to get information from the user

      使用InputBox函数从用户那里获取信息

    示例2:允许用户执行另一个过程 (Example 2: Allow User to Execute another Procedure)

    In the VBA Editor, select Insert -> New Module

    在VBA编辑器中,选择插入->新建模块

    Write this code in the Module window (don’t paste!):

    在“模块”窗口中编写以下代码(不要粘贴!):

    Sub UserReportQuery() Dim UserInput As Long Dim Answer As Integer UserInput = vbYesNo Answer = MsgBox("Process the XYZ Report?", UserInput) If Answer = vbYes Then ProcessReport End Sub

    子UserReportQuery()将UserInput设为长暗响应作为整数UserInput = vbYesNo答案= MsgBox(“处理XYZ报告?”,UserInput)如果Answer = vbYes,则ProcessReport结束Sub

    Sub ProcessReport() MsgBox ("Thanks for processing the XYZ Report.") End Sub

    Sub ProcessReport()MsgBox(“感谢您处理XYZ报告。”)

    Save and navigate back to the Developer tab of Excel and select the “Button” option. Click on a cell and assign the UserReportQuery macro to the button.

    保存并导航回到Excel的“开发人员”选项卡,然后选择“按钮”选项。 单击一个单元格,然后将UserReportQuery宏分配给该按钮。

    Now click the button. This message should display:

    现在单击按钮。 此消息应显示:

    Click “yes” or hit Enter.

    单击“是”或按Enter。

    Once again, tada!

    再次,多田!

    Please note that the secondary subroutine, ProcessReport, could be anything. I’ll demonstrate more possibilities in example #3. But first...

    请注意,辅助子例程ProcessReport可以是任何东西 。 我将在示例3中演示更多可能性。 但首先...

    怎么样了 (How is it doing that?)

    This example builds on the previous example and has quite a few new elements. Let’s go over the new stuff:

    这个例子建立在前面的例子的基础上,并具有许多新元素。 让我们来看看新的东西:

    • Dim UserInput As Long: Dim is short for “dimension” and allows you to declare variable names. In this case, UserInput is the variable name and Long is the data type. In plain English, this line means “Here’s a variable called “UserInput”, and it’s a Long variable type.”

      Dim UserInput As Long:Dim是“ dimension”的缩写,允许您声明变量名称。 在这种情况下,UserInput是变量名,Long是数据类型。 用简单的英语来说,此行的意思是“这里是一个名为“ UserInput”的变量,它是一个Long变量类型​​。”
    • Dim Answer As Integer: declares another variable called “Answer,” with a data type of Integer. Learn more about data types here.

      Dim Answer As Integer:声明另一个名为“ Answer”的变量,其数据类型为Integer。 在此处了解有关数据类型的更多信息。

    • UserInput = vbYesNo: assigns a value to the variable. In this case, vbYesNo, which displays Yes and No buttons. There are many button types, learn more here.

      UserInput = vbYesNo:为变量分配一个值。 在这种情况下,vbYesNo将显示“是”和“否”按钮。 按钮类型很多在此处了解更多信息

    • Answer = MsgBox(“Process the XYZ Report?”, UserInput): assigns the value of the variable Answer to be a MsgBox function and the UserInput variable. Yes, a variable within a variable.

      Answer = MsgBox(“处理XYZ报告?”,UserInput):将变量Answer的值分配为MsgBox函数和UserInput变量。 是的,变量中的变量。
    • If Answer = vbYes Then ProcessReport: this is an “If statement,” a conditional statement, which allows us to say if x is true, then do y. In this case, if the user has selected “Yes,” then execute the ProcessReport subroutine.

      如果Answer = vbYes然后ProcessReport:这是一个“ If语句”,是一个条件语句,它使我们可以说x如果为true,则执行y。 在这种情况下,如果用户选择“是”,则执行ProcessReport子例程。

    我什么时候可以使用? (When could I use this?)

    This could be used in many, many ways. The value and versatility of this functionality is more so defined by what the secondary subroutine does.

    可以以多种方式使用它。 此功能的价值和多功能性由辅助子例程的作用来定义。

    For example, maybe you have a file that is used to generate 3 different weekly reports. These reports are formatted in dramatically different ways.

    例如,也许您有一个用于生成3个不同的每周报告的文件。 这些报告的格式截然不同。

    • Problem: Each time one of these reports needs to be generated, a user opens the file and changes formatting and charts; so on and so forth. This file is being edited extensively at least 3 times per week, and it takes at least 30 minutes each time it’s edited.

      问题:每次需要生成这些报告之一时,用户将打开文件并更改格式和图表。 等等等等。 每周至少要对该文件进行3次广泛的编辑,每次编辑至少需要30分钟。
    • Solution: create 1 button per report type, which automatically reformats the necessary components of the reports and generates the necessary charts.

      解决方案:为每种报告类型创建1个按钮,该按钮将自动重新格式化报告的必要组成部分并生成必要的图表。

    现实世界的例子 (Real World Examples)

    • Create a dialog box for user to automatically populate certain information across multiple sheets

      创建一个对话框供用户自动在多张纸上填充某些信息
    • Use the InputBox function to get information from the user, which is then populated across multiple sheets

      使用InputBox函数从用户那里获取信息,然后将其填充到多个工作表中

    示例3:使用下一个循环将数字添加到范围 (Example 3: Add Numbers to a Range with a For-Next Loop)

    For loops are very useful if you need to perform repetitive tasks on a specific range of values - arrays or cell ranges. In plain English, a loop says “for each x, do y.”

    如果需要对特定值范围(数组或单元格范围)执行重复性任务,则for循环非常有用。 用简单的英语来说,一个循环说“对于每个x,做y”。

    In the VBA Editor, select Insert -> New Module

    在VBA编辑器中,选择插入->新建模块

    Write this code in the Module window (don’t paste!):

    在“模块”窗口中编写以下代码(不要粘贴!):

    Sub LoopExample() Dim X As Integer For X = 1 To 100 Range("A" & X).Value = X Next X End Sub

    Sub LoopExample()Dim X作为X的整数,范围为1到100(“ A”和X)。值​​= X下一个X结束Sub

    Save and navigate back to the Developer tab of Excel and select the Macros button. Run the LoopExample macro.

    保存并导航回到Excel的“开发人员”选项卡,然后选择“宏”按钮。 运行LoopExample宏。

    This should happen:

    这应该发生:

    Etc, until the 100th row.

    等等,直到第100行。

    怎么样了? (How is it doing that?)

    • Dim X As Integer: declares the variable X as a data type of Integer.

      Dim X As Integer:将变量X声明为Integer的数据类型。
    • For X = 1 To 100: this is the start of the For loop. Simply put, it tells the loop to keep repeating until X = 100. X is the counter. The loop will keep executing until X = 100, execute one last time, and then stop.

      对于X = 1到100:这是For循环的开始。 简而言之,它告诉循环继续重复直到X =100。X是counter 。 循环将一直执行直到X = 100,最后一次执行,然后停止。

    • Range("A" & X).Value = X: this declares the range of the loop and what to put in that range. Since X = 1 initially, the first cell will be A1, at which point the loop will put X into that cell.

      Range(“ A”&X).Value = X:这声明了循环的范围以及该范围内的内容。 由于最初X = 1,因此第一个单元格将为A1,这时循环会将X放入该单元格。
    • Next X: this tells the loop to run again

      下一个X:这告诉循环再次运行

    我什么时候可以使用? (When could I use this?)

    The For-Next loop is one of the most powerful functionalities of VBA; there are numerous potential use cases. This is a more complex example that would require multiple layers of logic, but it communicates the world of possibilities in For-Next loops.

    For-Next循环是VBA最强大的功能之一。 有许多潜在的用例。 这是一个更复杂的示例,需要多层逻辑,但它在For-Next循环中传达了各种可能性。

    Maybe you have a list of all products sold at your bakery in Column A, the type of product in Column B (cakes, donuts, or muffins), the cost of ingredients in Column C, and the market average cost of each product type in another sheet.

    也许您在面包店的A列中列出了所有在面包店出售的产品,在B列中的产品类型(蛋糕,甜甜圈或松饼),在C列中的原料成本以及在每种产品类型中的市场平均成本清单另一张纸。

    You need to figure out what should be the retail price of each product. You’re thinking it should be the cost of ingredients plus 20%, but also 1.2% under market average if possible. A For-Next loop would allow you to do this type of calculation.

    您需要弄清楚每种产品的零售价是多少。 您认为这应该是原料成本加上20%,但如果可能的话,还应该比市场平ASP格低1.2%。 For-Next循环将允许您执行这种类型的计算。

    现实世界的例子 (Real World Examples)

    • Use a loop with a nested if statement to add specific values to a separate array only if they meet certain conditions

      仅当满足特定条件时,才使用带有嵌套if语句的循环将特定值添加到单独的数组中
    • Perform mathematical calculations on each value in a range, e.g. calculate additional charges and add them to the value

      对范围内的每个值执行数学计算,例如计算额外费用并将其添加到该值
    • Loop through each character in a string and extract all numbers

      遍历字符串中的每个字符并提取所有数字
    • Randomly select a number of values from an array

      从数组中随机选择多个值

    结论 (Conclusion)

    Now that we’ve talked about pizza and muffins and oh-yeah, how to write VBA code in Excel spreadsheets, let’s do a learning check. See if you can answer these questions.

    既然我们已经讨论了比萨饼和松饼,是的,如何在Excel电子表格中编写VBA代码,让我们进行学习检查。 看看您是否可以回答这些问题。

    • What is VBA?

      什么是VBA?
    • How do I get set up to start using VBA in Excel?

      如何设置开始在Excel中使用VBA?
    • Why and when would you use VBA?

      为什么以及何时使用VBA?
    • What are some problems I could solve with VBA?

      我可以使用VBA解决哪些问题?

    If you have a fair idea of how to you could answer these questions, then this was successful.

    如果您对如何回答这些问题有一个明确的想法,那么这是成功的。

    Whether you’re an occasional user or a power user, I hope this tutorial provided useful information about what can be accomplished with just a bit of code in your Excel spreadsheets.

    无论您是偶尔使用还是高级用户,我都希望本教程提供有用的信息,说明只需使用Excel电子表格中的少量代码即可完成的工作。

    Happy coding!

    编码愉快!

    学习资源 (Learning Resources)

    关于我的一点 (A bit about me)

    I'm Chloe Tucker, an artist and developer in Portland, Oregon. As a former educator, I'm continuously searching for the intersection of learning and teaching, or technology and art. Reach out to me on Twitter @_chloetucker and check out my website at chloe.dev.

    我是俄勒冈州波特兰市的艺术家兼开发商Chloe Tucker。 作为前教育工作者,我一直在寻找学与教,技术与艺术的交集。 伸手到我的Twitter上@_chloetucker ,并在检查了我的网站chloe.dev

    翻译自: https://www.freecodecamp.org/news/excel-vba-tutorial/

    展开全文
  • Excel VBA教程:GetOpenFilename方法

    万次阅读 2010-07-15 23:32:00
    VBA示例 本示例显示文本文件的“打开”对话框。如果用户选择了一个文件名,则在消息框中显示所选的文件名。 fileToOpen = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If fileToOpen <> ...

    显示标准的“打开”对话框,获取用户文件名,而不必真正打开任何文件。

    expression.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect)

    expression   必需。该表达式返回一个 Application对象。

    FileFilter   Variant类型,可选。一个指定文件筛选条件的字符串。

    本字符串由一个文件筛选字符串与 MS-DOS 通配符表达的文件筛选规则说明组成,中间以逗号分隔。每个字符串都在“文件类型”下拉列表框中列出。例如,下列字符串指定两种文件筛选 —— 文本和加载宏:“文本文件 (*.txt)、*.txt、Add-In 文件 (*.xla)、*.xla”。

    若要为单个文件筛选类型使用多个 MS-DOS 通配符表达式,需用分号将通配符表达式分开。例如:“Visual Basic 文件 (*.bas; *.txt)、*.bas; *.txt”。

    如果省略本参数,则默认参数值为“所有文件 (*.*)、*.*”。

    FilterIndex   Variant 类型,可选。指定默认文件筛选条件的索引号,取值范围为 1 到由 FileFilter 所指定的筛选条件数目。如果省略本参数,或者取值大于可用筛选数目,则采用第一个文件筛选条件。

    Title   Variant 类型,可选。指定对话框的标题。如果省略本参数,则使用“打开”作为标题。

    ButtonText   Variant 类型,可选。仅用于 Macintosh。

    MultiSelect   Variant 类型,可选。如果该值为 True,则允许选定多个文件名,如果该值为 False,则只允许选定单个文件名。默认值为 False。

    说明

    本方法返回的是选定的文件名或用户输入的文件名。返回的名称可能包含路径的说明。如果 MultiSelect 为为 True,则返回值将是一个包含所有选定文件名的数组(即使仅选定了一个文件名)。如果用户取消了对话框,则该值为 False。

    本方法可能更改当前驱动器或文件夹。

    VBA示例

    本示例显示文本文件的“打开”对话框。如果用户选择了一个文件名,则在消息框中显示所选的文件名。

    fileToOpen = Application _
        .GetOpenFilename("Text Files (*.txt), *.txt")
    If fileToOpen <> False Then
        MsgBox "Open " & fileToOpen
    End If
    展开全文
  • Excel vba 入门教程

    2013-05-02 14:59:11
    Excel vba 入门教程,这是Excel vba 入门教程
  • Excel_VBA教程

    2010-08-27 09:32:40
    Excel_VBA教程,Excel_VBA教程
  • Excel VBA基础教程

    2018-02-13 09:09:00
    https://www.w3cschool.cn/excelvba/excelvba-basics.html Excel VBA语言基础 VBA语言的基础认识 详解VBA编程是什么 excel处理录制的宏 Excel中控件的常规使用方法 VBA的变量含义和...

     

    https://www.w3cschool.cn/excelvba/excelvba-basics.html

    转载于:https://www.cnblogs.com/dlsunf/p/8445987.html

    展开全文
  • excel vba pdf教程

    2008-01-16 11:23:04
    excel vba pdf教程
  • Excel vba入门教程

    2012-04-19 13:13:26
    Excel vba入门教程,一个适合初学者学习的好东东。
  • Excel-VBA教程

    2018-02-28 17:29:24
    excel 自学 教程 excel 自学 教程excel 自学 教程excel 自学 教程
  • ExcelVBA简明教程

    2014-06-18 00:16:47
    Excel_VBA简明教程,学习的好帮手,工作的好帮手哎!
  • excel-vba教程

    2013-03-25 21:33:42
    excel2002 vba教程完整版
  • Excel2010 VBA教程素材

    2017-10-26 15:53:20
    学习Excel2010VBA所用的素材,Excel2010VBA实用教程VBA学习
  • EXCEL vba简易教程

    2007-04-28 16:03:36
    EXCEL vba简易教程及有关函数运用
  • excel vba 初级教程

    2015-11-03 09:23:22
    非常好的excel初级教程,非常适合新手!
  • Excel VBA实例教程.zip

    2020-07-16 16:35:32
    教程是英文版的excel结合了vba实例教程涉及到vba种常用的函数以及组件的使用,适合初学者和想提高自身水平的阅读者
  • EXCEL VBA入门教程

    2008-11-16 13:04:31
    关于EXCEL VBA的入门系列教程,资源大小102K

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 530
精华内容 212
关键字:

excelvba教程