Tactical Logistics Solutions with Excel and VBA program
- 13 Nov 2018 – 14 Nov 2018, Intake: 1
Modern enterprise has strategic systems and applications to handle the by and large, regular and frequent operations and transactions. In cases when not applicable, professionals can face crunch time dealing with manual processes in the absence of appropriate last mile applications. This is akin to cargo delivered by air/sea freight, but no haulier available to complete the last mile delivery.
Are you facing crunch time when working on yearly or ad hoc reports which are not supported by existing applications? Are you reverting to manual processes due to business change and system update cannot catch up? Do you have to perform intensive analysis to meet tight timelines for project testing? These are just a few examples where Excel and VBA programs can serve as tactical solutions to automate intensive and laborious processes.
This workshop is specially designed for professionals working in the transport and logistics industry. It is an interactive workshop in a computer laboratory where each student uses a desktop with pre-installed program and exercises to facilitate hands-on learning.
The course contents are developed by logistician using real-life examples and case studies. Participants can apply the new skills immediately through the sample exercises. This course thus provides dual benefits – participants can learn new Excel VBA skills directly applicable to overcome common issues faced in the workplace.
Note: This is an advance level course. Participants are expected to be familiar with Excel functions, have some experience on using Excel macro and possess business analysis skills. This is not an introductory course on MS Excel. The version used will be MS Office Excel 2016.
MS Office Excel 2016.
What You will Learn
At the end of the programme, participants will :
- Understand the limitations of macro recorder
- Navigate around the Visual Basic Editor
- Conceptualise business process and map to programmable system events
- Apply frequently used VBA functions for Excel automation
- Implement decision and data processing logic
- Logistics Account Managers and Executives
- Operations / Warehouse / Distribution Supervisors and Officers
- Customer Service Officers
- Material Planners and Buyers
- Quality Assurance Officers
- Inventory Management / Control Officers
- IT Managers
(payable to TLA)
Payment will only be required after the course is confirmed.
(The status of the course would usually be advised one (1) month before the course start date.)
Payment mode (retail payments facilities are not available) :
Overseas – Telegraphic transfer
Local – Cheque or bank transfer
2 days : 8.30am to 5.30pm
Jorge Ng is a project manager with extensive experience in customer logistics and IT project management. Graduated with a Master of Engineering (research on computer architecture) from the National University of Singapore. Previously he worked in Nokia as the Customization Development Manager and taught the subject on IT for Supply Chain at Republic Polytechnic.
He led several logistics and IT deployment projects. Using Excel and VBA programs, he developed several applications to assists users in automating logistics operations and achieved productivity. Just to illustrate a couple of applications, one that reconcile several product launch plans and another that find customer record text files base on a name list. These applications reduced operations from five days down to a half day of work.
He has two endeavours now, mainly managing project management offices with the DBS bank and a small side business partnership retailing premium seafood, which he deploys web application powered by Python and Google App Engine at almost zero cost of ownership.
Central Plaza (above Tiong Bahru MRT)
A 30-minute open-book assessment will be conducted at the end of the seminar to ensure participants’ understanding on the topics. A Certificate of Achievement will be awarded upon successful completion of the assessment and attainment of 75% attendance.
This is a two-day course with the syllabus categorized into sections. Each section has a particular theme to offer a structured learning.
Section 1 : About Excel Macro
- Overview on Excel Macro
- Excel macro codes and Visual Basic Editor (VBE)
- Excel macro recorder limitation
- Hands-on session : Record an Excel macro
Section 2 : About Visual Basic for Application
- Overview VBA programming
- Jump start Excel VBA programming
- Hands-on Session : Start Writing sub procedures and declaring variables
Section 3 : Conceptualising the Solution
- Think about Current Processing
- Break down into steps and map to programmable events
Section 4 : Accessing Excel Objects
- Hands-On Sessions : Cell Manipulation, Worksheet Manipulations, Workbook Manipulations
Section 5 : Use Events to Trigger Code Execution
- Hands-On Sessions : Worksheet events and Workbook events
Section 6 : Implement Decision and Data Processing Logic
- Hands-On Sessions : Find last row/column and useful properties, Number operations, Text operations, Date and Time operations, Set decision logic and conditions, Iterative processing and control conditions
Section 7 : Deploy Macro using Add-in
- Hands-On Sessions : Create an Add-in
- Deploy Add-In as tactical framework to control macros
Section 8 : Automated Solutions Demo
- Case Study
This is a hands-on course where participants will have intensive practical session using MS Excel and VBA.