VBA in MS Excel - advanced level

Databases in Excel spreadsheet
    Duration
    Start hour
    Level

      Visual Basic for Applications in MS Excel - Databases in Excel spreadsheet

      Comprehensive training at the Advanced Level in the use Visual Basic for Applications in Microsoft Excel

      For Whom?

      This course is for people who have basic knowledge and skills in VBA workbook and worksheet programming and need to automate their work in data exchange with database.

      For Whom?

      Full program

      Training objectives

      Participants will receive additional sets of ready-to-use procedures.

      Skills

      Acquiring the ability to:

      • create their own mechanisms for uploading and downloading data from various databases (eg. MS Access, SQLServer, Oracle)
      • create an efficient and automated databases in worksheets
      • operate on array variables (eg to create efficient algorithms for millions of records processing).
      Audience profile

      This course is for people who have basic knowledge and skills in VBA workbook and worksheet programming and need to automate their work in data exchange with database.

      Am I ready for Excel VBA trainings? Do I need any programming skills?

      We need to put this straight – if you had enough technical capabilities and determination to learn Excel with all of its formulas, logic, and complexity, you are definitely able to be successful in the field of VBA. As mentioned above, no programming skills required. There are basically two main pre-requisites for Comarch VBA programming trainings: your honest interest to learn VBA and macros, and a little higher than basic (best if intermediate or advanced) knowledge in Excel. The courses are intended for people who work in Microsoft Excel on a daily basis and would like to take their skills to the next level. If you use Excel in your professional life, you probably already know what macros are. Now it’s time to learn their dictionary and grammar, in other words – the building elements of the story.

      Duration
      3 days,  8 teaching hours each.
      Training delivery method
      During the training, 80% of the time is carried out in the form of a practical lecture, in which the instructor presents ready-to-use examples and solutions that the participant can then use in their own macros. The remaining 20% of the training time is allocated to the independent work of the participants. The exercises are prepared in such a way as to consolidate the presented material and encourage creative activity.
      Theoretical and practical knowledge training course VBA
      Playing with dates
      ·       Date data type
      ·       Valid date and time range in Excel and VBA
      ·       VBA functions related to date and time
      ·       Check if the cell content is a date/time
      ·       Filtering by date/time
      Communicating with the macro users

      ·       Dialog windows that are ready to use
      ·       How to build your own dialog windows
      ·       Adding window controls
      ·       Handle control's events
      ·       How controls interact with each other
      ·       Make your dialog windows looking proffesional
      Procedural programming

      ·       What is procedural programming
      ·       Macros could communicate with each other by arguments
      ·       global variables/constants vs macro arguments
      Sorting data could be confusing

      ·       Sorting by using Autofilter
      ·       Sorting with the Sort method
      ·       Sorting using Sort object
      ·       Applying custom sort
      ·       Custom sort with a custom list
      Create Excel charts programmatically

      ·       The ways of creating a chart
      ·       There are many objects on a chart
      ·       The chart objects has hierarchy
      ·       Editing chart elements (axes, data series, data labels and others)
      ·       Set size and position of the chart in relation to cells position
      Automating Pivot tables creation

      ·       How to create a pivot table
      ·       A pivot table is composed of many objects
      ·       Setting the pivot table layout
      ·       Choose a function to do calculations
      ·       Applying filters
      Development path
      If you have attended the basic and intermediate trainings of Visual Basic for Applications in Microsoft Excel or you already have experience writing macros for the Excel application, check if you will not find the higher level courses useful (Expert).

      Similar trainings

      Consultant:

      Sylwester Woźniak
      Sylwester Woźniak

      sylwester.wozniak@comarch.com

      +48 12 687 78 11

      +48 608 646 465