Recent Excel-VBA Projects
BearingPoint
Project Leader: Mr. Christian J. Germain Senior Manager, Professional Services Market
This company needed an Excel-VBA application that would create monthly reports with 6 different summaries plus filtering, drill-down and sorting capabilities. For each report 10 CSV files needed to be imported in each Excel workbook. The main Excel VBA application does that for 2,600 projects using 26,000 CSV files in 2.5 hours each month. To achieve such performance not a single CSV file is opened yet the data is imported. The client had tried other "more powerful" programs resulting in 22 hours execution time.
SCI
Project Leader: Mr. Scott Farris, Pricing Director.
The company needed a costing application that they could send to their 500 cemetery managers so that they could model prices for their different products (lots, mausoleums, crypts, cremation gardens...),and have a management view of the bottom line results. The data (millions of records) is extracted from the main database, imported in 500 Excel workbooks that allow managers to filter, sort and submit prices (by types of products by garden, by location). Once their work is done the workbooks are sent to the head office and another Excel-VBA application extracts the data from the 500 workbooks and format it to be uploaded in the main database.
National Bank of Canada
Project Leader: Mrs Lise Couture, Senior Financial Analyst
The client required a business model for his 20 social benefit programs. The bank employs 13,000 employees in Canada and fiscal and other rules differ from one province to the other. Each time a single parameter is changed in the model 260,000 calculations must be executed and it all happens in seconds. The application has been running for 10 years. The employee and salary databases are imported and all the costs of the different programs are calculated per pay and per month.
Bombardier Recreational Products
Project Leader: Mr Frederick Angers. Manager Budget & Financial Performance
BRP manufactures hundreds of different products in his 5 plants in Canada, USA and Europe. These products are sold to parent companies and through dealers and distributors. Sale prices differ from one channel to the other. Manufacturing costs are available in the currency of the country where the production plants operate. National legal considerations affect warranty in the different markets (5 continents) and cost of sales and transportation also vary. The Budget & Financial Performance department needed a business model where they could modify volumes of sales, costs of production and prices to evaluate the effect of changes on the corporation cash flow.
Gildan Activewear
Project Leader: Mr Didier Ah Lone Chan, Senior Business Analyst
I have developed many applications for Gildan but the main one is a production planning application. Extracting data from JDEdwards on A/S 400 (sales and production numbers) the application calculates the production requirements for thousands of products produced in 13 plants around the world. Each week the planners click on a button and after 12 minutes the production schedule is generated. This application has been running for 10 years and has been maintained (at a very low cost) to reflect new realities (new products, new colors, new sizes, new production plants...)
Proctor Financial
This firm required an application that they could use to extract the records of all its clients from Charles Schwab, set a portfolio level by type of investment (small cap., large cap., emerging markets....) generating thousands of buy/sell orders. The orders are then formatted and sent electronically to CS for processing.
All the Lessons
Lesson 1: Visual Basic Editor (VBE) in Excel, Lesson 2: The Project Window in the Visual Basic Editor of Excel, Lesson 3: The Properties Window in the Visual Basic Editor of Excel, Lesson 4: The Code Window in the Visual Basic Editor of Excel, Lesson 5: Developing Macros in the VBE, Lesson 6: Testing Macros in the VBE, Lesson 7: Excel Macro Recorder, Lesson 8: Macros Help and Assistancem, Lesson 10: VBA Security and Protection, Lesson 11: VBA Coding Tips, Lesson 12: Dealing with Errors, Lesson 13: Working with the Application, Lesson 14: Working with the Workbooks, Lesson 15: Working with the Worksheets, Lesson 16: Moving Around the Worksheet, Lesson 17: Working with Message and Input Boxes, Lesson 18: Excel VBA Vocabulary to Filter and Sort Data. Lesson 19: Working with Variables, Lesson 20: Working with Statements, Lesson 21: Working with Functions, Lesson 22: Working with external data and SQL, Lesson 23: Working with Windows and other Microsoft Programs FROM Excel, Lesson 24: Forms (Userforms) in VBA for Excel, Lesson 25: Userforms Properties and VBA Code, Lesson 26: Properties and VBA code for Command Buttons, Lesson 27: Properties and VBA code for Labels, Lesson 28: Properties and VBA code for Text Boxes, Lesson 29: Properties and VBA code for Combo Boxes, Lesson 30: Properties and VBA code for List Boxes, Lesson 31: Properties and VBA code for Check Boxes, Option Buttons and Frames, Lesson 32: Properties and VBA code for Spin Buttons, Lesson 33: Excel Image Controls
This website and the downloadable Tutorial on Excel Macros are divided in three sections:
Section 1: Excel Macros Programming: Lessons 1 to 10
This section is about recording, writing, modifying and testing macros in the Visual Basic Editor. You will also learn about security and discover "events" (an event is what starts the macro).
Section 2: Excel VBA Vocabulary: Lessons 11 to 23
Developing a macro is communicating with Excel and to do so you need to use a language called Visual Basic for Applications (VBA). In section 2 you will learn all the VBA vocabulary that is essential to work with business data (accounting, sales, production and others).
Section 3: Forms and Controls in VBA for Excel: Lessons 24 to 33
The userform is a small or large dialog window that you create and allows the user to submit values that will be used by your macros. To these userforms you will add controls (command buttons, text boxes, list boxes and others) and program them.