How to Use Fix Function in Excel VBA (4 Examples)

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:

Arguments:

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 As Integer
    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.

Related Articles

• Excel VBA ASC() Function – Get ASCII Value of Character
• How to Create a Body Mass Index (BMI) Calculator in Excel Using VBA
• Use TRIM Function in VBA in Excel (Definition + VBA Code)
• How to Use VBA SPLIT Function in Excel (5 Examples)
• How to Use LCase Function in VBA in Excel (With 4 Examples)