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:C3345DE7E9C64333BB0B1519F1B13EAC(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:C5C44AB1544F4A8C9799CED46BC04A5B(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:C3345DE7E9C64333BB0B1519F1B13EAC(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 