what's this procedure called ? - Page 2
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

Thread: what's this procedure called ?

  1. #11
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    nar its called fixed as that waht you do that looks like a dollar sign and is used as such!

    These an ancher down Deptford High Steet (Near Peckham if your wondering)

    Dont look like ancher to me... $ fixes cells!

    Translation thing i guess, Excel is US made and designed ! He he he /// what ever mayyetr little .. if it works..

    must sent the txt file Chris...
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  2. #12
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-26 12:36, Juan Pablo G. wrote:
    Jack, one quick word.

    A good designed UDF can kick *** on a lot of formulae... for example, try to do the FuzzyMatch challenge with formulas... it's doable, I agree, but, it will take A LOT of Excel formulas, and just one UDF !

    So, they're not the Devil !!!
    Try to do the FuzzyMatch with VBA as well. Was there ever a definitive solution to that problem which solved it for all of the test data?

  3. #13
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-27 14:02, Mark O'Brien wrote:
    On 2002-03-26 12:36, Juan Pablo G. wrote:
    Jack, one quick word.

    A good designed UDF can kick *** on a lot of formulae... for example, try to do the FuzzyMatch challenge with formulas... it's doable, I agree, but, it will take A LOT of Excel formulas, and just one UDF !

    So, they're not the Devil !!!
    Try to do the FuzzyMatch with VBA as well. Was there ever a definitive solution to that problem which solved it for all of the test data?
    Mark I think Daemon came up with a solution.

    Juan...would have to agree UDF will do the job where no formula will do....just a matter
    of selecing the right tools/technique to do the Job.

    Hi Jack I know what you mean about UDF being
    slow....but a combination of UDF and VBA will
    take care of any speed issues. I've used this
    to good effect.


    Ivan

    Ivan

  4. #14
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ivan and Juan

    Can't say I agree. While most VBA UDF's will simplify a function greatly for the user, it would be very unlikely that a worksheetfunction equivalent would be as slow, even if nesting is needed. It may well look a lot shorter and be much more user friendly but it is rare that it would be as efficient. Excels standard Worksheet functions are not bounded by the same rules as we are when using Excel VBA to write UDF's. There are of course occasions that a UDF is the only option. IMO a well written UDF will incorporate the use of one or more WorsheetFunctions, but as soon as you start putting in Loops etc you are well behind the eight ball.

    I would be very interested to see some examples of a well written UDF being faster in calculating than a Worksheet Function equivalent.

  5. #15
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,

    funny you should mention that mate.... I was going to try and incorporate some named formulae in a single formula for converting numbers to actual text

    (57 becomes "fifty seven")

    up to values of 999,999,999. I know it's done via code but thought it would be a nice little excercise for me - maybe the differences in performance too can be measured in this case.

    Chris

  6. #16
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chris, this would be a case when a UDF is called for. Here is one from Microsoft


    '****************
    ' Main Function *
    '****************

    Function SpellNumber(ByVal MyNumber)
    Dim Dollars, Cents, Temp
    Dim DecimalPlace, Count

    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "

    ' String representation of amount.
    MyNumber = Trim(Str(MyNumber))

    ' Position of decimal place 0 if none.
    DecimalPlace = InStr(MyNumber, ".")
    ' Convert cents and set MyNumber to dollar amount.
    If DecimalPlace > 0 Then
    Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
    "00", 2))
    MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If

    Count = 1
    Do While MyNumber <> ""
    Temp = GetHundreds(Right(MyNumber, 3))
    If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
    If Len(MyNumber) > 3 Then
    MyNumber = Left(MyNumber, Len(MyNumber) - 3)
    Else
    MyNumber = ""
    End If
    Count = Count + 1
    Loop

    Select Case Dollars
    Case ""
    Dollars = "No Dollars"
    Case "One"
    Dollars = "One Dollar"
    Case Else
    Dollars = Dollars & " Dollars"
    End Select

    Select Case Cents
    Case ""
    Cents = " and No Cents"
    Case "One"
    Cents = " and One Cent"
    Case Else
    Cents = " and " & Cents & " Cents"
    End Select

    SpellNumber = Dollars & Cents
    End Function

    '*******************************************
    ' Converts a number from 100-999 into text *
    '*******************************************

    Function GetHundreds(ByVal MyNumber)
    Dim Result As String

    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)

    ' Convert the hundreds place.
    If Mid(MyNumber, 1, 1) <> "0" Then
    Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If

    ' Convert the tens and ones place.
    If Mid(MyNumber, 2, 1) <> "0" Then
    Result = Result & GetTens(Mid(MyNumber, 2))
    Else
    Result = Result & GetDigit(Mid(MyNumber, 3))
    End If

    GetHundreds = Result
    End Function

    '*********************************************
    ' Converts a number from 10 to 99 into text. *
    '*********************************************

    Function GetTens(TensText)
    Dim Result As String

    Result = "" ' Null out the temporary function value.
    If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
    Select Case Val(TensText)
    Case 10: Result = "Ten"
    Case 11: Result = "Eleven"
    Case 12: Result = "Twelve"
    Case 13: Result = "Thirteen"
    Case 14: Result = "Fourteen"
    Case 15: Result = "Fifteen"
    Case 16: Result = "Sixteen"
    Case 17: Result = "Seventeen"
    Case 18: Result = "Eighteen"
    Case 19: Result = "Nineteen"
    Case Else
    End Select
    Else ' If value between 20-99...
    Select Case Val(Left(TensText, 1))
    Case 2: Result = "Twenty "
    Case 3: Result = "Thirty "
    Case 4: Result = "Forty "
    Case 5: Result = "Fifty "
    Case 6: Result = "Sixty "
    Case 7: Result = "Seventy "
    Case 8: Result = "Eighty "
    Case 9: Result = "Ninety "
    Case Else
    End Select
    Result = Result & GetDigit _
    (Right(TensText, 1)) ' Retrieve ones place.
    End If
    GetTens = Result
    End Function

    '*******************************************
    ' Converts a number from 1 to 9 into text. *
    '*******************************************

    Function GetDigit(Digit)
    Select Case Val(Digit)
    Case 1: GetDigit = "One"
    Case 2: GetDigit = "Two"
    Case 3: GetDigit = "Three"
    Case 4: GetDigit = "Four"
    Case 5: GetDigit = "Five"
    Case 6: GetDigit = "Six"
    Case 7: GetDigit = "Seven"
    Case 8: GetDigit = "Eight"
    Case 9: GetDigit = "Nine"
    Case Else: GetDigit = ""
    End Select
    End Function

  7. #17
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 00:25, Dave Hawley wrote:
    Ivan and Juan

    Can't say I agree. While most VBA UDF's will simplify a function greatly for the user, it would be very unlikely that a worksheetfunction equivalent would be as slow, even if nesting is needed. It may well look a lot shorter and be much more user friendly but it is rare that it would be as efficient. Excels standard Worksheet functions are not bounded by the same rules as we are when using Excel VBA to write UDF's. There are of course occasions that a UDF is the only option. IMO a well written UDF will incorporate the use of one or more WorsheetFunctions, but as soon as you start putting in Loops etc you are well behind the eight ball.

    I would be very interested to see some examples of a well written UDF being faster in calculating than a Worksheet Function equivalent.
    Dave I agree on the worksheet functions...
    I probably didn't explain properly.
    The UDF I'm talking about would be used in
    conjuction with a macro to populate
    arrays and then pasted as a value only.
    These are typically Functions that a formula
    can't get eg.
    and this is just one of many.

    Get files in folder
    Populate with File info.
    I'd use a UDF to get the info
    populate the cells etc......

    eg.
    UDF

    Function ShowFileAccessInfo(sFileName As String)
    Dim Fso, F, Info

    Set Fso = CreateObject("Scripting.FileSystemObject")
    Set F = Fso.GetFile(sFileName)

    Info = UCase(sFileName) & " "
    Info = Info & "Created:= " & F.DateCreated & " "
    Info = Info & "Last Accessed:= " & F.DateLastAccessed & " "
    Info = Info & "Last Modified:= " & F.DateLastModified
    ShowFileAccessInfo = Info

    End Function


    Ivan
    The Functions I use can't be done via typical
    worksheet functions...and I wouldn't in most
    cases use it, so I agree with you here.
    As I said before It's using the right tools
    and codes depending I what is required.

  8. #18
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dave,

    nice one mate, thanks for digging that out...

    that's VBA code though !

    I already know this is achievable - what I'm hoping to achieve is a single formula, hopefully with a lot less typing and without the need to go opening VBA modules (ie somethign someone can just copy and paste from this forum straight into a cell)

    Jack said he'd buy me a night's worth of Castlemaine XXXX if I achieved it, so hey, I'm on to it...!


  9. #19
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-27 14:02, Mark O'Brien wrote:
    Was there ever a definitive solution to that problem which solved it for all of the test data?
    Yes, three so far. Mine (Altough Bill never posted my last code !), Damon's and one more code that I don't remember now who wrote it. Bill also sent me one solution using only Excel's formulas (That's why I used it as my example), but, unfortunately, it didn't work for all cases, but, I liked the idea. FYI, it used about 20 different cells to Try and get the answer. So... 20 formulas vs 1 UDF...
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  10. #20
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-03-28 13:53, Chris Davison wrote:
    Dave,

    nice one mate, thanks for digging that out...

    that's VBA code though !

    I already know this is achievable - what I'm hoping to achieve is a single formula, hopefully with a lot less typing and without the need to go opening VBA modules (ie somethign someone can just copy and paste from this forum straight into a cell)

    Jack said he'd buy me a night's worth of Castlemaine XXXX if I achieved it, so hey, I'm on to it...!

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com