UDF's Problems; Is my Fundamentals Skewed

K

Kieranz

Hi All
Not long Newbie in VBA, using XP with XL03.
Q1: I have 2 subtotal cells (say D10, E10) and a total cell (say G10).
Cells D10 and E10 can contain a numeric, text or "" (null string) as
default. My UDF is as follows:

Function AddFB(x, y) 'front and back score in golf
Dim a, b
a = Range(x)
b = Range(y)
If IsNumeric(a) And IsNumeric(b) Then
AddFB = a + b
ElseIf Len(a) = 0 Then 'to check for ""
AddFB = b 'whatever value in b eg number or text
ElseIf Len(b) = 0 Then
AddFB = a 'again number or text
ElseIf istext(a) then
AddFB = a
ElseIf istext(b) then
AddFB = b
End If
End Function

Whats wrong because when I have my formula below in G10 it works.

'=IF(AND(ISNUMBER(D10),ISNUMBER(E10)),SUM(D10,E10),IF(LEN(D10)=0, _
E10,IF(LEN(E10)=0,D10,IF(ISTEXT(D10),D10,IF(ISTEXT(E10),E10)))))

Background (specially golfers!!). Cell D10 is Front9 score and E10 is
Back9.
A golfer may have besides his shots (ie numeric), text eg n=NR (no
return), d=DQ (disqualified) or r-RTD (Retired). Therefore if text
overrides the scores BUT if there is a "" and a score then of course
score is counted. I hope this makes senses.
Summarised:
x y Result
n n x+y
n t y ie pickup text
t n x
"" n/t y ie pickup n or t
n/t "" x

Q2. I have a couple of UDFs BUT when I do into debug for any of my
Subs the debug goes to one of the various UDF and I can't debug (F8) my
codes. Also how do I debug UDFs line by line?

For those curious about my other UDFs, they are below:

Many, many thks. I am enjoying but it gets a hell lot fustrating when
in the cell you have UDF it goes ###. Improvements, with guide or
reason ;) being cheeky most welcome.
God bless and Rgds KN
Here's the other UDFs:

Function GScore(HSc As Range) 'select 9holes scores
Dim i As Long
For i = 1 To HSc.Cells.Count
If HSc(1) = "" Then
GScore = ""
Exit Function
End If
If HSc(i) = "d" Then
GScore = "DQ"
Exit Function
End If
If HSc(i) = "n" Then
GScore = "NR"
Exit Function
End If
If HSc(i) = "r" Then
GScore = "RTD"
Exit Function
End If
GScore = GScore + HSc(i) 'add the scores
Next i
End Function

Function NetF9(GSc As Range, HC As Range) 'front9 net score, gross
- ½ handicap
If GSc = "" Then
NetF9 = ""
Exit Function
End If
If WorksheetFunction.IsText(GSc) Then
NetF9 = GSc
Else
NetF9 = GSc - (WorksheetFunction.RoundUp(HC / 2, 0))
End If
End Function

Function NetB9(GSc As Range, HC As Range)
If GSc = "" Then
NetB9 = ""
Exit Function
End If
If WorksheetFunction.IsText(GSc) Then
NetB9 = GSc
Else
NetB9 = GSc - (HC - (WorksheetFunction.RoundUp(HC / 2, 0)))
End If
End Function
 
B

Bob Phillips

Function AddFB(x, y) 'front and back score in golf

If IsNumeric(x.Value) And IsNumeric(y.Value) Then
AddFB = x.Value + y.Value
ElseIf Len(x.Value) = 0 Then 'to check for ""
AddFB = y.Value 'whatever value in b eg number or text
ElseIf Len(y.Value) = 0 Then
AddFB = x.Value 'again number or text
ElseIf Application.IsText(x.Value) Then
AddFB = x.Value
ElseIf Application.IsText(y.Value) Then
AddFB = y.Value
End If
End Function


and use like so

=AddFB(D10,E10)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hi All
Not long Newbie in VBA, using XP with XL03.
Q1: I have 2 subtotal cells (say D10, E10) and a total cell (say G10).
Cells D10 and E10 can contain a numeric, text or "" (null string) as
default. My UDF is as follows:

Function AddFB(x, y) 'front and back score in golf
Dim a, b
a = Range(x)
b = Range(y)
If IsNumeric(a) And IsNumeric(b) Then
AddFB = a + b
ElseIf Len(a) = 0 Then 'to check for ""
AddFB = b 'whatever value in b eg number or text
ElseIf Len(b) = 0 Then
AddFB = a 'again number or text
ElseIf istext(a) then
AddFB = a
ElseIf istext(b) then
AddFB = b
End If
End Function

Whats wrong because when I have my formula below in G10 it works.

'=IF(AND(ISNUMBER(D10),ISNUMBER(E10)),SUM(D10,E10),IF(LEN(D10)=0, _
E10,IF(LEN(E10)=0,D10,IF(ISTEXT(D10),D10,IF(ISTEXT(E10),E10)))))

Background (specially golfers!!). Cell D10 is Front9 score and E10 is
Back9.
A golfer may have besides his shots (ie numeric), text eg n=NR (no
return), d=DQ (disqualified) or r-RTD (Retired). Therefore if text
overrides the scores BUT if there is a "" and a score then of course
score is counted. I hope this makes senses.
Summarised:
x y Result
n n x+y
n t y ie pickup text
t n x
"" n/t y ie pickup n or t
n/t "" x

Q2. I have a couple of UDFs BUT when I do into debug for any of my
Subs the debug goes to one of the various UDF and I can't debug (F8) my
codes. Also how do I debug UDFs line by line?

For those curious about my other UDFs, they are below:

Many, many thks. I am enjoying but it gets a hell lot fustrating when
in the cell you have UDF it goes ###. Improvements, with guide or
reason ;) being cheeky most welcome.
God bless and Rgds KN
Here's the other UDFs:

Function GScore(HSc As Range) 'select 9holes scores
Dim i As Long
For i = 1 To HSc.Cells.Count
If HSc(1) = "" Then
GScore = ""
Exit Function
End If
If HSc(i) = "d" Then
GScore = "DQ"
Exit Function
End If
If HSc(i) = "n" Then
GScore = "NR"
Exit Function
End If
If HSc(i) = "r" Then
GScore = "RTD"
Exit Function
End If
GScore = GScore + HSc(i) 'add the scores
Next i
End Function

Function NetF9(GSc As Range, HC As Range) 'front9 net score, gross
- ½ handicap
If GSc = "" Then
NetF9 = ""
Exit Function
End If
If WorksheetFunction.IsText(GSc) Then
NetF9 = GSc
Else
NetF9 = GSc - (WorksheetFunction.RoundUp(HC / 2, 0))
End If
End Function

Function NetB9(GSc As Range, HC As Range)
If GSc = "" Then
NetB9 = ""
Exit Function
End If
If WorksheetFunction.IsText(GSc) Then
NetB9 = GSc
Else
NetB9 = GSc - (HC - (WorksheetFunction.RoundUp(HC / 2, 0)))
End If
End Function
 
B

Bob Phillips

I have no idea why your F8 doesn't work, it does for me.

Amended UDFs

Function GScore(HSc As Range) 'select 9holes scores
Dim i As Long
If HSc(1) = "" Then
GScore = ""
Exit Function
End If
For i = 1 To HSc.Cells.Count
If HSc(i) = "d" Then
GScore = "DQ"
ElseIf HSc(i) = "n" Then
GScore = "NR"
ElseIf HSc(i) = "r" Then
GScore = "RTD"
Exit Function
Else
GScore = GScore + HSc(i) 'add the scores
End If
Next i
End Function

Function NetF9(GSc As Range, HC As Range) 'front9 net score, gross - ½
handicap
If GSc = "" Then
NetF9 = ""
ElseIf WorksheetFunction.IsText(GSc) Then
NetF9 = GSc
Else
NetF9 = GSc - (WorksheetFunction.RoundUp(HC / 2, 0))
End If
End Function

Function NetB9(GSc As Range, HC As Range)
If GSc = "" Then
NetB9 = ""
ElseIf WorksheetFunction.IsText(GSc) Then
NetB9 = GSc
Else
NetB9 = GSc - (HC - (WorksheetFunction.RoundUp(HC / 2, 0)))
End If
End Function

Value is the default property for Range, I don't like to let it default, I
prefer to be explicit

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Bob Many thk
Any ideas on Q2. Also the importance of 'value'.
Rgds
KN
 
K

Kieranz

Hi Bob
Sorry didnt get back early; on dialup from this part of the planet.
But many many thks. I still can't get my debug to work properly, keeps
reverting to function codes. Must be my coding confusing poor VBE.
<sigh>
Thats to all you MVPs; VB would be tough without you guys out there.
Thks again
God bless
Rgds
K
 
B

Bob Phillips

Kieranz,

Can you post your workbook somewhere (eg http://cjoint.com) so we can see
whether it is environmental or not?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Hi Bob
Sorry didnt get back early; on dialup from this part of the planet.
But many many thks. I still can't get my debug to work properly, keeps
reverting to function codes. Must be my coding confusing poor VBE.
<sigh>
Thats to all you MVPs; VB would be tough without you guys out there.
Thks again
God bless
Rgds
K
 
K

Kieranz

I am sorry or is it ignorant; i don't how or where to post the file for
U to look at the codes.
Help or directions appreciated. cjoint.com is all french and therefore
very french to me.
Rgds
K
 
B

Bob Phillips

It was just an example site, the one that I use, but there are others. You
don't need to be able to read French to do it, its is self-explanatory.

Click the Browse button and go and find your file, then press the 'Créer le
lien Cjoint' button. CJoint will then give you a url that you can post.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

I am sorry or is it ignorant; i don't how or where to post the file for
U to look at the codes.
Help or directions appreciated. cjoint.com is all french and therefore
very french to me.
Rgds
K
 
K

Kieranz

Hi Bob
Pls find the URL i hope its correct.
Le lien a été créé: http://cjoint.com/?jbroVSA7UI
The file is zipped.
Many, many thks. I will be using it on Sun for our Tournament. You will
notice that I do long a long winded way to get something done. Your
comments will be most appreciated.
Thks and god bless
K
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Check Workbook name currently opens is correctly 4
Beginner struggling with if, then, else if etc... 2
Passing Paramiters and UDF Question 1
Unable to set the Text Property of the Characters class 4
Excel seems to hang while running a UDF 0
pop up widow 2
custom error messages 2
Code optimisation 5

Top