Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > UDF's Problems; Is my Fundamentals Skewed
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 55
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 55
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 55
Default 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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default 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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
UDF's shane Excel Discussion (Misc queries) 4 March 24th 10 03:37 PM
looking for shape of right-skewed, left-skewed symmetric how do Sharon Setting up and Configuration of Excel 1 December 28th 06 12:06 AM
Windows File Dialog box problem from "Office 2000 VBA Fundamentals Peter Rooney Excel Programming 11 March 7th 06 12:54 PM
Many low values, one high value- how do I avoid skewed chart? bpmedtech Charts and Charting in Excel 1 March 3rd 06 03:39 AM
Charting a skewed bell curve Remeez Charts and Charting in Excel 2 December 22nd 04 11:47 AM


All times are GMT +1. The time now is 10:33 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement