Worksheetfunction.Convert using VBA with variable as the number/amount

JulianJaimes1985

New Member
Joined
Feb 18, 2022
Messages
4
Office Version
  1. 2021
Platform
  1. Windows
Hi all,

I am in need to write a custom function where i would be using the Worksheetfunction.convert(). Inside the arguments for the function the first argument (number or amount to convert) is a variable inside the function. I have typed the function like this but it has not worked: Worksheetfunction.convert(X, "m", "ft"). I have tried changing the X for a number e.g. 10 and it works but no when i use the variable name.

Does anyone know how to fix this? Thanks for your help.

Regards
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Not sure I understand what you are trying to do. Does this help at all?
VBA Code:
Sub test()
Dim X
X = 10
MsgBox Application.Convert(X, "ft", "m")
End Sub
 
Upvote 0
Edit: Oops, hadn't noticed JoeMo's post. :)

Welcome to the MrExcel board!

Perhaps you need to show us the whole code as there could be something else wrong with your variable declaration or value as this works for me.

VBA Code:
Sub testConv()
  Dim X
 
  X = 10
  MsgBox WorksheetFunction.Convert(X, "m", "ft")
End Sub
 
Upvote 0
Not sure I understand what you are trying to do. Does this help at all?
VBA Code:
Sub test()
Dim X
X = 10
MsgBox Application.Convert(X, "ft", "m")
End Sub
Thanks for your reply Joe. Sorry for not making myself clear. I'll give it another try

I am trying to write a user defined function (UDF). The function takes some parameters to give me the result i want. However, within the function I have to use the Worksheetfunction.Convert() to convert from my unit to another. So i know the Convert function takes 3 arguments (Arg1 = number/value, arg2=units to convert from (eg. meters), arg3=unit to convert to (eg. ft).

Argument 1 is not a fix number it is a number that i calculate first within the function and i have assigned a variable name to that number, eg "X".

When i call the worksheetfunction.Convert() inside the UDF i write it like this:

Result = Worksheetfunction.Convert(X, ''m", "ft")

However, it is giving me a answer of 0 (zero), which is not correct. The problem is that Worksheetfunction.Convert cannot read the value stored in X to make the conversion.

I am wondering if there is a particular way of writing so it can read the value stored in my variable X.

Regards
 
Upvote 0
Thanks, the code i have is as follows:

Function AnnVol_OHtoBHA(CsgShoe, TopBHA, HoleDepth, OD_Hole, AVGOD_BHA, BHA_Length) As Double

Dim X As Double
Dim OH_Length As Double

If TopBHA >= CsgShoe Then

X = BHA_Lenght

ElseIf TopBHA < CsgShoe Then

X = HoleDepth - CsgShoe
End If

AnnVol_OHtoBHA = WorksheetFunction.Convert(X, "m", "ft") '*[((OD_Hole ^ 2) - (AVGOD_BHA ^ 2)) / 1029.4]

End Function


Thanks
 
Upvote 0
Check your spelling. :)

X = BHA_Lenght

Good reason to set this option in your vba

1645244267392.png


BTW, when posting code in the forum, please use the available code tags. My signature block below has more details.
 
Upvote 0
You're welcome. :)

Check that you have seen the couple of edits that I made to my last post. One would stop that embarrassment in the future.
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top