UDF's Problems; Is my Fundamentals Skewed
 

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

UDF's Problems; Is my Fundamentals Skewed
 

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)

"Kieranz" wrote in message
ups.com...
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

UDF's Problems; Is my Fundamentals Skewed
 

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

Bob Phillips wrote:
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)

"Kieranz" wrote in message
ups.com...
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

UDF's Problems; Is my Fundamentals Skewed
 

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)

"Kieranz" wrote in message
oups.com...
Bob Many thk
Any ideas on Q2. Also the importance of 'value'.
Rgds
KN

Bob Phillips wrote:
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)

"Kieranz" wrote in message
ups.com...
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

UDF's Problems; Is my Fundamentals Skewed
 

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


Bob Phillips wrote:
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)

"Kieranz" wrote in message
oups.com...
Bob Many thk
Any ideas on Q2. Also the importance of 'value'.
Rgds
KN

Bob Phillips wrote:
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)

"Kieranz" wrote in message
ups.com...
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

UDF's Problems; Is my Fundamentals Skewed
 

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)

"Kieranz" wrote in message
oups.com...
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


Bob Phillips wrote:
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)

"Kieranz" wrote in message
oups.com...
Bob Many thk
Any ideas on Q2. Also the importance of 'value'.
Rgds
KN

Bob Phillips wrote:
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)

"Kieranz" wrote in message
ups.com...
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

UDF's Problems; Is my Fundamentals Skewed
 

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
Bob Phillips wrote:
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)

"Kieranz" wrote in message
oups.com...
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


Bob Phillips wrote:
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)

"Kieranz" wrote in message
oups.com...
Bob Many thk
Any ideas on Q2. Also the importance of 'value'.
Rgds
KN

Bob Phillips wrote:
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)

"Kieranz" wrote in message
ups.com...
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

UDF's Problems; Is my Fundamentals Skewed
 

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)

"Kieranz" wrote in message
ups.com...
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
Bob Phillips wrote:
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)

"Kieranz" wrote in message
oups.com...
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


Bob Phillips wrote:
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)

"Kieranz" wrote in message
oups.com...
Bob Many thk
Any ideas on Q2. Also the importance of 'value'.
Rgds
KN

Bob Phillips wrote:
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)

"Kieranz" wrote in message
ups.com...
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

UDF's Problems; Is my Fundamentals Skewed
 

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

Bob Phillips wrote:
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)

"Kieranz" wrote in message
ups.com...
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
Bob Phillips wrote:
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)

"Kieranz" wrote in message
oups.com...
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


Bob Phillips wrote:
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)

"Kieranz" wrote in message
oups.com...
Bob Many thk
Any ideas on Q2. Also the importance of 'value'.
Rgds
KN

Bob Phillips wrote:
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)

"Kieranz" wrote in message
ups.com...
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