Prev: Excel 2007 "Protect Sheet" option not available
Next: Search and display based on cell value
From: Rick Rothstein on 14 May 2010 10:22
> 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?

--
Rick (MVP - Excel)

From: Mrs. Robinson on 14 May 2010 10:32
Perfect...thanks.

"Mike H" wrote:

> 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: Mike H on 14 May 2010 11:09
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" wrote:

> > 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: Rick Rothstein on 14 May 2010 11:58
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 (MVP - Excel)



"Mike H" <MikeH(a)discussions.microsoft.com> wrote in message
news:C5C44AB1-544F-4A8C-9799-CED46BC04A5B(a)microsoft.com...
> 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" wrote:
>
>> > 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: Rick Rothstein on 14 May 2010 11:59
>> 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?

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

--
Rick (MVP - Excel)

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