Category Archives: PowerPoint

Dynamic Charts in PowerPoint – Self-refreshing Charts using VBA

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Technically in PowerPoint you can link an Excel file to a PowerPoint presentation. However that will not make the Chart dynamic, and sometimes it is hard to say when the Chart will be updated. But what if we want even more – what if we want the chart to refresh during a Presentation? I can imagine this can have many uses. Imagine wanting to present a series of dynamic slides in an Office environment, each slide showing latest charts from an updates Excel dashboard. In other cases you may want to prevent any linking out to Excel files and simply have a dynamic reference to your source file or files. Today we will explore how to create Dynamic Charts in PowerPoint using VBA.

See an example in this Youtube video:

Why not use Linked Charts?

Before we start I want to explain that you don’t need to use the approach explained below to simply link an Excel Chart to a PowerPoint presentation. If this is all you need, read this Support Office article. The purpose of this exercise is to avoid linking at all and make it entire flexible on your own preference when you want to update your Charts and even update the Excel files before doing so.

Creating Dynamic Charts in PowerPoint

Below is a step by step tutorial of how to setup the Charts and Macro:

Create the PowerPoint and Shapes

First we need to create a PowerPoint Shape that will be used as a placeholder to mark the location and size of our Chart. Click on image below for a reference.

PowerPoint with Placeholders for Shapes
Click to enlarge

Create an Excel Chart

Next we need to create and name an Excel Chart. This will be copy pasted as an Image to our PowerPoint to avoid linking and to assure the format is identical to how it is visualized in your Excel file.

Excel file with Chart to be copied to PowerPoint
Click to enlarge

Create the Dynamic Charts VBA macro

If you don’t know how to access your Developer tab you can proceed similarly as in Excel. Next create a new VBA Module and copy the following VBA code:

Let me break this down. Above first we defined the VBA Sleep function as we will use it as a delay mechanizm between updates. Next we define the CopyChartFromExcelToPPT function which basically does the following:

  • Open an Excel file defined with excelFilePath
  • Copies the chart chartName from the sheet sheetName
  • Pastes it into the ActivePresentation on slide dstSlide an the location shapeTop and shapeLeft and with the size of shapeWidth and shapeHeight

As you can see this is a generic function you can reuse to your own purpose regardless of the example usage below.

Auto refresh the Chart

Now let us create simple scenario using the generic function for copying an Excel Chart to PowerPoint:

  1. Run in Presentation mode
  2. Every second update the Excel Chart and update the TimeStamp
  3. Exit Presentation mode

Below VBA code does exactly that:

Use Cases of Dynamic PowerPoint Charts

For me a way to animate Excel Charts in PowerPoint present a new pallete of options to further push the boundries of what we can do with PowerPoint. Below a short list of ideas that can help you image how useful this can be:

  • Create a dynamic Office presentation with auto refreshing Charts from multiple Excel files
  • Create a button to manually refresh single or all Charts in your PowerPoint deck
  • Create an easy way to link your charts in PowerPoint to Excel files that might move (e.g. link to Excel files in same directory

Have more ideas? Raise a Question or let us meet on Reddit.

VBA Status Bar, Progress Bar, Sounds and Emails – Alerts in VBA

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 4.67 out of 5)
Loading...

Some VBA Macros run for ages – be it minutes or even hours. If we optimized our VBA Macro to the fullest there is often not much more you can do to change that. Usually what is irritating is waiting for VBA Macro code completion. Sitting by your desk is a waste of time – why not tell Excel/Access to let you know when the code has completed? Today we will explore such tools as the VBA Status Bar, VBA Progress Bar, Sound Notifications in VBA and Sending Email Alerts from VBA.

VBA Progress Bar

vba progress barProgress Bars are ubiquitous anywhere anything takes more than a couple of seconds to complete. So why doesn’t VBA have a native Progress Bar – beats me.

To add a VBA Progress Bar to your Visual Basic for Applications macro read my post here.

Excel Status Bar

vba status barThe VBA Status Bar is a panel that appears at the bottom of your Excel (or Access) Workbook. It is basically a Text Box to which you can display any non-blocking (non-modal) Message to your users.

Showing a Message in the VBA Status Bar

To show a message in the VBA Status Bar we need to Enable it using Application.DisplayStatusBar:

Hiding the VBA Status Bar

The VBA Status Bar when displayed, will remain displayed until ordered otherwise. It is best to hide and clean it after code execution completion:

Sound Notifications in VBA

What if you are away from your Screen, getting a Coffee or simply chatting with a colleague or getting on with our tasks stressed that we might miss that moment when our VBA Macro completes its task. Well why not introduce a Sound Notification? Let Excel or Access run a Sound to inform you that your Visual Basic for Applications macro has finally completed.

Playing a Sound in VBA

Playing a Sound in VBA is easy. Just add the below code snippet to a VBA Module:

It will play the Chimes.wav sound Once.

What if you don’t hear it? Running it once might obviously be not enough…

VBA Sound Alarm

The best approach is to create a VBA Sound Alarm that will run in a loop until we Turn it Off manually. What we will need is a Non-Modal UserForm as a pop-up to ask to stop the alarm.

First the macro code:

What happens here?:

  • I am measuring the execution time with the startTime and execTime variables
  • I am using the stopPlaying global VBA Variable to Turn Off the Alarm from our Alarm UserForm changes this value to True
  • I am displaying the AlarmForm with the Turn Off Alarm button

And now our AlarmForm code:

The result:
turn off vba sound notification

VBA Send Email Notification

Lastly when we are on the run and leaving our Workstation to process our VBA Macro remotely we might appreciate an email from Excel saying: Hey there! I just completed running the Macro!

You can find the code for the SendEmailFromOutlook function in my post here.

We might want to spice things up with maybe sending also the time it took to complete the macro:

Cool right? Makes the VBA Message Box hide in shame doesn’t it? The email should look like this:

vba email notification
VBA Email Notification

VBA FileDialog – Opening, Selecting and Saving files and folders

1 Star2 Stars3 Stars4 Stars5 Stars (8 votes, average: 4.38 out of 5)
Loading...

VBA File DialogOften in VBA we need to ask the users to select files or directories before we execute the actual functionality of our macro. Welcome to the VBA Open file dialog post. Today we will learn how to use the Application.FileDialog, to understand the various msoFileDialogFilePicker file dialog picking options and how to properly manage these dialogs.

Here is a simple example of a VBA File Dialog:

Application.FileDialog function

Before we start let’s understand the Application.FileDialog function.

The Application.FileDialog has the following syntax:

Parameter

MsoFileDialogType
An enumeration defining the type of file dialog to open. It has the following values:

Value Description
msoFileDialogOpen Open dialog box
msoFileDialogSaveAs Save As dialog box
msoFileDialogFilePicker File picker dialog box
msoFileDialogFolderPicker Folder picker dialog box

Properties and functions

FileDialog properties

Property Description
AllowMultiSelect Allow to select more than one file or folder
ButtonName Text displayed on the action button of a file dialog box
DialogType Change the MsoFileDialogType (see above)
Filter Set a file filter to filter file types user can select
InitialFileName The initial path to be opened e.g. C:\
InitialView The initial file view. Can be one of the following:

Value
msoFileDialogViewDetails
msoFileDialogViewLargeIcons
msoFileDialogViewList
msoFileDialogViewPreview
msoFileDialogViewProperties
msoFileDialogViewSmallIcons
msoFileDialogViewThumbnail
msoFileDialogViewWebView
SelectedItems Collection of type FileDialogSelectedItems with all selected items
Title Title of the Open file dialog window

Select files – msoFileDialogFilePicker

select file filedialogThe msoFileDialogFilePicker dialog type allows you to select one or more files.

Select single files

The most common select file scenario is asking the user to select a single file. The code below does just that:

Select multiple files

Quite common is a scenario when you are asking the user to select one or more files. The code below does just that. Notice that you need to set AllowMultiSelect to True.

Select folder – msoFileDialogFilePicker

select folder application.filedialogSelecting a folder is more simple than selecting files. However only a single folder can be select within a single dialog window.

Select folder example

The dialog below will ask the user to select a folder:

The msoFileDialogFolderPicker dialog allows you to only select a SINGLE folder and obviously does not support file folders

Open file – msoFileDialogOpen

file open application.filedialogOpening files is much more simple as it usually involves a single file. The only difference between the behavior between Selecting and Opening files are button labels.

Open file example

The dialog below will ask the user to select a file to open:

Save file – msoFileDialogSaveAs

saveas application.filedialogSaving a file is similarly easy, and also only the buttons are differently named.

The save file dialog will in fact not save any files! It will just allow the user to select a filename for the file. You need to open the files for reading / writing yourself. Check out my post on how to write files in VBA

Save file example

The dialog below will ask the user to select a path to which a files is to be saved:

The msoFileDialogSaveAs dialog does NOT support file filters

FileDialog Filters

One of the common problems with working with the Application.FileDialog is setting multiple file filters. Below some common examples of how to do this properly. To add a filter for multiple files use the semicolor ;:

Be sure to clear your list of filters each time. The FileDialog has its nuisances and often filters are not cleared automatically. Hence, when creating multiple dialogs you might see filters coming from previous executed dialogs if not cleared and re-initiated properly.

The open file dialog will in fact not open any files! It will just allow the user to select files to open. You need to open the files for reading / writing yourself. Check out my posts:

Do you really need Visual Basic for Applications macro for that?

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

Visual Basic for Applications (abbrv. VBA) is often misused causing issues with maintaining code, backward compatibility with older Excel version and other issues. Reason being is often that some of us don’t know, or better still, don’t make the minimal effort to learn ready available tools in Excel or other Office applications. From time to time I see some Excel macro that does the job of a Excel PivotTable or more often that of a simple MS Query (SQL). So I am dedicating today’s post to those struggling with their doubts on whether to open up the VBE (Visual Basic Editor).

More often than not, Excel users tend to create macros in situations where they could easily leverage an Excel formula, PivotTable, AddIn or other ready solution instead of writing VBA Code. So let us ask ourselves today:

Should I use a Visual Basic for Applications macro for that?

Do your research

Google your problem!
Google your problem!
What is the reason you consider resorting to VBA? Did you really do your research and you are sure there are no native Excel functions that will do the job? Too often I see people using VBA Macros because they didn’t do a 10 min Google search to see if there are Excel formulas that will do the job.

Does Excel have a built in tool for that?

Excel is the best entry level Data Analysis Tool out there. Are you sure you know all the tools natively available in Excel? Check the Data ribbon tab first!

None of them fit the issue?

Can you use an Array Formulas (CTRL+SHIFT+ENTER)?

Often what we need is to create a complex formula that will run on an entire array of cells or rows, not just a single cell.
Are you aware of Array Formulas? They allow you to do operations on whole arrays (rows/columns) of Excel data. A simple example below to demonstrate the power of Array Formulas:

The Table

A
2
5
11
12

The Array Formula (type and hit CTRL+SHIFT+ENTER):

=COUNT(IF(A1:A10 > 10;A1:A10))

The result (number of cells with value above 10):

2

As you can see the formula above can easily count all cells between A1:A10 of value higher that 1. Nice right? That is just the peak of the iceberg of what Array Formulas can do. Check out this Array Formula tutorial to learn more.

Is there an App or AddIn for that?

I know some of us like to be creative and look to code stuff their selves. However, I do encourage that you check for existing solutions:

No alternatives – need to use VBA

Ok so there doesn’t seem to be any alternative to writing a VBA macro. Where to start before you begin?

Need help / support

If you stumble across issues when developing your VBA code, feel free to post your questions (do some research first!) on one of these forums:

  • StackOverflow – in my opinion the best technical forum out there
  • AnalystCave.com forums – got a question to one of my posts or tools? Feel free to reach out!
  • MSDN Excel forum – the official Excel forum by Microsoft
  • MSDN VBA forum – the official VBA forum by Microsoft
  • MrExcel forum – no longer the power house it used to be, but still hosts a bunch of Excel users that share their knowledge and can help
Words of advice when posting questions: most forums have a policy to ignore (at best) questions which don’t show that you made any effort to resolve the problem yourself. Therefore when posting a question remember to follow the above steps or at least:

  • Google your question
  • Search the Forum database for similar questions.. you would be surprised how many people had the same problem as you did
  • Include a summary of your research in the question
  • Include your sourcecode (line that throws error even better) or be sure to explain the issue in detail

On the other hand don’t:

  • Post questions showing negligence to details and quality – lots of typos, misuse of formatting etc.
  • Write general statements that nothing works or post questions with general titles like – Excel Problem or VBA Issue
  • Demand that someone write the code for you (really, it happens)

Why am I discouraging the use of Visual Basic for Applications?

Don’t get me wrong – my blog is ALL about VBA. It is not that I discourage the use of VBA, in many cases you won’t have an alternative. It is just that in so many cases we are incorrectly lazy enough to avoid doing proper research that we actually spend more time writing unnecessary VBA scripts that copy often built in Excel capabilities. So many enterprises rely on VBA scripts that often do nothing more than a simple Pivot Table, Array Formula or PowerQuery/MS Query could do in seconds.

So seriously Help Yourself and do your research first. You will appreciate VBA when you will really need it.

Excel VBA Option Explicit – explicit variable declaration

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

The Excel VBA Option Explicit statement is such useful feature in Excel VBA, it is strange, this is the first time I am writing a post about it. It proves incredibly useful in preventing typos and other stupid mistakes when developing Visual Basic for Applications macros. But what does it actually do?

What does Option Explicit do?

The VBA Option Explicit statement can either:

  • Forces an explicit declaration of all variables in a VBA Module
  • Allow the implicit declaration of variables in VBA Modules

However, the first question most people have is…

What is explicit and implicit variable declaration?

Explicit variable declaration in VBA is the declaration of a variable with the use of the VBA Dim statement:

Dim x as Long 'Explicit declaration
x = 1

Implicit variable declaration on the other hand is the opposite – the usage of a variable without any preceding declaration. The VBA Compiler will create a variable that has been used even if it was not preceded with an explicit declaration:

x = 1 'Implicit declaration

Option Explicit usage

Now that we know what implicit and explicit declarations we can ponder on a second on why we would want to force explicit variable declaration. Well, for a number of reasons:

  • To prevent variable name typos
  • Clean up VBA Code
  • Optimize VBA performance
By default all implicit variables are Variant types. Variants are much less efficient than other data types (Longs, Integers, Strings etc.). Read more on VBA Performance here

Using Option Explicit

Option Explicit 'Declare that all variables are to be explicitly declared

Sub Main
 Dim x as Long
 x = 1 'OK!
 y = 10 'ERROR!
End Sub

option explicit errorWhat happens when you try to declare a variable implicitly. Believe me, it is better to fix Variable not defined errors than scramble to find variable name typos.

Turn on Option Explicit by default

For me the Option Explicit statement is a must requirement. I turn it on by default in all my macros. Even when using Variant variables.

turn on option explicitHow to turn on Option Explicit by default for all VBA Code Modules? Go to Tools->Options.... In the Options window select Require variable declaration. That is it! Similarly you can turn off the addition of the Option Explicit statement in all your VBA Code Modules by unchecking this option.