Tag Archives: VBA

VBA Paste Excel to PowerPoint

VBA Paste from Excel to PowerPoint

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

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:

Excel random number generator

Random Number Generator in Excel / VBA

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

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:

run vba on all files in folder

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

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

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

Unhide Sheets in Excel

How to unhide sheets in Excel? Unhide all Sheets in Excel VBA

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

To unhide Sheets in Excel you need to right click on the Worksheet tab and select the Worksheet you want to unhide from the Unhide Window.

I will start by showing a way to manually unhide an Excel Worksheet using the Excel Unhide Window. Then I will show you a few tricks of how to unhide all Sheets in Excel using VBA. Lastly

Unhide Sheets in Excel

To unhide a Hidden Worksheet in Excel proceed as follows:

  1. Right click on a the Worksheets tab
  2. Click the Unhide button in the opened tab
  3. Select the Worksheet you want to unhide and click Ok

Unhide hidden Sheets in Excel

Unhide Sheet using VBA

To Unhide a single Worksheet in Excel using VBA we need to use open the Visual Basic Editor. To do this quickly simply us this Excel Keyboard shortcut ALT+F11.

You can type the below in the Immediate window and hit Enter:

Where NameOfWorksheet is obviously the name of the Worksheet you want to Unhide in Excel.

Hide Sheet using VBA

If you want to Hide a Worksheet in Excel using VBA you can modify the code above. Instead of using xlSheetVisible just use xlSheetHidden:

This will hide the worksheet named NameOfWorksheet.

Unhide All Sheets in Excel using VBA

To unhide all Sheets in Excel we must revert to VBA as there is no other way to select multiple worksheets using the built in Excel Unhide window. Again let us open the Visual Basic Editor by using the Excel Keyboard shortcut ALT+F11. Next in the Immediate Window let us type:

Unhide all Sheets in Excel using VBA
Unhide all Sheets in Excel using VBA

Below the same code as above but spread across multiple lines. Let us run through this:

Unhide all Sheets by Name

In many cases you don’t necessarily want to Unhide all Sheets in Excel. Instead you might want to Unhide only a subset of the Hidden Worksheets using a name pattern.

Assume you want to Unhide all Worksheets that fall into a certain pattern where part of the name can be any sequence of characters. For this we can amend the code above using the VBA Like operator:

Similarly as above we can wrap it up to a oneliner to run in the Immediate Window:

The code above will unhide all Worksheets which name starts with Hidden and suffixed by any number of characters e.g. numbers like in the example below:
Hidden worksheets Excel

What is happening is using the VBA For Each loop we are iterating through the VBA Collection of Worksheets. When a certain Worksheet name matches our VBA Like function statement we make it visible.

Button to Hide/Unhide Sheets

Lastly to learn how to Unhide Sheets in Excel we will sum up what we have learned and make a simple VBA UserForm to be able to quick manage visibily across the entire list of Excel Worksheets.

Create the UserForm

First you need to create a VBA UserForm with a VBA ListBox and a VBA CommandButton:
Manage Sheets UserForm

Program Initialize and Button Click

If you named your objects correctly past the following code into the VBA UserForm source code:

Run the UserForm

To put our code to the test all we need to do is create and show the ManageWorksheets VBA UserForm. The following VBA Procedures code needs to created in a VBA Module:

When executed this is how it will look like:
Unhide Multiple Sheets in Excel

VBA Tricks

VBA Tips and Tricks

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

Instant Print in the Immediate Window

Use the “?” preceding a line of code that can evaluate to a value in the VBE Immediate Window. Hit Enter to evalute the code in the next line. See example below:

VBA Trick Use question mark for instant Debug Print
VBA Trick Use question mark for instant Debug Print

This is a VBA Trick I was not aware for a lot time but saves a lot of time when debugging code or trying to evaluate a single line or bit of code without needing to debug a whole functions or procedure.

If you are not familiar with the Immediate Window read my VBE Project tutorial

Evaluate formulas shortcut

One of my favorite VBA Trick is using the evaluate function using square brackets “[]”. If you want to read more read my VBA Evaluate article.

Evaluate works both on:

  • Evaluation of Named Ranges
  • Evaluation of Excel Worksheet Functions

Evaluating Named Ranges in VBA

Here’s an example of Named Range evaluation in VBA:

VBA Trick: VBA Evaluate Ramed Range
VBA Trick: VBA Evaluate Ramed Range

Evaluating Worksheet Functions in VBA

Here’s an example of Worksheet Function evaluation in VBA:

VBA Trick VBA Evaluate Worksheet Function
VBA Trick VBA Evaluate Worksheet Function

This is one of the most useful trick as VBA does not allow you to otherwise easily do a lookup of data is a worsheet.

Remeber to always use the “,” list separator when defining the arguments as VBA is not not dependent on your system country settings even if you separate arguments in regular Excel worksheet functions.

Use VBA. to view all VBA Built in functions and constants

Often you stumble not being able to remind yourself of that one useful VBA Function and need to search the function online. A great VBA Trick is to type in “VBA.” and then you will be provided with a list of available VBA Functions and Constants.

VBA Trick Use VBA to list all built in functions and constants
VBA Trick Use VBA to list all built in functions and constants

User Defined Functions

Excel has a multitude of functions, half of which you probably don’t know. However, what happens when you lack a particular function or want to save time bundling up common functions? Let me introduce you to the little known world of User Defined Functions (UDF).

Simple UDF

A simple UDF can be found below:
VBA Trick Simple UDF

Advanced UDF

Let us not try a more advanced UDF showing how powerful it can be. First I will create a simple table:

VBA Trick UDF Table
VBA Trick UDF Table

Now what I need is to somehow get a number of matches or these records against various criteria e.g. sex, sales, age, country. One way would be to go for a Pivot Table but sometimes a simple UDF can be more useful especially if we want to embed this value in your calculations.

So I will create a UDF that takes these arguments and provides the number of matches:

Now we can use the UDF Function in Excel:

VBA Trick Complex UDF
VBA Trick Complex UDF

Speeding up your VBA Macros

Your macro running slow? A lot of CPU overhead goes into updating your screen in case and recalculations of formulas impacted by your macros. With a simple VBA Trick you can turn off ScreenUpdating and Calculations until your macro is done.

What to do when your macros needs to run for a long time and you need to update your screen every now and then? Use the DoEvents procedure to refresh your screen only in selected intervals.
Read here how learn of more ways of optimizing your VBA performance.