formula to VBA

[Mrs. Robinson]

How do I go from this formula
=INT(G20/12) & " ft. " & MOD(G20,12) & " in."
to a line in VBA that will perform in this function:

Function ConvertInches(intInches)

Dim intInches As Integer
Dim intFeet As Integer

ConvertInches = INT(intInches/12) & " ft. " & MOD(intInches,12) & " in."

End Function

I know I'm screwing it up but hopefully it's a simple fix.

Thanks,

 

[Mike H]

hi,

try this. The correct syntax for MOD in VB is 'Num MOD divisor' but
unfortunately we cant use that because it returns an integer which is
different to the worksheet MOD

Function ConvertInches(intInches) As String
inches = intInches - (Int(intInches / 12) * 12)
ConvertInches = Int(intInches / 12) & " ft. " & inches & " in."
End Function
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rick Rothstein]

Mod is an operator in VB, not a function like it is in the worksheet world.
Also, you don't need those variable declarations inside your function
because you never make use of them. Finally, for future reference, you
should declare integer type variables as Long, not Integer... your 32-bit
software will end up working with your values in a Long memory storage unit
anyway, so no need to make VB convert back and forth from a declared Integer
when working with the value. Here is the code you were looking for...

Function ConvertInches(Inches As Long) As String
ConvertInches = Int(Inches / 12) & " ft. " & (Inches Mod 12) & " in."
End Function

 

[Rick Rothstein]

The correct syntax for MOD in VB is 'Num MOD divisor' but

unfortunately we cant use that because it returns an integer which is
different to the worksheet MOD

Given the OP's whole number of inches as input, what integer is VB's Mod
operator returning that is different from the worksheet MOD function's
return value?

 

[Ron Rosenfeld]

How do I go from this formula
=INT(G20/12) & " ft. " & MOD(G20,12) & " in."
to a line in VBA that will perform in this function:

Function ConvertInches(intInches)

Dim intInches As Integer
Dim intFeet As Integer

ConvertInches = INT(intInches/12) & " ft. " & MOD(intInches,12) & " in."

End Function

I know I'm screwing it up but hopefully it's a simple fix.

Thanks,

1. Since you are declaring intInches in the Function line, you should not also
declare it in the Dim lines.

2. There is no significant advantage to using the Integer data type in current
versions of VBA, and it will limit your input and any interim calculations to
2^15 -1

3. The MOD function works differently in VBA than in Excel (see HELP for both
for an explanation.

With those caveats, if you want to reproduce the worksheet formula exactly in
VBA, you could use Evaluate:

------------------
Function ConvertInches(intInches)
ConvertInches = Evaluate("=INT(G20/12) & "" ft. "" & MOD(G20,12) & "" in.""")
end function
---------------------------

Or, if you want to just use native VBA functions, and output a string as would
the worksheet function:

------------------------------
Function ConvertInches(intInches)
ConvertInches = Format(Int(intInches / 12), "0"" ft. """) & _
Format(intInches - Int(intInches / 12) * 12, "General Number") & _
" in."
End Function

 

[Rick Rothstein]

3. The MOD function works differently in VBA than in Excel (see HELP

for both for an explanation.

Given the OP's set up where the two values (the function argument and the
number 12) are whole numbers, I do not think the worksheet's MOD function
and VB's Mod operator will return different values... am I wrong in thinking
that?

 

[Mrs. Robinson]

Perfect...thanks.

hi,

try this. The correct syntax for MOD in VB is 'Num MOD divisor' but
unfortunately we cant use that because it returns an integer which is
different to the worksheet MOD

Function ConvertInches(intInches) As String
inches = intInches - (Int(intInches / 12) * 12)
ConvertInches = Int(intInches / 12) & " ft. " & inches & " in."
End Function
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Rick,

Despite the OP's variable declaration in the function the worksheet formula
the OP posted would deal with a decimal number so I attempted to emulate that.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rick Rothstein]

Ahh, I see. I figured the OP would only be interested in whole numbers of
inches because he deliberately declared the function's argument to be an
Integer. But you (and Ron) have taken the safe position which, in thinking
about it, is probably the best thing to do.

 

[Rick Rothstein]

3. The MOD function works differently in VBA than in Excel (see HELP

Given the OP's set up where the two values (the function argument and the
number 12) are whole numbers, I do not think the worksheet's MOD function
and VB's Mod operator will return different values... am I wrong in
thinking that?

Never mind... Mike gave me an explanation which, I'm guessing, would cover
your reasoning as well.

 

[Dana DeLouis]

How do I go from this formula
=INT(G20/12)& " ft. "& MOD(G20,12)& " in."
to a line in VBA that will perform in this function:

Function ConvertInches(intInches)

Dim intInches As Integer
Dim intFeet As Integer

ConvertInches = INT(intInches/12)& " ft. "& MOD(intInches,12)& " in."

End Function

I know I'm screwing it up but hopefully it's a simple fix.

Thanks,

Another option:

intInches=26

?Int(intInches / 12)
2

or...

?intInches\12
2



Not the best, but something different if the Op doesn't mind a leading
zero for the inches...

Sub Test()
Debug.Print Inch2FtIn(33)
End Sub

2 ft. 09 in

Function Inch2FtIn(x)
Inch2FtIn = Format(WorksheetFunction.DollarFr(x / 12, 12), _
"## \f\t. 00 \i\n")
End Function


= = = = = = =
Dana DeLouis

 

[Rick Rothstein]

IF the inches argument to the function could possibly contain a fraction
component (as Mike has suggested could be the case), then you might not want
to use integer division as you have suggested. If floating point numbers are
involved, integer division may not return the answer you expect. Most people
think x\y is short-hand for Int(x/y), probably because of its name "integer"
division. However, VB applies Banker's Rounding to the numerator AND
denominator BEFORE performing the division. Many would think that this...

MsgBox 4.5 \ 1.5

should display 3 in the MessgeBox, however, it prints out 2 instead.
Banker's Round rounds the 4.5 to 4 (the nearest even number) and rounds 1.5
to 2 (again, the nearest even number) AND ONLY THEN does it do the division
and truncate the fractional part (4/2 producing 2 as the answer).