Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

You must enable JavaScript to be able to view our online training courses (the tabbed menu below doesn't work otherwise).

Collections and Loops

Exercise 6.01

The aim of this exercise is to alter the formatting of multiple chart objects in a worksheet by looping over the appropriate collection.

Files Needed

Click here to download the file needed for this exercise.

Instructions

Extract and open the Formatting Charts.xlsm workbook:

The workbook contains a single worksheet with a collection of twelve ChartObjects, some of which are shown here.

Open the VBE, insert a new module and create a subroutine called Different_Chart_Styles.
Declare an Integer variable in the subroutine.
Use the variable to create a For Next loop which counts from 1 to the count of the ChartObjects collection on Sheet1.

As the worksheet contains twelve chart objects you could simply construct the loop to count from 1 to 12.

Within the loop, add an instruction to change the ChartStyle property using the value of the integer variable. Use the example below as a guide:

Sheet1.ChartObjects(i).Chart.ChartStyle = i

Run the subroutine and check the results in Excel:

Each chart will have a different format.

Create a new subroutine called Same_Chart_Style.
Declare a variable which can hold a reference to a ChartObject object.
Use this variable to write a For Each loop which processes the ChartObjects collection on Sheet1.
Within the loop, add an instruction which changes the ChartStyle property of the ChartObject referenced by the variable. Set the chart style to any number between 1 and 12:

co.Chart.ChartStyle = 4

Run the subroutine and check the results in Excel:

This time, each chart will have the same formatting.

Save and close the workbook.

Answer Files

Click here to download a file containing a suggested answer.

This page has 0 threads

Add a new post

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

Site map

Terms & Conditions

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

MODULES

EXERCISES

Exercise 6.01

Files Needed

Instructions

Answer Files

Head office

London

Manchester