The Fix function is categorized under the Math function list in Excel VBA. It is an inbuilt VBA Function. This function is used to remove the decimals from the argument and it works in a similar manner as the VBA INT function does. This article will guide you with 4 simple examples to learn how to use the Fix function in Excel VBA.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
What is Fix Function in Excel VBA
Purpose:
The Excel FIX function is used to return the integer segment of a number.
Syntax:
Fix(expression)Arguments:
| Argument | Required/Optional | Explanation |
|---|---|---|
| expression | Required | A numeric expression that will be truncated to its integer part. |
Return Parameter:
The integer portion of a number.
Works in:
We can use the Fix function in Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, and Excel 2000.
4 Examples of Using Fix Function in Excel VBA
Example 1: Use Fix Function in VBA for Positive Number
In our very first method, we’ll use the Fix function in Excel VBA for a positive real number. For this example, our number is 3.567 that I have placed in Cell C4. I’ll extract the output in Cell C5.
Steps:
- Right-click on the sheet title.
- Select View Code from the context menu.
A VBA window will open up.
Or you can press Alt+F11 on your keyboard to open the VBA window directly.
- Now type the following codes in it-
Sub Fix_Func()
Range("C5").Value = Fix(Range("C4"))
End Sub- Then just click the Run icon in the VBA window to run the codes.
Then you will see the output in our selected cell.
Read More: Excel Formula to Generate Random Number (5 examples)
Example 2: Apply Fix Function in VBA for Negative Number
Now we’ll apply the Fix function in VBA for a negative real number which is -5.348. The Fix function will keep the negative sign and will return the integer part.
Steps:
- Right-click your mouse on the sheet title.
- Then select View Code from the context menu.
- After appearing the VBA window type the following codes in it-
Sub Fix_Func_Ex2()
Range("C5").Value = Fix(Range("C4"))
End Sub- After that click the Run icon to run the codes.
Then you will get your desired output like the screenshot below-
Read More: How to Use VBA IsNumeric Function (9 Examples)
Similar Readings:
- How to Use VBA Rnd in Excel (4 Methods)
- Use the VBA Environ Function (4 Examples)
- How to Use VBA Int Function in Excel ( 3 Examples)
- Use VBA And Function in Excel (4 Examples)
- How to Use VBA Mod Operator (9 Examples)
Example 3: Insert VBA Fix Function And Get the Output in a Pop-Up Message Box
In this example, we’ll get the output in a pop-up message box instead of getting it in the worksheet. Here, we’ll do the operation for the number 7.4538.
Steps:
- Firstly, Right-click your mouse on the sheet title.
- Later, from the context menu select View Code.
Soon after, a VBA window will appear.
- Write the following codes-
Sub Fix_Func_Ex3()    Dim x AsInteger   Dim output As Doubl    x = 7.4538    output = Fix(x)    MsgBox "The Integer part of the number is: " & output, vbInformation, "Fix Function in VBA" End Sub
- Click the Run icon then to run the codes.
And then you will get a Macro dialog box.
- Now select the Macro name as we mentioned it in the VBA codes.
- Then just press Run.
A pop-up message box will then show the output.
Read More: How to Use MsgBox Function in Excel VBA (A Complete Guideline)
Example 4: Use VBA Fix Function for a Number within Apostrophe
In our last method, we’ll return the integer part by using the Fix function in VBA where the umber is within apostrophe- ‘325.6734’. Here we’ll get the output in a message box too.
Steps:
- Right-click your mouse on the sheet title.
- Then from the context menu select View Code.
- Write the following codes after opening the VBA window-
Sub Fix_Func_Ex4()
   Dim x As Integer
   Dim output As Double
   x = "325.6734"
   output = Fix(x)
   MsgBox "The Integer part of the number is: " & output, vbInformation, "Fix Function in VBA"
End Sub- Later, click the Run icon to run the codes.
- Then after appearing the Macros dialog box, select the Macro name that we specified in the codes.
- Finally, press Run.
Here’s our integer part of the number-
Read More: How to Use the VBA Chr Function (2 Examples)
Fix Function Error in VBA
If we input anything that cannot be interpreted as a number then the Fix function will return the error- Type Mismatch. Let’s run a code and see the output.
Steps:
- Like the previous methods, right-click on the sheet title to open the VBA window.
- Write the following codes-
Sub Fix_Function_Error()
   Dim x As String
   Dim output As Double
   x = "Sample_text"
   output = Fix(x)
   MsgBox "The Integer part of the number 'Sample_text' is: " & output, vbInformation, "Fix Function in VBA"
End Sub- Click the Run icon.
Then it will show Run-time error’13’ because we used a non-numeric value.
Things to Remember
- The Fix function will return a NULL If the input number is a NULL.
- If the input number is negative then it will return the first negative integer greater than or equal to the input number.
- The Fix function does not return the nearest integer or round a fraction. Rather it truncates the integer portion of a fractional number.
Conclusion
I hope the procedures described above will be good enough to use the Fix function in Excel VBA. Feel free to ask any question in the comment section and please give me feedback.
