Excel-VBA Solutions

Convert Multiple Excel Sheets to a Single PDF File Manually

From an earlier post we learnt how to convert one excel sheet to a PDF manually. If you want to know how, then please check this post.

Save an Excel Sheet as PDF

This Excel tutorial explains how to convert multiple sheets from your excel workbook to a one single PDF file manually. So you can convert sheets you wish from your excel file to a one PDF file. Assume we have an Excel file with 5 sheets like this.

If we want to convert only Sheet 1 and Sheet 3 to one PDF file, then these are the steps we need to follow.

First we need to select Sheet 1 and Sheet 3. You can select multiple sheets from excel file by clicking on sheet names while pressing Ctrl key on your keyboard.

Now we have selected Sheet 1 and Sheet 3. Next we need to click on the “File” menu.

Then click on the “Save as”

Next click on the “Browse”

It will open the “Save as” dialog box like this.

Now browse for the folder you want to save the file. Next enter a suitable name to the “File name” field. After that click on the “Save as type” dropdown. Select PDF from the list.

Once you select the PDF from the list, Save as dialog box will show you some more options. So click on the “Options” button at the bottom.

When you click on that button, “Options” window will open like this. Then make sure that the “Active sheet(s)” option is selected under the “Publish what” section.

Now click ok. It will close the “Options” window. Then click the “Save” button in the “Save as” dialog box. Selected sheets will be converted to a one pdf file successfully. So this is how we can convert multiple excel sheets to a one PDF file manually. Also you can convert an entire workbook to a single PDF file as well. Check this post if you want to know how.

Convert an Entire Workbook to a Single PDF File

BESSELJ Function

Today I’m going to show you how to use an another engineering function in Excel. This new function is called Besselj. It returns the Bessel function J_n(x).

Syntax of the function is BESSELJ(x,n). So we need to input two parameters (x and n) for this function. Both are required to return a value. When you input those two arguments, function will be evaluated at x. And n is the order of the Bessel function. You can use this function in Excel like this.

Here I have used cell addresses to input the arguments. But you can also use values directly like this as well.

So now let’s find the J_n(x) values for x values from -5 to 5 when the order of the function is 1.

If we draw a graph of J_n(x) for the above values it will look like this.

If we change the order of the Bessel function to 2, then we will get the following result.

This is the graph for the above table.

BESSELI Function

This Excel tutorial explains how to use an Engineering function in Excel. Excel BESSELI function returns the modified Bessel functions I_n(x).

Syntax of the BESSELI function is BESSELI(x,n). So this function has two arguments. Both of them are mandatory. x is the value at which to evaluate the function. n represents the order of the function. And n should be positive. Function will return an error if n is less than 0.

And this is how you can use the function in the Excel.

In the above example I have passed the arguments by cell addresses. However you can pass the arguments like this as well

=BESSELI(-5,1)

This table shows the I_n(x) for x values from -5 to 5. Order of the function is 1.

And here is the graph for above table.

Then I changed the order of the function to 2 and calculated the I_n(x).

This is the graph for order 2.

DateSerial Function (VBA)

In this post I will explain how to use DateSerial function in VBA. DateSerial function is a quite useful function we can use in VBA. If we input year, month and day this function returns the relevant date.

So we need to input three parameters to the function. All of them are mandatory.

Year - Integer type value which represents the year (ex - 2020)
Month - Integer type value which represents the month (ex - 10)
Day - Integer type value which represents the day (ex - 20)

Below example shows you how you can use this function in VBA

Sub DateSerialExample()

Dim SalesDataDate As Date

Dim iYear As Integer
Dim iMonth As Integer
Dim iDay As Integer

iYear = 1965
iMonth = 10
iDay = 25

SalesDataDate = DateSerial(iYear, iMonth, iDay)

Debug.Print SalesDataDate

End Sub

You will get this result if you run above macro

And if you want to change the format of the date then you can do it like this.

So you will get following result.

How to Add Additional Controls in Excel VBA

In this post, I will show you how to add additional controls such as Windows Media Player, Adobe PDF Reader and Microsoft ListView Control in Excel VBA.

So here are the steps you need to follow. First create a blank workbook.

Then click somewhere in the excel sheet and press Alt+F11 to open the VBA editor. Then click on the Insert menu and select Userform.

Once you select the Userform, a new userform will be created like this.

Also the Toolbox will appear to the side of the form as well.

Some of the controls are already in this toolbox such as Textbox, Label and ListBox etc. Now let’s look at how to add additional controls to it. To do that we need to click on the Tools menu and click on Additional Controls...

It will open the Additional Controls Window like this.

Now you can add any additional control you like. For this example let’s add Windows Media Player. Select the checkbox in front of the Windows Media Player and then click OK.

This will add Windows Media Player icon to our Toolbox like this.

Now we can add Windows Media Player to our userform.

How to use preserve keyword in arrays

Today I’m going to teach you how to use preserve keyword effectively. We use preserve keyword to resize arrays without loosing existing data. But you should use it carefully. Because if you use it unwisely, then it may have huge impact on run time of the program. For an example it is inadvisable to use preserve inside the loops.

So now I will show you how you can avoid using preserve keyword inside loops. Consider following example. This excel sheet has list of names in column A. Assume we have names up to 30,000 rows. If you look at the list carefully you will notice that this list has duplicate names. Our goal is to get unique names to an array.

Here is a one method you can use to do that.

Sub GetUniqueNames()

Dim WS As Worksheet

Dim AllNames(1 To 30000) As String
Dim UniqueNames() As String

Dim i As Long
Dim j As Long
Dim Counter As Long

Dim NameFound As Boolean

Set WS = ActiveSheet

For i = 1 To 30000
     AllNames(i) = WS.Range("A" & i).Value
Next i

ReDim UniqueNames(1 To 1)
UniqueNames(1) = AllNames(1)
Counter = 1
For i = 1 To 30000
     NameFound = False
     For j = 1 To Counter
          If StrComp(AllNames(i), UniqueNames(j), vbTextCompare) = 0 Then
              NameFound = True
         End If
     Next j
     If NameFound = False Then
          Counter = Counter + 1
          ReDim Preserve UniqueNames(1 To Counter)
          UniqueNames(Counter) = AllNames(i)
     End If
Next i

End Sub

If you look at above code you will notice that there is a nested for loop in above subroutine. And I have placed preserve keyword inside the outer for loop. So when we execute the code, program goes through all the values from 1 to 30000. For each value, it checks whether this current name is already in the UniqueNames array or not. If the value is not in the UniqueNames array then program resize the UniqueNames array copying existing data. Then program add that new name to the end of the array. So this means that when ever there is new name, program need to resize UniqueNames array copying existing data. But this is an expensive operation. So we should try to find different approach for this.

So our goal here is to remove the preserve keyword from the For Loop. To do that, first we need to identify the highest possible size UniqueNames array can have. So in this example it should be 30000. Now we resize the array to it’s highest possible size at the beginning.
ReDim UniqueNames(1 To 30000)

Then we can change the nested For Loop section like this.

Counter = 1
For i = 1 To 30000
     NameFound = False
     For j = 1 To Counter
         If StrComp(AllNames(i), UniqueNames(j), vbTextCompare) = 0 Then
             NameFound = True
         End If
     Next j
     If NameFound = False Then
         UniqueNames(Counter) = AllNames(i)
         Counter = Counter + 1
     End If
Next i

ReDim Preserve UniqueNames(1 To Counter - 1)

Here we loop through the values and add new names to UniqueNames array. We calculate the number of unique names using Counter variable. So at then end, we use preserve keyword once to resize the UniqueNames array to it’s correct size.

So the complete code of the second method is as follows.

Sub GetUniqueNames_Method2()

Dim WS As Worksheet

Dim AllNames(1 To 30000) As String
Dim UniqueNames(1 To 30000) As String

Dim i As Long
Dim j As Long
Dim Counter As Long

Dim NameFound As Boolean

Set WS = ActiveSheet

For i = 1 To 30000
     AllNames(i) = WS.Range("A" & i).Value
Next i

Counter = 1
For i = 1 To 30000
     NameFound = False
     For j = 1 To Counter
         If StrComp(AllNames(i), UniqueNames(j), vbTextCompare) = 0 Then
             NameFound = True
         End If
     Next j
     If NameFound = False Then
         UniqueNames(Counter) = AllNames(i)
         Counter = Counter + 1
     End If
Next i

ReDim Preserve UniqueNames(1 To Counter - 1)

End Sub

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