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

Get FREE Advanced Excel Exercises with Solutions!

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.

Fix Function in VBA for Positive Number

Then you will see the output in our selected cell.

Fix Function in VBA for Positive Number

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.

Fix Function in VBA for Negative Number

  • 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.

Fix Function in VBA for Negative Number

Then you will get your desired output like the screenshot below-

Read More: How to Use VBA IsNumeric Function (9 Examples)


Similar Readings:


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.

VBA Fix Function And Get the Output in a Pop-Up Message Box

  • 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.

VBA Fix Function And Get the Output in a Pop-Up Message 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.

VBA Fix Function for a Number within Apostrophe

  • 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.

VBA Fix Function for a Number within Apostrophe

  • 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.

Fix Function Error in VBA

  • 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.

Fix Function Error in VBA

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

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo