Need a function to convert mm to inches

[kualjo]

I am trying to create a rounding function to convert millimeters to inches, or parts of inches if necessary. Specifically, I want the conversion to translate into 1", 1/2", 1/4", or 1/8" increments. I already have the mm-to-inch conversion factor, so I just need to know how to take that result and display it in standard American measurements.
Thanks!

[lucas]

Go to help in excel and type in Convert

[lucas]

or metric

[Bob Phillips]

If you use a cutom format of ? ?/?\" you will see it as fractions, but that could be any fraction, not just those you show.

[RonMcK]

I am trying to create a rounding function to convert millimeters to inches, or parts of inches if necessary. Specifically, I want the conversion to translate into 1", 1/2", 1/4", or 1/8" increments. I already have the mm-to-inch conversion factor, so I just need to know how to take that result and display it in standard American measurements.
Thanks!
Kualjo,

Fom reading your msg, I believe that you want to display (print) the metric measures converted to inches to the nearest one-eighth inch. The following VBA function should do what you ask.



Public Function in_eighths(mm_in, inch_out, inches) As String
Dim fract_part As String
inches = mm_in / 25.375
inch_out = Int(inches * 8 + 0.5) / 8
Select Case inch_out - Int(inch_out)
Case 0
fract_part = ""
Case 0.125
fract_part = "-1/8"
Case 0.25
fract_part = "-1/4"
Case 0.375
fract_part = "-3/8"
Case 0.5
fract_part = "-1/2"
Case 0.625
fract_part = "-5/8"
Case 0.75
fract_part = "-3/4"
Case 0.875
fract_part = "-7/8"
End Select
in_eighths = Format(Int(inch_out), "####0" & fract_part)
End Function


Sub test_it()
Dim mm_in As Double
Dim text_out As String
Dim inch_out As Double
Dim inches As Double
mm_in = 25
text_out = in_eighths(mm_in, inch_out, inches)
Debug.Print text_out, inch_out, inches
mm_in = 29
text_out = in_eighths(mm_in, inch_out, inches)
Debug.Print text_out, inch_out, inches
mm_in = 31
text_out = in_eighths(mm_in, inch_out, inches)
Debug.Print text_out, inch_out, inches
mm_in = 35
text_out = in_eighths(mm_in, inch_out, inches)
Debug.Print text_out, inch_out, inches
mm_in = 39
text_out = in_eighths(mm_in, inch_out, inches)
Debug.Print text_out, inch_out, inches
mm_in = 41
text_out = in_eighths(mm_in, inch_out, inches)
Debug.Print text_out, inch_out, inches
mm_in = 44
text_out = in_eighths(mm_in, inch_out, inches)
Debug.Print text_out, inch_out, inches
mm_in = 48
text_out = in_eighths(mm_in, inch_out, inches)
Debug.Print text_out, inch_out, inches
mm_in = 51
text_out = in_eighths(mm_in, inch_out, inches)
Debug.Print text_out, inch_out, inches
End Sub


I hope that this helps.

Ron
Orlando, FL:thumb

[TonyJollans]

Or more simply ...



Public Function in_eighths(mm_in) As String
in_eighths = WorksheetFunction.Text(mm_in / 25.375, "? ?/8")
End Function

[lucas]

I've been using the convert function in the analysis tool pak to convert to inches and then a function to convert to fraction.......see attached

Bob's custom format method should work also in lieu of the convert to fraction function, I have just been using this for a while and find it reliable

convert to fraction function by John Lacher with permission.

[TonyJollans]

That's using the same tool - formatting as a fraction - but has an extra level of complexity with feet and inches and fractions of an inch. It might suit the OP (who knows, we've scared him off!) but it's not something I do often enough to need a custom function for :)

[RonMcK]

Or more simply ...


Public Function in_eighths(mm_in) As String
in_eighths = WorksheetFunction.Text(mm_in / 25.375, "? ?/8")
End Function


Tony,

Thanks! I learned something new. There is a slight gotcha. It appears that the TEXT() function knows nothing about lowest common denominator. It tells me that 31 mm is 1-2/8" rather than 1-1/4"

What does 'OP' mean? Original Poster, Other Party, 0* P* ?

Out of curiosity, do we (VBAX) get a lot of 'drive by shooting' type queries (Ask a question and disappear) ?

Thanks,

Ron
Orrlando, FL

[Bob Phillips]

OP - original poster.

Drive by - yes, it's inevitable really.

[lucas]

That's using the same tool - formatting as a fraction - but has an extra level of complexity with feet and inches and fractions of an inch. It might suit the OP (who knows, we've scared him off!) but it's not something I do often enough to need a custom function for :)
Definitely depends on need. I make my living with AutoCAD detailing structural steel so it's a necessity for me.

[RonMcK]

OP - original poster.

Drivbe by - yes, it's inevitable really.
So, we're really (for the most part) helping one another learn while we craft and refine solutions for the missing OP.

I wonder how many OPs are sitting out "there" wondering when they'll get an answer to their query on VBAX? I surmise that an OP says 'I'll wait for a better answer' when he sees the first forum message has no 'answer' to their problem. When OP does not respond (acknowledge) to a forum email, the system stops sending OP notices of new postings. As a result, OP never sees when one or more real solutions are posted.

thanks,

Ron
Orlando, FL

[Bob Phillips]

I don't use email postings Ron, I just check the threads.

[RonMcK]

I don't use email postings Ron, I just check the threads.

If I were in the forum more than I am, I'd use your scheme. Since I'm occasional, I subscribe to threads that interest me. On logging-in, I scan My Threads and My Subscribed Threads, for new activity. After reading those, I scan the new postings.


Ron
Orlando, FL

[TonyJollans]

Drivbe by - yes, it's inevitable really.
So, we're really (for the most part) helping one another learn while we craft and refine solutions for the missing OP.
I'm just driving by :) I haven't been here for quite a while until a couple of weeks ago and, yes, I do it to learn.



It appears that the TEXT() function knows nothing about lowest common denominator. It tells me that 31 mm is 1-2/8" rather than 1-1/4"

Well that's because I explicitly asked for eighths. If you change the format pattern to ? ?/? you'll get 1/4 - actually you'll get 2/9 but that isn't really the point. I don't know if there's a way to get eighths and get LCD without hard coding as you did.

[RonMcK]

Well that's because I explicitly asked for eighths. If you change the format pattern to ? ?/? you'll get 1/4 - actually you'll get 2/9 but that isn't really the point. I don't know if there's a way to get eighths and get LCD without hard coding as you did.

Thanks. I guess I better fire off a suggestion to teh Excel team at MS. :yes

It would be very helpful to have a symbol (similar how the ? in /? causes Excel to compute divisor that fits most closely) that woiuld use the precision I specified and reduce the fraction to LCD.

Ron
Orlando, FL

[liwe]

If you trying to create a rounding function to convert millimeters to inches (https://mm-to-inches.net), or parts of inches conversion to translate into 1", 1/2", 1/4", or 1/8" increments. Use it to take that result and display it in standard American measurements.

[paulked]

I think everyone should convert to Longs. 1 Long = the covalent radius of an atom of Helium (the smallest covalent value of the elements).

There shouldn't be any need for fractions then. For example, an atom of helium would be 2 Longs wide, the distance from New York to Paris would be 1.82e+17 Longs... simple :whistle:

[Paul_Hossler]

@liwe --

Welcome to the forum

Please take a minute and read the FAQs in the link in my signature

Now ...

While the comments were valid, this was a 12 year old post, so I would be surprised if the OP was still interested in it

[paulked]

:rotlaugh:missed that!

[jacklien]

I am trying to create a rounding function to convert millimeters to inches, or parts of inches if necessary. Specifically, I want the conversion to translate into 1", 1/2", 1/4", or 1/8" increments. I already have the mm-to-inch conversion factor, so I just need to know how to take that result and display it in standard American measurements.
Thanks!

I suggest you to use mm to inches (https://www.easyunitconverter.com/mm-to-inches). It helps you to convert mm to inch and inch to mm conversion.