Prev: Excel 2007 "Protect Sheet" option not available
Next: Search and display based on cell value
From: Mrs. Robinson on 14 May 2010 09:25
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,
From: Mike H on 14 May 2010 09:52
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.


"Mrs. Robinson" wrote:

> 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,
From: Rick Rothstein on 14 May 2010 09:59
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 (MVP - Excel)



"Mrs. Robinson" <MrsRobinson(a)discussions.microsoft.com> wrote in message
news:8B91CBB0-8944-4061-A2C8-81CB1FCBC8F7(a)microsoft.com...
> 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,

From: Rick Rothstein on 14 May 2010 10:05
> 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?

--
Rick (MVP - Excel)



"Mike H" <MikeH(a)discussions.microsoft.com> wrote in message
news:C3345DE7-E9C6-4333-BB0B-1519F1B13EAC(a)microsoft.com...
> 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.
>
>
> "Mrs. Robinson" wrote:
>
>> 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,

From: Ron Rosenfeld on 14 May 2010 10:05
On Fri, 14 May 2010 06:25:01 -0700, Mrs. Robinson
<MrsRobinson(a)discussions.microsoft.com> wrote:

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

--ron
 |  Next  |  Last
Pages: 1 2 3
Prev: Excel 2007 "Protect Sheet" option not available
Next: Search and display based on cell value