Excel is a fantastic tool for all mathematical operations. On top of it, using VBA for these purposes can also be handy.
In the example below, we will show how to store the Pi value in our VBA code, and how to use it in the Excel file.
Create Pi in VBA
Sadly for the users, there is no VBA function for pi. Because of this, if you want to create pi in VBA, you will need to use the WorkseetFunction object. In addition to this, our best option is to create the variable and store the pi value, so that we can use this later in the code.
The first thing we need to do is open the VBA module. To do this, we go to the Developer tab, then click Visual Basic:
We can also do it with the help of a keyboard shortcut, by clicking ALT+F11.
Once there, we will right-click anywhere on the left window, then go to Insert >> Module:
Now we can add our code to the module. This is what our code looks like:
1 2 3 4 5 6 7 8 9 10 |
Sub CreatePi() 'Declaring variables Dim Pi As Single Dim ee As Range 'Setting the variables Pi = Application.WorksheetFunction.Pi() Set ee = Application.InputBox(prompt:="Please Select any cell", Type:=8) ee = ActiveCell ee = Pi End Sub |
In the module itself, the code looks similar:
It is worth mentioning that using single as a type in declaring pi will give us seven figures. If we use double we will get 14 figures.
What our formula does is that it first declares two variables: Pi as Single and ee as Range:
1 2 3 |
'Declaring variables Dim Pi As Single Dim ee As Range |
Once declared, we can set these variables. As said, there is no VBA function for pi so we need to store it in a function found in Excel. We set ee to be equal to the cell of the user’s choosing.
Then we declare ee to be an active cell, and finally to be equal to our Pi variable.
1 2 3 4 5 |
'Setting the variables Pi = Application.WorksheetFunction.Pi() Set ee = Application.InputBox(prompt:="Please select any cell", Type:=8) ee = ActiveCell ee = Pi |
This is everything we need. When we run the code (by clicking F5 on the keyboard), the following thing will happen:
A user will be prompted to select any cell in our worksheet. We will select cell C4:
And when we click OK, we will have the pi number inserted:
Additionally, we could add any comment for our user or change the text in the original input box. We can also go on and change the type for our Pi variable from single to double, so instead of
1 |
Dim Pi As Single |
we will insert
1 |
Dim Pi As Double |
in our code and we would be shown the following number at the end:
We would do this just in case we want to be extra precise.