Tag Archives: VBA

VBA Timer: Create a Stopwatch in Excel VBA

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

In this post we will explore the various ways we can set a Timer in Excel VBA as well as how to build your own VBA Stopwatch. Setting timers can be extremly useful if you want to run code as specific time intervals. On the otherhand a practical example can be a VBA Stopwatch which can be useful to measure time elapsed directly in a Excel spreadsheet.

VBA Timer

The most simple example of a VBA Timer can be made using the VBA Timer function:

VBA Timer with Hours, Minutes and Seconds

The above is very useful if you want to measure time elapsed in Seconds, if you want to calculate time elapsed in Hours, Minutes and Seconds you can simply use the VBA Now function with text formatting using VBA Format instead:

VBA Wait

In case you don’t want to measure time elapsed and instead set an interval or delay between code execution you either use the VBA Sleep or VBA Wait procedures like below. Remember that the VBA Sleep function is not a native VBA function and needs to be declared.

VBA Alarms and Scheduling

Another VBA Timer scenario is scheduling procedures to run at a specific time or after a specific time interval like an alarm clock. To do this we use the Excel VBA OnTime procedure:

You can use the VBA OnTime function also to schedule code execution after a specific duration of time. The below will save the current file after every 5 min.

VBA Stopwatch

Using the VBA OnTime function we can also create a simple Excel VBA Stopwatch:
VBA Stopwatch in Excel
As you can see in the example above I created a button that launches a Start / Stop sequence. When the stopwatch is running it increments the TIMESTAMP cell (a named cell). You can use the code below to achieve this. Remember to select your named range as well as to connect your button to the StartStop procedure.

What happens above? When you hit the button the AddSecond procedure will be set to run within a second and then automatically sets itself to run in the next second after updating the timestamp. The next hit of the button will turn of the schedule. This approach may see some slight delays over a longer duration of time (as we are running code before the schedule), however, it should be good enough.

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 Paste from Excel to PowerPoint

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

In this post we will explore how VBA paste from Excel to PowerPoint objects such as a Range, Chart or other element. Below you will find working code snippets. We will learn also to modify this routine to address different VBA Copy Paste from Excel to PowerPoint.

VBA Paste Range from Excel to PowerPoint

We will start with an example of VBA Paste Excel Range into PowerPoint as Picture as this is the most typical scenario. For this I created a custom function called CopyFromExcelToPPT:

What does the VBA Function do? In short you need to provide the following parameters:

  • excelFilePath – full file path to the Excel from which you want to copy a VBA Range
  • sheetName – the Sheet name from which you want to copy
  • rngCopy – the VBA Range you want to copy
  • dstSlide – the number of the slide (starting at 1) to which you want to copy the Range
  • shapeTop Optional. The Top position in pixels of the new pasted Shape
  • shapeLeftOptional. The Left position in pixels of the new pasted Shape

Let us use this function in the following scenario. We want to copy range A1:B4.
VBA Paste Excel Range to PowerPoint as Picture
Let us use our function above for this scenario

VBA Paste Chart from Excel to PowerPoint

Now an example of VBA Paste Excel Graph into PowerPoint as Picture as this is also a useful case. For this I created a custom function called CopyChartFromExcelToPPT:

Again let us use it on the example below where we want to copy a Chart from a Excel Workbook to PowerPoint:
VBA Paste Excel Chart to PowerPoint as Picture
Example execution of the VBA Function below:

If you want to place the Chart at a specific place use the shapeTop and shapeLeft arguments. The below will place the chart at 10 pixels from the Top and 100 pixels from the Left.

Changing Height / Width of pasted elements

In the examples above we didn’t change the Width and Height of the pasted Range or Chart. To do this use the adjusted functions below:

Random Number Generator in Excel / VBA

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

We will explore the options to create your own random number generator in an Excel Worksheet or in VBA (Macro). You can generate randoms in 2 ways:

  • Using Excel functions i.e. using the RAND or RANDBETWEEN functions
  • Using VBA (Visual Basic macro) using the RANDOMIZE and RND functions

Random Number Generator using Excel functions

To create a random number in Excel you have 3 options:

  • Numbers between 0-1 using RAND function:
    Excel RAND function
  • Numbers between 2 whole numbers (lower and upper boundry, inclusive) using RANDBETWEEN. Below example for numbers between 0-100.
    Excel RANDBETWEEN function
  • Any decimal number between 2 numbers using the RAND function. Simply follow the pattern below (replace LOWER_BOUNDRY and UPPER_BOUNDRY with your values):
    . Example below generating any decimal numbers between 0-100 e.g. 1.5.
    Excel RANDBETWEEN function for any numbers

Remember that the RAND and RANDBETWEEN functions are volatile, meaning they will be recalculated on any change to the worksheet regardless if it affects the formula. This may mean you will see constant changes to these numbers. In case it affects your performance be sure to replace your random numbers with static (copy paste as values) or generate them using VBA.

Random Numbers using VBA functions

To generate random numbers in VBA you need to use 2 functions:

  • Randomize – that initializes the Rnd function with a provided seed. If you leave the argument blank it will use the actual system timer value. If you provide a certain number e.g. 10 you will always get the same sequence of random numbers. Why? Because computers use pseudo random number generators.
  • Rnd – function that generates the actual random decimal numbers between 0-1.

Below a simple example:

VBA Generate whole numbers

To generate whole numbers similarly like the RANDBETWEEN Excel function we need to use the VBA CInt function to convert the decimal number to an Integer:

The above is limited to numbers starting at 0 up to the upper boundry (above 100). We can adjust the lower and upper boundries adjusting the formula above:

The above will generate numbers between 5 and 100.

VBA Generate decimal numbers

Using similar approach as above and removing the VBA CInt function we can generate decimal numbers between any 2 given numbers:

VBA Run Macro on All Files in a Folder / All Worksheets in a Workbook

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

A very popular Excel automation scenario is the need to a VBA run macro on all files in a folder or running VBA on all Worksheets in an Excel Workbook. This is a very typical case where you process similar data dump files and want to extract data or transform the workbook. In this post I will provide ready code snippets to address these scenarios and walk you through what happens.

VBA Run Macro on All Files in a Folder

To run macro on all files in folder use the code snippet below. The code will do the following:

  • Open a VBA FileDialog in the current workbook path and ask for you to select a folder where all files are stored
  • It will open a separate Excel process (Application) and then open each file one by one
  • Replace the YOUR CODE HERE section with any code you want to run on every opened workbook
  • Each opened workbook will be closed w/o saving

To make it more simple currWb and currWS represent the ActiveWorkbook and ActiveWorksheet whereas wb represents the newly opened Workbook from the selected folder.

There is also built in simple progress tracking via the Application StatusBar.

VBA Run Macro on All Files in Subfolders

A scenario of the above case when you want to run a macro on all Excel files in a folder is also traversing all subfolders to run your macro. The below is an extension of the above and utilizes a slightly modified version of the TraversePath procedure from here.

The below is almost identical to the above, however, notice the global variable fileCollection. This will be used to first store all file identified in subfolders and only after used to run all macros on files stored in this VBA Collection.

Run VBA on All Worksheets

To run macro on all Sheets in Workbook you need to can use the code snippet below. Here is a walkthrough of the code:

  • Opens each worksheet in ActiveWorkbook that isn’t the ActiveSheet. This clause is to avoid running on Worksheet on which macro was activated assuming this is a working sheet, feel free to remove the If clause if needed.
  • Replace the YOUR CODE HERE section with any code you want to run on every opened Worksheet