Efnisyfirlit

• Excel® 2016 Power Programming with VBA
• About the Authors
• About the Technical Editor
• Credits
• Contents at a Glance
• Table of Contents
• Introduction
• Part I: Introduction to Excel VBA
  • Chapter 1: Essentials of Spreadsheet Application Development
    • What Is a Spreadsheet Application?
    • Steps for Application Development
    • Determining User Needs
    • Planning an Application That Meets User Needs
    • Determining the Most Appropriate User Interface
      • Customizing the Ribbon
      • Customizing shortcut menus
      • Creating shortcut keys
      • Creating custom dialog boxes
      • Using ActiveX controls on a worksheet
      • Executing the development effort
    • Concerning Yourself with the End User
      • Testing the application
      • Making the application bulletproof
      • Making the application aesthetically appealing and intuitive
      • Creating a user Help system
      • Documenting the development effort
      • Distributing the application to the user
      • Updating the application when necessary
    • Other Development Issues
      • The user’s installed version of Excel
      • Language issues
      • System speed
      • Video modes
  • Chapter 2: Introducing Visual Basic for Applications
    • Getting a Head Start with the Macro Recorder
      • Creating your first macro
      • Comparing absolute and relative macro recording
      • Other macro recording concepts
    • Working with the Visual Basic Editor
      • Understanding VBE components
      • Working with the Project window
      • Working with a Code window
      • Customizing the VBA environment
      • The Editor Format tab
      • The General tab
      • The Docking tab
    • VBA Fundamentals
      • Understanding objects
      • Understanding collections
      • Understanding properties
    • Deep Dive: Working with Range Objects
      • Finding the properties of the Range object
      • The Range property
      • The Cells property
      • The Offset property
    • Essential Concepts to Remember
    • Don’t Panic—You Are Not Alone
      • Read the rest of the book
      • Let Excel help write your macro
      • Use the Help system
      • Use the Object Browser
      • Pilfer code from the Internet
      • Leverage user forums
      • Visit expert blogs
      • Mine YouTube for video training
      • Learn from the Microsoft Office Dev Center
      • Dissect the other Excel files in your organization
      • Ask your local Excel genius
  • Chapter 3: VBA Programming Fundamentals
    • VBA Language Elements: An Overview
    • Comments
    • Variables, Data Types, and Constants
      • Defining data types
      • Declaring variables
      • Scoping variables
      • Working with constants
      • Working with strings
      • Working with dates
    • Assignment Statements
    • Arrays
      • Declaring arrays
      • Declaring multidimensional arrays
      • Declaring dynamic arrays
    • Object Variables
    • User-Defined Data Types
    • Built-In Functions
    • Manipulating Objects and Collections
      • With-End With constructs
      • For Each-Next constructs
    • Controlling Code Execution
      • GoTo statements
      • If-Then constructs
      • Select Case constructs
      • Looping blocks of instructions
  • Chapter 4: Working with VBA Sub Procedures
    • About Procedures
      • Declaring a Sub procedure
      • Scoping a procedure
    • Executing Sub Procedures
      • Executing a procedure with the Run Sub/UserForm command
      • Executing a procedure from the Macro dialog box
      • Executing a procedure with a Ctrl+shortcut key combination
      • Executing a procedure from the Ribbon
      • Executing a procedure from a customized shortcut menu
      • Executing a procedure from another procedure
      • Executing a procedure by clicking an object
      • Executing a procedure when an event occurs
      • Executing a procedure from the Immediate window
    • Passing Arguments to Procedures
    • Error-Handling Techniques
      • Trapping errors
      • Error-handling examples
    • A Realistic Example That Uses Sub Procedures
      • The goal
      • Project requirements
      • What you know
      • The approach
      • Some preliminary recording
      • Initial setup
      • Code writing
      • Writing the Sort procedure
      • More testing
      • Fixing the problems
      • Utility availability
      • Evaluating the project
  • Chapter 5: Creating Function Procedures
    • Sub Procedures versus Function Procedures
    • Why Create Custom Functions?
    • An Introductory Function Example
      • Using the function in a worksheet
      • Using the function in a VBA procedure
      • Analyzing the custom function
    • Function Procedures
      • A function’s scope
      • Executing function procedures
    • Function Arguments
    • Function Examples
      • Functions with no argument
      • A function with one argument
      • A function with two arguments
      • A function with an array argument
      • A function with optional arguments
      • A function that returns a VBA array
      • A function that returns an error value
      • A function with an indefinite number of arguments
    • Emulating Excel’s SUM Function
    • Extended Date Functions
    • Debugging Functions
    • Dealing with the Insert Function Dialog Box
      • Using the MacroOptions method
      • Specifying a function category
      • Adding a function description manually
    • Using Add-Ins to Store Custom Functions
    • Using the Windows API
      • Windows API examples
      • Determining the Windows directory
      • Detecting the Shift key
      • Learning more about API functions
  • Chapter 6: Understanding Excel’s Events
    • What You Should Know about Events
      • Understanding event sequences
      • Where to put event-handler procedures
      • Disabling events
      • Entering event-handler code
      • Event-handler procedures that use arguments
    • Getting Acquainted with Workbook-Level Events
      • The Open event
      • The Activate event
      • The SheetActivate event
      • The NewSheet event
      • The BeforeSave event
      • The Deactivate event
      • The BeforePrint event
      • The BeforeClose event
    • Examining Worksheet Events
      • The Change event
      • Monitoring a specific range for changes
      • The SelectionChange event
      • The BeforeDoubleClick event
      • The BeforeRightClick event
    • Monitoring with Application Events
      • Enabling Application-level events
      • Determining when a workbook is opened
      • Monitoring Application-level events
    • Accessing Events Not Associated with an Object
      • The OnTime event
      • The OnKey event
  • Chapter 7: VBA Programming Examples and Techniques
    • Learning by Example
    • Working with Ranges
      • Copying a range
      • Moving a range
      • Copying a variably sized range
      • Selecting or otherwise identifying various types of ranges
      • Resizing a range
      • Prompting for a cell value
      • Entering a value in the next empty cell
      • Pausing a macro to get a user-selected range
      • Counting selected cells
      • Determining the type of selected range
      • Looping through a selected range efficiently
      • Deleting all empty rows
      • Duplicating rows a variable number of times
      • Determining whether a range is contained in another range
      • Determining a cell’s data type
      • Reading and writing ranges
      • A better way to write to a range
      • Transferring one-dimensional arrays
      • Transferring a range to a variant array
      • Selecting cells by value
      • Copying a noncontiguous range
    • Working with Workbooks and Sheets
      • Saving all workbooks
      • Saving and closing all workbooks
      • Hiding all but the selection
      • Creating a hyperlink table of contents
      • Synchronizing worksheets
    • VBA Techniques
      • Toggling a Boolean property
      • Displaying the date and time
      • Displaying friendly time
      • Getting a list of fonts
      • Sorting an array
      • Processing a series of files
    • Some Useful Functions for Use in Your Code
      • The FileExists function
      • The FileNameOnly function
      • The PathExists function
      • The RangeNameExists function
      • The SheetExists function
      • The WorkbookIsOpen function
      • Retrieving a value from a closed workbook
    • Some Useful Worksheet Functions
      • Returning cell formatting information
      • A talking worksheet
      • Displaying the date when a file was saved or printed
      • Understanding object parents
      • Counting cells between two values
      • Determining the last nonempty cell in a column or row
      • Does a string match a pattern?
      • Extracting the nth element from a string
      • Spelling out a number
      • A multifunctional function
      • The SHEETOFFSET function
      • Returning the maximum value across all worksheets
      • Returning an array of nonduplicated random integers
      • Randomizing a range
      • Sorting a range
    • Windows API Calls
      • Understanding API Declarations
      • Determining file associations
      • Determining default printer information
      • Determining video display information
      • Reading from and writing to the Registry
• Part II: Advanced VBA Techniques
  • Chapter 8: Working with Pivot Tables
    • An Introductory Pivot Table Example
      • Creating a pivot table
      • Examining the recorded code for the pivot table
      • Cleaning up the recorded pivot table code
    • Creating a More Complex Pivot Table
      • The code that created the pivot table
      • How the more complex pivot table works
    • Creating Multiple Pivot Tables
    • Creating a Reverse Pivot Table
  • Chapter 9: Working with Charts
    • Getting the Inside Scoop on Charts
      • Chart locations
      • The macro recorder and charts
      • The Chart object model
    • Creating an Embedded Chart
    • Creating a Chart on a Chart Sheet
    • Modifying Charts
    • Using VBA to Activate a Chart
    • Moving a Chart
    • Using VBA to Deactivate a Chart
    • Determining Whether a Chart Is Activated
    • Deleting from the ChartObjects or Charts Collection
    • Looping through All Charts
    • Sizing and Aligning ChartObjects
    • Creating Lots of Charts
    • Exporting a Chart
      • Exporting all graphics
    • Changing the Data Used in a Chart
      • Changing chart data based on the active cell
      • Using VBA to determine the ranges used in a chart
    • Using VBA to Display Arbitrary Data Labels on a Chart
    • Displaying a Chart in a UserForm
    • Understanding Chart Events
      • An example of using Chart events
      • Enabling events for an embedded chart
      • Example: Using Chart events with an embedded chart
    • Discovering VBA Charting Tricks
      • Printing embedded charts on a full page
      • Creating unlinked charts
      • Displaying text with the MouseOver event
      • Scrolling a chart
    • Working with Sparkline Charts
  • Chapter 10: Interacting with Other Applications
    • Understanding Microsoft Office Automation
      • Understanding the concept of binding
      • A simple automation example
    • Automating Access from Excel
      • Running an Access Query from Excel
      • Running an Access Macro from Excel
    • Automating Word from Excel
      • Sending Excel data to a Word document
      • Simulating mail merge with a Word document
    • Automating PowerPoint from Excel
      • Sending Excel data to a PowerPoint presentation
      • Sending all Excel charts to a PowerPoint presentation
      • Convert a workbook into a PowerPoint presentation
    • Automating Outlook from Excel
      • Mailing the Active Workbook as an Attachment
      • Mailing a Specific Range as an Attachment
      • Mailing a Single Sheet as an Attachment
      • Mailing All Email Addresses in Your Contact List
    • Starting Other Applications from Excel
      • Using the VBA Shell function
      • Using the Windows ShellExecute API function
      • Using AppActivate
      • Running Control Panel dialog boxes
  • Chapter 11: Working with External Data and Files
    • Working with External Data Connections
      • Manually creating a connection
      • Manually editing data connections
      • Using VBA to create dynamic connections
      • Iterating through all connections in a workbook
    • Using ADO and VBA to Pull External Data
      • The connection string
      • Declaring a Recordset
      • Referencing the ADO object library
      • Putting it all together in code
      • Using ADO with the active workbook
    • Working with Text Files
      • Opening a text file
      • Reading a text file
      • Writing a text file
      • Getting a file number
      • Determining or setting the file position
      • Statements for reading and writing
    • Text File Manipulation Examples
      • Importing data in a text file
      • Exporting a range to a text file
      • Importing a text file to a range
      • Logging Excel usage
      • Filtering a text file
    • Performing Common File Operations
      • Using VBA file-related statements
      • Using the FileSystemObject object
    • Zipping and Unzipping Files
      • Zipping files
      • Unzipping a file
• Part III: Working with UserForms
  • Chapter 12: Leveraging Custom Dialog Boxes
    • Before You Create That UserForm
    • Using an Input Box
      • The VBA InputBox function
      • The Application.InputBox method
    • The VBA MsgBox Function
    • The Excel GetOpenFilename Method
    • The Excel GetSaveAsFilename Method
    • Prompting for a Directory
    • Displaying Excel’s Built-In Dialog Boxes
    • Displaying a Data Form
      • Making the data form accessible
      • Displaying a data form by using VBA
  • Chapter 13: Introducing UserForms
    • How Excel Handles Custom Dialog Boxes
    • Inserting a New UserForm
    • Adding Controls to a UserForm
    • Toolbox Controls
      • CheckBox
      • ComboBox
      • CommandButton
      • Frame
      • Image
      • Label
      • ListBox
      • MultiPage
      • OptionButton
      • RefEdit
      • ScrollBar
      • SpinButton
      • TabStrip
      • TextBox
      • ToggleButton
    • Adjusting UserForm Controls
    • Adjusting a Control’s Properties
      • Using the Properties window
      • Common properties
      • Accommodating keyboard users
    • Displaying a UserForm
      • Adjusting the display position
      • Displaying a modeless UserForm
      • Displaying a UserForm based on a variable
      • Loading a UserForm
      • About event-handler procedures
    • Closing a UserForm
    • Creating a UserForm: An Example
      • Creating the UserForm
      • Writing code to display the dialog box
      • Testing the dialog box
      • Adding event-handler procedures
      • The finished dialog box
    • Understanding UserForm Events
      • Learning about events
      • UserForm events
      • SpinButton events
      • Pairing a SpinButton with a TextBox
    • Referencing UserForm Controls
    • Customizing the Toolbox
      • Adding new pages to the Toolbox
      • Customizing or combining controls
      • Adding new ActiveX controls
    • Creating UserForm Templates
    • A UserForm Checklist
  • Chapter 14: UserForm Examples
    • Creating a UserForm “Menu”
      • Using CommandButtons in a UserForm
      • Using a ListBox in a UserForm
    • Selecting Ranges from a UserForm
    • Creating a Splash Screen
    • Disabling a UserForm's Close Button
    • Changing a UserForm's Size
    • Zooming and Scrolling a Sheet from a UserForm
    • ListBox Techniques
      • Adding items to a ListBox control
      • Determining the selected item in a ListBox
      • Determining multiple selections in a ListBox
      • Multiple lists in a single ListBox
      • ListBox item transfer
      • Moving items in a ListBox
      • Working with multicolumn ListBox controls
      • Using a ListBox to select worksheet rows
      • Using a ListBox to activate a sheet
      • Filtering a ListBox from a TextBox
    • Using the MultiPage Control in a UserForm
    • Using an External Control
    • Animating a Label
  • Chapter 15: Advanced UserForm Techniques
    • A Modeless Dialog Box
    • Displaying a Progress Indicator
      • Creating a stand-alone progress indicator
      • Showing a progress indicator that’s integrated into a UserForm
      • Creating a non-graphical progress indicator
    • Creating Wizards
      • Setting up the MultiPage control for the wizard
      • Adding the buttons to the wizard’s UserForm
      • Programming the wizard’s buttons
      • Programming dependencies in a wizard
      • Performing the task with the wizard
    • Emulating the MsgBox Function
      • MsgBox emulation: MyMsgBox code
      • How the MyMsgBox function works
      • Using the MyMsgBox function
    • A UserForm with Movable Controls
    • A UserForm with No Title Bar
    • Simulating a Toolbar with a UserForm
    • Emulating a Task Pane with a UserForm
    • A Resizable UserForm
    • Handling Multiple UserForm Controls with One Event Handler
    • Selecting a Color in a UserForm
    • Displaying a Chart in a UserForm
      • Saving a chart as a GIF file
      • Changing the Image control’s Picture property
    • Making a UserForm Semitransparent
    • A Puzzle on a UserForm
    • Video Poker on a UserForm
• Part IV: Developing Excel Applications
  • Chapter 16: Creating and Using Add-Ins
    • What Is an Add-In?
      • Comparing an add-in with a standard workbook
      • Why create add-ins?
    • Understanding Excel’s Add-In Manager
    • Creating an Add-in
    • An Add-In Example
      • Adding descriptive information for the example add-in
      • Creating an add-in
      • Installing an add-in
      • Testing the add-in
      • Distributing an add-in
      • Modifying an add-in
    • Comparing XLAM and XLSM Files
      • XLAM file VBA collection membership
      • Visibility of XLSM and XLAM files
      • Worksheets and chart sheets in XLSM and XLAM files
      • Accessing VBA procedures in an add-in
    • Manipulating Add-Ins with VBA
      • Adding an item to the AddIns collection
      • Removing an item from the AddIns collection
      • AddIn object properties
      • Accessing an add-in as a workbook
      • AddIn object events
    • Optimizing the Performance of Add-Ins
    • Special Problems with Add-Ins
      • Ensuring that an add-in is installed
      • Referencing other files from an add-in
      • Detecting the proper Excel version for your add-in
  • Chapter 17: Working with the Ribbon
    • Ribbon Basics
    • Customizing the Ribbon
      • Adding a button to the Ribbon
      • Adding a button to the Quick Access Toolbar
      • Understanding the limitations of Ribbon customization
    • Creating a Custom Ribbon
      • Adding a button to an existing tab
      • Adding a check box to an existing tab
      • Ribbon controls demo
      • A dynamicMenu control example
      • More on Ribbon customization
    • Using VBA with the Ribbon
      • Accessing a Ribbon control
      • Working with the Ribbon
      • Activating a tab
    • Creating an Old-Style Toolbar
      • Limitations of old-style toolbars in Excel 2007 and later
      • Code to create a toolbar
  • Chapter 18: Working with Shortcut Menus
    • CommandBar Overview
      • CommandBar types
      • Listing shortcut menus
      • Referring to CommandBars
      • Referring to controls in a CommandBar
      • Properties of CommandBar controls
      • Displaying all shortcut menu items
    • Using VBA to Customize Shortcut Menus
      • Shortcut menu and the single-document interface
      • Resetting a shortcut menu
      • Disabling a shortcut menu
      • Disabling shortcut menu items
      • Adding a new item to the Cell shortcut menu
      • Adding a submenu to a shortcut menu
      • Limiting a shortcut menu to a single workbook
    • Shortcut Menus and Events
      • Adding and deleting menus automatically
      • Disabling or hiding shortcut menu items
      • Creating a context-sensitive shortcut menu
  • Chapter 19: Providing Help for Your Applications
    • Help for Your Excel Applications
    • Help Systems That Use Excel Components
      • Using cell comments for help
      • Using a text box for help
      • Using a worksheet to display help text
      • Displaying help in a UserForm
    • Displaying Help in a Web Browser
      • Using HTML files
      • Using an MHTML file
    • Using the HTML Help System
      • Using the Help method to display HTML Help
      • Associating a help file with your application
      • Associating a help topic with a VBA function
  • Chapter 20: Leveraging Class Modules
    • What Is a Class Module?
      • Built-in class modules
      • Custom class modules
    • Creating a NumLock Class
      • Inserting a class module
      • Adding VBA code to the class module
      • Using the CNumLock class
    • Coding Properties, Methods, and Events
      • Programming properties of objects
      • Programming methods for objects
      • Class module events
    • Exposing a QueryTable Event
    • Creating a Class to Hold Classes
      • Creating the CSalesRep and CSalesReps classes
      • Creating the CInvoice and CInvoices classes
      • Filling the parent classes with objects
      • Calculating the commissons
  • Chapter 21: Understanding Compatibility Issues
    • What Is Compatibility?
    • Types of Compatibility Problems
    • Avoid Using New Features
    • But Will It Work on a Mac?
    • Dealing with 64-Bit Excel
    • Creating an International Application
      • Multilanguage applications
      • VBA language considerations
      • Using local properties
      • Identifying system settings
      • Date and time settings
• Part V: Appendix
  • Appendix A: VBA Statements and Function Reference
• Index
• Advert
• EULA

UM RAFBÆKUR Á HEIMKAUP.IS

Bókahillan þín er þitt svæði og þar eru bækurnar þínar geymdar. Þú kemst í bókahilluna þína hvar og hvenær sem er í tölvu eða snjalltæki. Einfalt og þægilegt!

Rafbók til eignar
Rafbók til eignar þarf að hlaða niður á þau tæki sem þú vilt nota innan eins árs frá því bókin er keypt.

Þú kemst í bækurnar hvar sem er
Þú getur nálgast allar raf(skóla)bækurnar þínar á einu augabragði, hvar og hvenær sem er í bókahillunni þinni. Engin taska, enginn kyndill og ekkert vesen (hvað þá yfirvigt).

Auðvelt að fletta og leita
Þú getur flakkað milli síðna og kafla eins og þér hentar best og farið beint í ákveðna kafla úr efnisyfirlitinu. Í leitinni finnur þú orð, kafla eða síður í einum smelli.

Glósur og yfirstrikanir
Þú getur auðkennt textabrot með mismunandi litum og skrifað glósur að vild í rafbókina. Þú getur jafnvel séð glósur og yfirstrikanir hjá bekkjarsystkinum og kennara ef þeir leyfa það. Allt á einum stað.

Hvað viltu sjá? / Þú ræður hvernig síðan lítur út
Þú lagar síðuna að þínum þörfum. Stækkaðu eða minnkaðu myndir og texta með multi-level zoom til að sjá síðuna eins og þér hentar best í þínu námi.



Fleiri góðir kostir
- Þú getur prentað síður úr bókinni (innan þeirra marka sem útgefandinn setur)
- Möguleiki á tengingu við annað stafrænt og gagnvirkt efni, svo sem myndbönd eða spurningar úr efninu
- Auðvelt að afrita og líma efni/texta fyrir t.d. heimaverkefni eða ritgerðir
- Styður tækni sem hjálpar nemendum með sjón- eða heyrnarskerðingu