Название: Advanced Excel Solutions: A user’s cookbook
Автор: Harish Gopalkrishnan
Год: 2016
Формат: EPUB, PDF
Страниц: 783
Размер: 15,9 Mb
Язык: English
For a user … By a user
This book is a collection of general techniques that can be easily combined to create a wide variety of spreadsheet based solutions.
Written for a typical analyst/manager business owner (who spends a lot of time combining & processing inputs from different sources and then sends different outputs to different stakeholder), instead of diving deep into minute details, the book focuses on techniques that:
– can be used frequently
– have practical utility
– are easy to learn
– are flexible enough to be combined with at least one other technique.
Techniques to work on data in an Excel workbook & also integrate Excel with MS Word, MS PowerPoint, MS Outlook, Lotus Notes & databases.Main focus is on automation with VBA code but, formula based methods are also described.
The book is divided as follows:
Part I–Data in a workbook
Starts off with basic keystrokes & VBA code needed for moving around on a worksheet & performing some basic operations.
Explores the Range object-fundamental object for writing macros. Various methods are shown that provide a reference to a specific range on a worksheet.
Has chapters that deal with:
1. Sorting data on a worksheet with macros.
2. Applying Auto-Filter with VBA code.
3. Worksheet functions (INDEX, MATCH, OFFSET, ADDRESS, SUMIF, COUNTIF etc.) that can extract specific data from worksheets and, techniques to combine these functions.
4. Creating dynamic Named ranges (they expand/ contract as data is added/removed on a spreadsheet). We then explore ways to use such named ranges in formulas, charts & cell validation. An example using named ranges to create country, state & city selection is discussed.
5. Visual Basic functions (MsgBox, IsArray, InStr, Split, Join, DateAdd, DateDiff, DatePart, etc.) that are frequently used when writing macros.
6. Creating Excel Tables & their use in formulas/macros
7. Using VBA to update & read data from PivotTables created on worksheets.
8.Offbeat techniques
-macros to zip or unzip files
-executing one or more macros at a scheduled time.
Part 2-Input/output of data
Discuss ways to integrate Excel with MS Outlook & IBM Lotus notes. We’ll create macros that can search for emails with specific date, subject or sender & can download email attachments.
Explore use of FileSystemObject in macros in order to manage files on discs connected to our computer. & read/Write text files using the TextStream object.
See chapters devoted to VBA code that can transfer data from Excel to Ms Word & Ms PowerPoint. We’ll automate 4 specific tasks that transfer (to a Word or PowerPoint file) a:
-piece of text to a specific location
-chart
-table with fixed number of rows
-table whose number of rows increase/decrease in every reporting period.
A chapter discusses ways to bring data into Excel from databases. We see details of Microsoft’s MS Query feature & explore macros that would 1)update data retrieved by MS Query, 2)get triggered in response to data getting updated.
Part 3-Creating standalone applications in Excel.
Firstly, we review features of a sample application that we’ll create, discuss how data will be entered, how to organize and store data on worksheets.
Next, create the frontend forms & discuss commonly used properties of various controls-ListBox, ComboBox, SpinButton, RadioButton(OptionButton), ScrollBar, TextBox,CheckBox & Label. We’ll see how these controls & their properties can be accessed through our VBA code. A chapter each is devoted to creating forms on an Excel worksheet & on a Visual Basic UserForm.
Develop the ‘Middleware’-actual macros to handle data transfer between forms & data store, respond to actions of the application’s user, produce reports.
Appendix
A-Error Handling-For readers having some experience in macro programming.
B-Visual Basic for Applications (VBA) in context of Excel-For readers who know Visual Basic & want to know VBA
C-Programming Fundamentals