Atlantic City 2008 Schedule

Tuesday, Sep. 23rd: Open forum dinner 6:00 pm - Hard Rock Cafe

Wednesday, Sep. 24th: Beyond the Basics

Wednesday, Sep. 24th: Open forum dinner 6:00 pm - Atlantic City Bar and Grill

Thursday, Sep. 25th:  Advance to the Next Level

Thursday, Sep. 25th: Open forum dinner 6:00 pm - Dynasty

Friday, Sep. 26th: All User Friday

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 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