what's this procedure called ?

[Chris Davison]

Evening all,

What's this procedure called ?

activate B1
insert
names
define
refers to : =a1*1.25
name it "addtax"
add it

I know it's not naming a range and I don't think it's a UDF, don't they need VBA ?, what are these called ?

ta
Chris

 

[Jack in the UK]

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

 

[Mark O'Brien]

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?

 

[Ivan F Moala]

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

 

[Dave Hawley]

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.

 

[Chris Davison]

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

 

[Dave Hawley]

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

 

[Ivan F Moala]

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.

 

[Chris Davison]

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

:wink:

 

[Juan Pablo González]

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

 

[Jack in the UK]

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

:wink: