|
Conference sessions on Wednesday:
9:00 - What's new in Excel 2007 that you might have overlooked; what missing that you want back Bernard Liengme will examine some of the fantastic new features of Excel 2007. In addition he will show us where some of the old features are hidden, along with what is missing.
10:45 - Access - Introduction to Queries Damon Longworth will demonstrate how easy it is to begin using queries in Access.
1:00 - Excel - Tips and Tricks Bob Umlas will go through a fast paced display of tips and tricks to increase productivity with Excel.
2:45 - Excel - Introduction to Charting Jon Peltier will show us how to easily create charts as he explains how the charting engine works. All of this while giving us tips to make the whole process easier.
Conference sessions on Thursday:
9:00 - Interactions Between Office Application Jon Peltier will examine some of the problems and solutions when data is moved between the different Microsoft Office applications.
10:30 - Excel - Array Formulas Bob Umlas will demonstrate how to construct and then show some of the some of the creative ways to use array formulas - unveiling the mystery of these complex formulas.
1:00 - Excel Checking Your Answers in Solver in VBA Bernard Liengme will reveal the power and how easy it is to use the Solver Add-in.
2:45 - Excel - Pivot Tables Damon Longworth present some of the most useful features of Pivot Tables in Excel.
Add-on classes on Wednesday:
8:30 - Excel Advanced Charting Techniques - Jon Peltier 1. Intro and Review of Best Practices 2. Combination Charts 3. Dynamic Charts 4. Axis Tricks 5. User Defined Types and Templates 6. VBA to Automate Charting in Excel 7. VBA for Charting Across Applications. 8. Question and Answer
1:00 - Excel - Using Array Variables - Damon Longworth 1. What are Array Variables? 2. Declaring Array Variables - Dynamic vs. Static 3. Single vs Multi Dimensional 4. Useful Procedures - Examples 5. Adding Data to Arrays 6. Getting Data Out of Arrays 7. Sorting Arrays 8. Question and Answer
Add-on classes on Thursday:
8:30 - Excel - Intro to VBA for Excel starting at the very basics - Bernard Liengme This workshop is designed for those who would like to become more informed about VBA for Excel. It will not make you a programmer but it will give you more confidence in using VBA solutions obtained from books or internet; and give you the skill to make minor changes to them. If you plan to really learn VBA this is a good way to get started with a complete overview.
Topic 1 Terminology: macros, modules, subroutines and functions; general, worksheet, workbook modules.
Topic 2 The VBE Editor: introduction to its parts; Insert Module command; the Immediate window
Topic 3 Tech Speak: variables constants, arrays; the DIM statement; scope of variables.
Topic 4 Program Structure: structured programming; loop and branching structures; the often-abused Go To statements.
Topic 5: We look at and construct some simple User Defined functions
Topic 6: We make some simple subroutines with Recorder; we modify them.
Topic 7: More subroutines explored. Some debugging techniques. Adding controls to the worksheet.
Topic 8 Sharing modules with others: Import and Export; Add-ins; Personal.XLS
Topic 9 the Excel Object model: what it is and why it is important to understand it.
Topic 10: Working with files for input and output.
Each registrant will be given a CD with the lecture notes and sample modules. We will not have time to cover Forms but this topic is well covered in many books once you know the basics. You are welcome to bring your laptop but there will not be time for personal instruction.
1:00 - Intermediate VBA - Bob Umlas Bob Umlas will show examples of these techniques: Range("A1").range("Data").whatever, For i=1 to 801 step 100 cells(i,1).range("Data").Clearcontents Next IsAddin For each x in Array(…) Debug.Assert; Debug.Print, Add Watch, Right-click in VBA, last position, set next statement, run to cursor, Using Bookmarks, Get/Set/Delete, Setting Application.Cursor =, Aware of naming ranges for XL2007, Ranges.xls, Variable scoping, Arr=Range("A1:E100") and then work with Arr Gosub, Selection.Name.name, Immediate window, Selection.offset(0,2).Select (for large discontiguous range), F5 from WS to VBA, Range name = VBA proc name & why it says module1.xyz instead of xyz, Set statement, >~5000 lines of code/module, Doevents, Ontime, Now Events - sheet vs workbook, Erl, On Error Resume Next, w/If Err.Number<>0, Stop vs Breakpoint, Option Private Module; Sub XYZ(Optional q) to not show in macro, run, list, Commandbars("Toolbar List").enabled, Option Base 1, Option compare text, Application.Worksheetfunction…., EnableEvents, For i=1 to 1000 Select Case i Case 10,57,355 Case 12,702,780 to 800, 977 End Select Next IIF
Add-on classes on Friday:
8:30 - Excel Charting with VBA - Jon Peltier Jon Peltier will provide examples to create charts using VBA. In addition, he will walk through ways to modify all portions of the chart with code.
1:00 - User Forms - Bob Umlas Checkboxes, Combobox, Adding new data, Automatically displaying a list, Frames, Images, Labels, Listbox, Getting selected items, Adding/Removing items, Multi-column RowSource, Multipage, Option button, RefEdit, Scrollbar, Spin button, Tab Strip, TextBox, Restricting textbox entries, Password characters, ToggleButtons, Events, Disabling the Close button, Getting information from worksheet to form & vice versa, Creating Wizards, Creating your own controls, Useful URL's, Miscellaneous: getting info from clipboard to controls, Invisible objects shown by mouse move, Initialize event, using GetCustomListContents, setting focus, controltip, text accelerator - including for textboxes, tab order, tag, groupname, tabstop, enabled default buttons, Startup positions for forms
|