Visual Basic for Applications in Microsoft Excel – Advanced

Course Code: ASK7VA

Visual Basic for Applications in Microsoft Excel – Advanced

Have you ever wondered about the applications of VBA in Microsoft Excel and how it can be a good addition to sharpen your data analysis efforts? Our ‘Visual Basic for Applications in Microsoft Excel – Advanced’ course will equip participants with more profound knowledge of VBA. You will learn how to simplify complex processes involving pivot tables and charts while working with external data sources.

 

So who should learn the advanced level of VBA? VBA is applicable in a wide variety of situations and fields such as fintech, Big Data or logistics etc. Be it for deep learning of customer performance, product improvement strategies or process improvement, VBA helps to omit human errors by automating repetitive tasks needed for analysis and can also help to enhance the insights gained from collated data.

 

Given the wide applications of VBA, someone who is skilled in VBA will have no trouble gaining employment in our digitalized economy where data analytics plays a big part in improving business performances.  Interested learners for this course will need to know how to do basic programming, have fundamental knowledge of Macros and VBA and be comfortable with the application of Excel objects, variables, looping, functions and subroutines.

 

Visual Basic for Applications in Microsoft Excel - Advanced

 

In this 3-day ‘Visual Basic for Applications in Microsoft Excel – Advanced’ course, you will learn how to process text files, use arrays, use filters for data mining, create pivot tables with VBA, create customized charts and work with Microsoft Access from Excel VBA to add, retrieve and update records in a database.

 

Course Objectives – Visual Basic for Applications in Microsoft Excel – Advanced

At the end of the course, you will be able to:

  • Process text files
  • Declare an array and dynamically manipulate data with arrays
  • Process data with advanced filters
  • Create pivot tables with VBA
  • Create customized and advanced charts in Excel
  • Use Microsoft Access from Excel VBA to add, retrieve and update records in a database

 

Participants who are keen to build a stronger foundation in VBA can consider attending our 3-day ‘Visual Basic for Applications in Microsoft Excel VBA –  Fundamental‘ course.

  • Course Dates and Venue

    Course Duration: 3 day (24 hours), 9:00a.m. – 5:00p.m.

    CourseVenueOct-20Nov-20Dec-20Jan-21Feb-21Mar-21
    Visual Basic for Applications in Microsoft Excel - AdvancedFor in-house and customisation only, please contact us for more information.---

    September 2015

    SMTWTFS
    30
    31
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    1
    2
    3
  • Text File Processing

        1. Importing Text File
        1. Reading Text Files One Row at a time
      1.           Writing Text Files

    Arrays

        1. Declare an Array
        1. Declare a Multidimensional Array
        1. Filling and Retrieving Data from an Array
      1.           Using Dynamic Arrays

    Data Mining

        1. AutoFilter Using VBA
        1. Using Advanced Filter to extract a Unique list of values
        1. Using Advanced Filter with Criteria Ranges
      1.            Using Filter in place of Advanced Filter

    Combining and Separating Workbooks

        1. Separate Worksheets into Workbooks
      1.            Combine Workbooks

    Using VBA to Create Pivot Tables

        1. Introduction to Pivot Table
        1. Building Pivot Tables using VBA
        1. Advanced Pivot Table Features
      1.           Setting up Filter in Pivot Tables

    Creating Charts

        1. Charting in Excel
        1. Understanding the Global Settings
        1. Creating a Chart in Various Excel Versions
        1. Customizing a Chart
      1.           Creating Advanced Charts

    Working with Access from Excel VBA

        1. ADO vs DAO
        1. The tools of ADO
        1. Adding a Record to the Database
        1. Retrieving Records from the Database
        1. Updating Records to the Database
      1.            Summarizing Records
  • The course, Visual Basic for Applications in Microsoft Excel – Advanced, would require the individuals to have attended VBA in Microsoft Excel – Fundamental or have experience writing codes using VBA with basic understanding of objects, variables, conditionals and looping before attending VBA – Advanced.

  • “The trainer, Stella, step through the thought process after reiterating the key points. Very useful. Thanks. Stella is also clear, concise and motivating.” – Koh Chee Hiang; Hewlett-Packard Singapore (Private) Limited (04-06 Oct 2017)

    “Stella is very clear, very organised. Sharp in answering questions. Easy to understand.” – Lim Cheng Hui; JTC (04-06 Oct 2017)

Fees and Grants

$695.50 per participant (Inc. GST)

SDF subsidy: $2 per training hour. For more information on SDF, please refer to www.skillsconnect.gov.sg > Funding Mechanism.

Applicants may use SkillsFuture Credit for this course

Absentees will be charged the full course fee.

Registration

Click here to download registration form.

For Organisations on HRMS Learning Hub, you are strongly encouraged to use this platform for registration.

Apply Online

Visual Basic for Applications in Microsoft Excel – Advanced (ASK7VA)


All registration should be submitted at least 1 week before course commencement. Please call us directly to check on the class status.

A. Company Particulars

Company Name*

Company UEN*

Company Address*

Postal Code*

B. Training Coordinator / Contact Person

TC / Contact Person Name*

TC / Contact Person Email*

Contact Number*

Fax Number

C. Billing Information


Billing Contact Person Name*

Billing Contact Person Email*

Billing Contact Number*

Billing Fax Number

Business Unit (BU / SBU)

For Businesses or Government Agencies using the e-invoice system only.

Are you applying for SDF Grant?

Applicable to some select IT and Softskills Courses.

How did you get to hear about us?*

D. Participants

Participant 1

Course Start Date*

Course End Date*

Participant 2

Course Start Date*

Course End Date*

Participant 3

Course Start Date*

Course End Date*

Have more than 3 participants?

Attach their particulars here (PDF, Word .doc, Excel .xls)

I have Read and Agree to the Terms and Conditions


We do not sell or share your information with anyone else.

Share this course