Save a Workbook as a Single PDF Using VBA

From our last post we learnt how to convert an entire workbook to a single PDF manually. If you want to know how, then please check this post.

Convert an Entire Workbook to a Single PDF File

Today I’m going to teach you how to do the same thing using VBA. So you can use this subroutine inside your VBA applications where necessary. Now let’s start developing the code. First we need to declare a few variables.

We need to loop through all the sheets of the workbook. So we need to declare one variable as worksheet.

Dim WS As Worksheet

Then we need an array to assign sheet names.

Dim SheetNames() As Variant

PDF file name will be assigned to a string type variable.

Dim PDF_FileName As String

In addition to those variables, let’s declare two more variables of type integer. One is to hold the number of sheets. And other variable is to use as a counter inside for next loop.

Dim NumberOfSheets As Integer Dim Counter As Integer

Now we have declared all the required variables. After variable declaration we can calculate the number of sheets inside the workbook as our first step.

NumberOfSheets = ThisWorkbook.Worksheets.Count

Now we know the upper bound of our SheetNames array. So we can size that dynamic array using redim statement.

ReDim SheetNames(1 To NumberOfSheets)

As our next step we can loop through all the sheets of the workbook and assign name of each worksheet to the SheetNames array. We can do it as follows.

Counter = 1 For Each WS In Worksheets      SheetNames(Counter) = WS.Name      Counter = Counter + 1 Next WS

Now let’s assign a name to our PDF file. You can give any valid name to the PDF file.

PDF_FileName = "PDf file name here"

Next we use SheetNames array to select all the sheets.

Sheets(SheetNames).Select

Then we can convert all the sheets to one single PDF file as follows.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "D:\Work\Create PDF\" & PDF_FileName & ".pdf", Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ False

You should replace "D:\Work\Create PDF\" with path of your folder where you need to save the PDF file. Or else you can assign the folder path to a variable and then use that inside the code like this.

Dim FolderPath as string FolderPath = "D:\Work\Create PDF" ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ FolderPath & "\" & PDF_FileName & ".pdf", Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ False

Here is the complete subroutine.

Sub ConvertWorkbookToSinglePDF() Dim WS As Worksheet Dim SheetNames() As Variant Dim PDF_FileName As String Dim NumberOfSheets As Integer Dim Counter As Integer NumberOfSheets = ThisWorkbook.Worksheets.Count ReDim SheetNames(1 To NumberOfSheets) Counter = 1 For Each WS In Worksheets      SheetNames(Counter) = WS.Name      Counter = Counter + 1 Next WS PDF_FileName = "PDf file name here" Sheets(SheetNames).Select ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "D:\Work\New Post 2\" & PDF_FileName & ".pdf", Quality:=xlQualityStandard, _ IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _ False End Sub

Convert an Entire Workbook to a Single PDF File

Did you know that you can convert an entire Excel workbook to a single PDF. In this post I will teach you how to do that. First open your excel workbook. And the click on the “File” menu.

Then click on the “Save As” and browse for the folder.

Save As dialog box will open like this.

Give a suitable name for the PDF file from the file name field. And select PDF from the save as type field.

When you select the PDF, few more options will be available at the bottom of the Save As dialog box. Click on the options button.

When you click on the Options button excel will open Options window. Select entire workbook option from “Publish what” section (Default setting is Activesheet(s)). Then click OK. Options window will be closed.

Now click on the “Save” button of the “Save As” dialog box. Excel will convert all the tabs in the Excel workbook to a single PDF file.

VBA Int function

In a recent post we learnt a useful VBA function call Fix function. Today I’m going to explain about very similar function. Name of this new VBA function is Int. Like Fix function this also returns the integer part of the number. But there is a small difference between these two functions. These two functions behave slightly different when dealing with negative numbers. If there is a decimal place in a negative value then Fix function will return the first negative number greater than the value.

Ex -
Fix(-29.1) will return -29

If there is a decimal place in the negative value then Int function will return first negative number less than the value.

Ex -
Int(-29.1) will return -30

However both functions treat positive values in same manner.

Now let’s consider this below subroutine. We can get a clear idea about VBA Int function if we run it.

Sub IntFunctionExample() Dim SampleValues(7) As Double Dim i As Integer SampleValues(0) = 0.32 SampleValues(1) = 5 SampleValues(2) = 7.1 SampleValues(3) = 7.8 SampleValues(4) = 0 SampleValues(5) = -15.1 SampleValues(6) = -15.9 SampleValues(7) = -30 For i = LBound(SampleValues) To UBound(SampleValues)      Debug.Print Int(SampleValues(i)) Next i End Sub

So if we run the above code it will show us below result in immediate window.

0
5
7
7
0
-16
-16
-30

Compare number arguments of the function and return values to understand how Int function works.

How to Check If a Value Has Decimal Places

In my last post I introduced you a useful VBA function call Fix. Today I’m going to show you how you can use that function to check whether a value has decimal places or not. In other words how to check whether a value is an integer/long or not.

I will explain this using an example. Let’s consider this array.

Dim SampleValues(4) As Double

SampleValues(0) = 5
SampleValues(1) = 5.95
SampleValues(2) = 20
SampleValues(3) = -13.7
SampleValues(4) = -2

So now let’s try to find out whether each value in this array has decimal place or not. We can do it like this.

Sub CheckForDecimalPlaces() Dim SampleValues(4) As Double Dim i As Long SampleValues(0) = 5 SampleValues(1) = 5.95 SampleValues(2) = 20 SampleValues(3) = -13.7 SampleValues(4) = -2 For i = LBound(SampleValues) To UBound(SampleValues)      If Fix(SampleValues(i)) = SampleValues(i) Then          Debug.Print SampleValues(i) & " - No decimal places"      Else          Debug.Print SampleValues(i) & " - has decimal places"      End If Next i End Sub

In above subroutine Fix function is used with an if statement. Let’s consider the following line.

If Fix(SampleValues(i)) = SampleValues(i) Then

So if we take the value 5 then Fix(5) equals to 5. Then 5=5. So above if statement becomes true. If we take the value 5.95 then Fix(5.95) equals to 5. But 5 is not equals to 5.95. So in that case above if statement becomes false.

That's how you can use Fix function to check whether a value has decimal places or not. So If we run above code we will get following result in the immediate window.

5 - No decimal places
5.95 - has decimal places
20 - No decimal places
-13.7 - has decimal places
-2 - No decimal places

VBA Fix Function

Today I'm going to explain you another very useful VBA function. It is Fix function. You can use this function to get the integer part of a numerical value. For an example, if you parse value 102.5 to the Fix function it will return 102

Here below is a sample subroutine from which you can get a clear idea about how this function works.

Sub FixFunctionExample() Dim SampleValues(4) As Double SampleValues(0) = 0.25 SampleValues(1) = 5.78 SampleValues(2) = 100.5 SampleValues(3) = -25.8 SampleValues(4) = -30.2 For i = LBound(SampleValues) To UBound(SampleValues) Debug.Print Fix(SampleValues(i)) Next i End Sub

If you run the above code, you will get following result in the intermediate window.

0
5
100
-25
-30

Change Font Color in VBA Editor in MS Excel

Did you know that you can change font colors in VBA editor. We can find various text types inside the VBA editor. These different text types have different font colors in default. Here is the complete list of text types you can find inside the VBA editor.

• Normal Text
• Selection Text
• Syntax Error Text
• Execution Point Text
• Breakpoint Text
• Comment Text
• Keyword Text
• Identifier Text
• Bookmark Text
• Call Return Text

In default, these various text types have different colors. However we have an option to choose colors for these text types according to our preference. So today I'm going to show you how to change font colors in VBA editor. First, launch your Excel application and create blank workbook. Then go to the VBA editor. You can click inside the spreadsheet and press Alt + F11 to use shortcut keys to open the VBA editor.

So this is a sample code I have written in a module.

As you can see, I'm using default text colors in VBA editor. Now let's see how to change the text colors inside the editor. Click on "Tools" menu and select "Options".

Then "Options dialog box" will open like this.

This dialog box has 4 tabs.

• Editor
• Editor Format
• General
• Docking

Editor tab is selected default. Now select the "Editor Format" tab.

As you can see different text types are listed under code colors. When you select a text type, font color of that text type is shown under the Foreground. In default, Normal text foreground color is set to Auto. If you select "Syntax Error Text" then Foreground will change to red like this.

Also sample text will be shown in the bottom right corner of the dialog box. So you can easily preview the formatting of the text when you do the changes.

Now let's change color of the "Normal Text". First select the "Normal Text" under code colors and then select color you want from the Foreground dropdown.

However as you can see there are limited number of colors we can choose for the font colors. So I selected different color for the "Normal text" and this is how my code looks like now.

In default, both "Normal Text" and "Identifier Text" have same font color. So if I change the "Identifier Text" to the same color as "Normal Text" then sample code will look like this.