Don't be afraid. Excel VBA is really Simple (2nd edition) pdf

Source: Internet
Author: User
Tags arithmetic operators case statement goto logical operators

: Network Disk Download

Content Introduction· · · · · ·

For most professionals who do not have a foundation for programming, they tend to have a lot of fear in learning VBA. This book is aimed at such a crowd, with easy-to-understand language and vivid image of the metaphor, and with a large number of illustrations, the seemingly complex concepts and code in Excel, from simple macro recording, VBA programming environment and basic grammar introduction, to the operation and control of common objects, the execution of program automatic switch-object events, The design of the custom interface, debugging and optimization of the code written, have been introduced in the image.

This book is suitable for those professionals who want to improve their productivity, especially those who often need to process and analyze large amounts of data, and are also suitable for college and university students reading in financial science.

Author profile ...


English name, the network is Kevin, insiders repetitive K total (not the general manager of the total, but the total moderator of the total). Once was the pursuit of the ultimate office efficiency accounting One (well, I admit that I just like to lazy), for this desperately toss office, especially Excel. In order to be with like-minded people to toss excel,1999 years of Whim founded Excelhome, did not expect a make on to today. Restless small accountant later simply moved to it circle, tinker and many times implement information system. Some books over the years, shared some online video tutorials, and often touted their lazy experience and customized solutions to the business, and so Microsoft has been rated as a top worker--oh, wrong, should call Microsoft the world's most valuable expert. The future plans to continue preaching the road, let more people embark on the road of efficient office.

Kosala Hair

Network Name Ggsmart, Feng. As a teacher after graduation, more than 10 years of teaching experience let me grow into an experienced teaching expert (self-styled). Life in leisure also have no hobbies, love the Internet, visit the forum, turn over the Web page, occasionally hooking up with sister (although never hooked success). But I love the Excel, I love to study Excel methods, also love to share Excel tips and tutorials. But I'm not a hero, and I don't want to be an expert. I like you, just a cousin, I just want to use my experience to tell you: To learn Excel, is actually very simple.

1th. Excel VBA, it's not as hard as you think.
1.1 The repeated and repetitive actions in Excel
1.1.1 You use Excel like this, I'm going to spit the groove
1.1.2 Repeat operations, Excel everywhere
1.1.3 repetitive operations, like repetitive sounds
1.1.4 Excel also has a similar "recording device"
1.2 Repeat operations in Excel can be recorded
1.2.1 "Recording device" in Excel
1.2.2 Recording actions in Excel with the macro recorder
1.2.3 Let the recording operation reproduce again
1.3 Recording of the operation, but also to reproduce it
1.3.1 Pursue execution speed, use shortcut keys
1.3.2 want an intuitive image, you can use the button
1.4 Recording a good macro, why can't you do it
1.4.1 macros cannot be executed for security reasons
1.4.2 to modify macro security so that Excel allows all macros to be executed
1.5 Excel with what record recorded action/17
1.5.1 macro is a string of code that can control and manipulate excel
1.5.2 Learning VBA is learning to write code that controls and operates Excel
1.6 VBA, is the language we communicate with Excel
1.6.1 to use Excel, you need to know how to "communicate" with it.
1.6.2 VBA, just a name for a computer programming language
1.7 Excel has been able to record code, you need to write it again
1.7.1 recorded macros, does not resolve all issues
1.7.2 can increase the power of a macro simply by making changes
1.7.3 Self-writing code to make the macro function more flexible
The 2nd chapter of Understanding the programming tools, begin to learn the first step of VBA
2.1 Where should I write a VBA program
2.2 Learn about VBA's programming tools-VBE
2.2.1 Which methods can be used to open the VBE window
What's in the 2.2.2 VBE window
2.3 How to write VBA programs in the VBE
2.3.1 VBA Program is a set of VBA code required to complete a task
2.3.2 See what the VBA program looks like
2.3.3 to write a VBA program
3rd. Learn the grammar and understand the rules that VBA programming should follow
3.1 Grammar is the rule that should be followed when language is expressed
3.1.1 Do not understand grammar, the expression will be wrong
3.1.2 As a programming language, VBA also has syntax
3.1.3 Don't worry, VBA syntax is not complicated
3.2 Data and data types in VBA
3.2.1 In Excel, data is the information stored in a cell
3.2.2 data types, which are collectively referred to as data of the same type
3.2.3 VBA divides data into types
3.2.4 Why do you classify the data?
3.3 Containers for data stored in VBA: Variables and constants
Where is the data stored in the 3.3.1 program
3.3.2 variable, which is the memory space reserved for the data
3.3.3 constants, typically used to store certain fixed data
3.4 Using variables to store data in a program
3.4.1 declares a variable, which is the name of the specified variable and the type of data that can be stored
3.4.2 can also declare variables with these statements
3.4.3 Assigning a value to a variable is to store the data in a variable
3.4.4 the data that is stored in the variable to participate in the program calculation
3.4.5 about declaring variables, you should also grasp this knowledge
3.4.6 different variables, scopes may not be the same
3.4.7 defining variables of different scopes
3.5 Special Variables-arrays
3.5.1 Array, which is a collection of multiple variables of the same type
3.5.2 How to represent an element in an array
3.5.3 the size of an array should be declared when declaring an array
3.5.4 Assigning a value to an array is assigning values to each element of the array individually.
Dimensions of the 3.5.5 array
3.5.6 declaring multidimensional arrays
3.5.7 declaring dynamic arrays
3.5.8 This method of creating an array is simpler
3.5.9 about arrays, these operations should be mastered
3.5.10 writes data saved in an array to a range of cells
3.6 Dedicated containers for special data-constants
3.6.1 constants are like disposable paper cups.
3.6.2 when declaring constants, assign values to constants at the same time
3.6.3 constants also have different scopes
3.7 Properties and methods for objects, collections, and objects
3.7.1 objects are things that you manipulate and control with code.
3.7.2 the hierarchy of objects
3.7.3 collections are multiple objects of the same type
3.7.4 how to represent an object in a collection
The 3.7.5 property is the content or feature that the object contains
3.7.6 objects and properties are relative.
The 3.7.7 method is an action or action that is performed on an object
3.8 Connecting data to a bridge, operator in VBA
3.8.1 Arithmetic operators
3.8.2 comparison Operators
3.8.3 text Operators
3.8.4 logical operators
3.8. More than 5 kinds of operations should first calculate who
3.9 Built-in functions in VBA
The 3.9.1 function is a pre-defined calculation
3.9.2 what functions are in VBA
3.10 BASIC statement structure for control program execution
The ubiquitous choice in 3.10.1 life
3.10.2 Resolving selection problems in VBA with an if statement
3.10.3 using the Select Case statement to resolve the "multiple choice One" issue
3.10.4 with For ... Next statement Loop executes the same piece of code
3.10.5 with For Each ... Next statement loops over members in a collection or array
3.10.6 using a Do statement to control loops by condition
3.10.7 use a goto statement to have the program go to another statement to execute
3.10.8 with statement, shorthand code without it
3.11 Sub procedure, basic program unit
3.11.1 VBA process is a combination of the code required to complete a task
Basic structure of the 3.11.2 Sub process
Where the 3.11.3 should write the Sub procedure.
Basic structure of the 3.11.4 Sub process
Scope of the 3.11.5 process
3.11.6 executing another procedure in the process
3.11.7 passing parameters to the procedure/125
3.12 Custom functions, Function procedures
3.12.1 function procedures are functions that are customized with VBA
3.12.2 try to write a custom function
3.12.3 using a custom function to complete a set calculation
3.12.4 to count the number of cells in a specified color with a custom function
3.12.5 statement structure declaring a Function procedure
3.13 Typography and annotations to make writing code more readable
3.13.1 code layout, an essential habit
3.13.2 add comments to special statements to make the code's intentions clear
The 4th chapter is the object of operation, solving the practical problems in the work
4.1 Common objects that you need to be familiar with to communicate with Excel
4.1.1 Programming with VBA It's like cooking in the kitchen.
4.1.2 VBA controls Excel by manipulating different objects
4.1.3 Programming with VBA, you should remember which objects
4.2 Everything starting from me, the topmost application object
4.2.1 Use the ScreenUpdating property to set whether to update the contents on the screen
4.2.2 Setting the DisplayAlerts property suppresses the warning dialog box
4.2.3 Using worksheet functions with the WorksheetFunction property
4.2.4 setting properties, changing the Excel work interface
4.2.5 child objects of the Application object
4.3 Managing workbooks, Understanding Workbook objects
4.3.1 Workbook object is a member of the Workbooks collection
4.3.2 Accessing the properties of an object to obtain information about a workbook file
4.3.3 Creating a workbook with the Add method
4.3.4 opening a workbook with the Open method
4.3.5 activating a workbook with the Activate method
4.3.6 Saving a workbook file
4.3.7 closing a workbook with the Close method
4.3.8 ThisWorkbook and ActiveWorkbook
4.4 Manipulating worksheets, recognizing worksheet objects
4.4.1 3 Ways To reference worksheets
4.4.2 to create a new worksheet with the Add method
4.4.3 Setting the Name property to change the label name of the sheet
4.4.4 Delete a worksheet by using the Delete method
4.4.5 two ways to activate a worksheet
4.4.6 copying a worksheet with the Copy method
4.4.7 moving a worksheet with the Move method
4.4.8 setting the Visible property to hide or show worksheets
4.4.9 access the Count property to get the number of worksheets in the workbook
4.4.10 easily confusing sheets and worksheets objects
4.5 The core of the operation, the critical Range object
4.5.1 referencing cells with the Range property
4.5.2 referencing cells with the Cells property
4.5.3 referencing cells, shorter shortcuts
4.5.4 referencing an entire row of cells
4.5.5 referencing an entire column of cells
4.5.6 merging multiple cell ranges with the Union method
4.5.7 The offset property of a Range object
4.5.8 The Resize property of a Range object
4.5.9 the UsedRange property of the Worksheet object
4.5.10 the CurrentRegion property of a Range object
The End property of the 4.5.11 Range object
4.5.12 the contents of cell: Value property
4.5.13 access the Count property to get the number of cells contained in the range
4.5.14 to get the address of a cell via the Address property
4.5.15 selecting cells with Activate and select methods
4.5.16 Select to clear the information in the cell
4.5.17 copying a range of cells using the Copy method
4.5.18 cutting cells with cut method
4.5.19 Delete the specified cell with the Delete method
4.6 Combine examples to learn how to manipulate objects
4.6.1 creating workbooks based on requirements
4.6.2 determine if a workbook is open
4.6.3 to determine if a workbook file with the specified name exists in the folder
4.6.4 entering data into a workbook that is not open
4.6.5 Hide all sheets outside the active worksheet
4.6.6 batch new worksheet with specified name
4.6.7 Batch data classification and save to different worksheets
4.6.8 merge data from multiple worksheets into a single worksheet
4.6.9 Save every sheet in a workbook as a separate workbook file
4.6.10 merging data from multiple workbooks into the same sheet
4.6.11 create a linked directory for worksheets in the same workbook
5th. Automatic switch to execute Program-object event
5.1 Installing an auto-execute switch with an event handler
An 5.1.1 event is an action that can be identified by an object.
How the 5.1.2 event executes the program
5.1.3 let Excel automatically respond to our actions
5.1.4 Sub Procedure-event procedure that can be run automatically
5.1.5 using events to let Excel write the current system time in a cell
5.2 Working with worksheet events
5.2.1 Worksheet events are events that occur in the worksheet object
5.2.2 Change events for Worksheet objects
5.2.3 Disabling events so that event procedures are no longer automatically executed
5.2.4 in a swoop, skillfully using the Change event to quickly enter data
5.2.5 SelectionChange Event: Occurs when the selected cell changes
5.2.6 See if I should have a supervisor.
5.2.7 using annotations to record changes to the data in a cell
5.2.8 Common Worksheet Events
5.3 Working with Workbook events
5.3.1 Workbook events are events that occur in the Workbook object
5.3.2 Open Event: Occurs when a workbook is opened
5.3.3 BeforeClose Event: Occurs before the workbook is closed
5.3.4 SheetChange Event: Occurs when a cell in any worksheet is changed
5.3.5 Common Workbook Events
5.4 Events that are not events
5.4.1 OnKey method for application objects
5.4.2 OnTime method for application objects
5.4.3 keep files automatically saved every 5 minutes
The 6th chapter design the custom Operation interface
6.1 What you need to use to design the operator interface
6.1.1 Why do you want to do the programming interface
6.1.2 controls to build the necessary parts of the operator interface
6.1.3 using form controls in worksheets
6.1.4 using ActiveX controls in worksheets
6.1.5 the difference between a form control and an ActiveX control
6.2 No settings required, use out-of-box dialogs
6.2.1 Creating a dialog box with the InputBox function to enter data
6.2.2 Creating an interactive dialog box with the InputBox method
6.2.3 Creating an Output dialog box with the MsgBox function
6.2.4 Display the Open dialog box with the FindFile method
6.2.5 Display the Open dialog box with the GetOpenFileName method
6.2.6 using the Getsaveasfilename method to display the Save As dialog box
6.2.7 get the directory name with the FileDialog property of the Application object
6.3 Designing an Interactive interface using form objects
6.3.1 design interface, need to use UserForm object
6.3.2 Adding a UserForm to the project
6.3.3 setting properties to change the appearance of a form
6.3.4 Adding and setting controls on a form
6.4 Using code to manipulate your own design form
6.4.1 Display User Forms
6.4.2 Setting the display position of a form
6.4.3 to display a form as a modeless form
6.4.4 Close or hide a displayed form
6.5 Event Apps for user forms
6.5.1 initializing the form with the Initialize event
6.5.2 Disable the "Close" button on the form with the help of the QueryClose event
6.5.3 other events for form objects
6.6 Writing code to set functionality for controls in a form
6.6.1 Adding an event procedure for the OK button
6.6.2 using forms to enter data
6.6.3 Adding an event procedure to the Exit button
6.6.4 setting shortcut keys for a control
6.6.5 Changing the tab order of controls
6.7 Create a simple login form with a form
6.7.1 Design the Login form interface
6.7.2 Setting the initial user name and password
6.7.3 Adding code to specify functionality for the control
The 7th Chapter debugging and optimizing the Code of writing
7.1 Errors that may occur in VBA
7.1.1 Compilation Error
7.1.2 Run-time error
7.1.3 Logic Error
7.2 3 states of the VBA program
7.2.1 design mode
7.2.2 operating mode
7.2.3 Interrupt Mode
7.3 Excel is ready for debugging tools
7.3.1 getting the program into break mode
7.3.2 set breakpoints to allow the program to pause execution
7.3.3 using the Stop statement to suspend execution of the program
7.3.4 See changes in variable values in the "Immediate Window"
7.3.5 viewing the values and types of variables in the local window
7.3.6 using the variables in the Watch Window watcher
7.4 Processing run-time errors, these statements may be used
7.4.1 on Error GoTo label
7.4.2 on Error Resume Next
7.4.3 on Error GoTo 0
7.5 Develop good habits and make your code run faster
7.5.1 Rational use of variables in programs
7.5.2 do not repeatedly refer to the same object multiple times with long code
7.5.3 try to use functions to complete the calculation
7.5.4 do not let the code do extra work
7.5.5 Rational Use of arrays
7.5.6 Turn off screen updates if you don't need to interact with the program

: Network Disk Download

Don't be afraid. Excel VBA is really Simple (2nd edition) pdf

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.