I'd like to count the number of values that a user enters in an excel simple
addition calculation.

For example, if the cell has "=17+256", I'd like to get the count of "2".
If the cell has "=101+2+0+65", I'd like to get the count of "4". Is there a
way to parse this 'function' string?

Everything I've tried so far starts *after* the cell value has been
calculated (e.g. it's working with a single value of 273 and a single value
of 168 for the two examples above, respectively).

Thanks,
- Cathy

Hi Cathy,

Use this User Defined Function.
If you're new to VBA, look here first:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Function CountAdd(a As Range) As Double
Dim i As Long
Dim b As String
b = a.Formula
For i = 1 To Len(b)
If Asc(Mid$(b, i, 1)) = 43 Then CountAdd = CountAdd + 1
Next i
CountAdd = CountAdd + 1
End Function

--
Kind regards,

Niek Otten


"CRH" wrote in message
...
I'd like to count the number of values that a user enters in an excel
simple
addition calculation.

For example, if the cell has "=17+256", I'd like to get the count of "2".
If the cell has "=101+2+0+65", I'd like to get the count of "4". Is there
a
way to parse this 'function' string?

Everything I've tried so far starts *after* the cell value has been
calculated (e.g. it's working with a single value of 273 and a single
value
of 168 for the two examples above, respectively).

Thanks,
- Cathy

If a plus sign is always going to be the separator, this UDF will work. To
implement, simply type =CountValues(A1) in your spreadsheet.

Function CountValues(cell)
form = cell.Formula
Length = Len(form)
Count = 0
For i = 1 To Length
If Mid(form, i, 1) = "+" Then
Count = Count + 1
End If
Next i
CountValues = Count + 1
End Function

--
Regards,
Dave


"CRH" wrote:

I'd like to count the number of values that a user enters in an excel simple
addition calculation.

For example, if the cell has "=17+256", I'd like to get the count of "2".
If the cell has "=101+2+0+65", I'd like to get the count of "4". Is there a
way to parse this 'function' string?

Everything I've tried so far starts *after* the cell value has been
calculated (e.g. it's working with a single value of 273 and a single value
of 168 for the two examples above, respectively).

Thanks,
- Cathy

That *almost* worked. Thank you! The only problem is that I have to *OPEN*
'personal.xls' for the function to work.

Based on reading the reference material a few times, this is what I did:
1. Open C;\data\MS\excel\startup\personal.xls (I did a find to locate
'personal.xls')
2. Click on "Tools", "Macro", "Visual Basic Editor"
3. Cut and Paste the "CountAdd" function from your response into the white
board area.
4. Save in Visual Basic Editor, and close the editor.
5. Open my data spreadsheet.
6. Specify the function CountAdd, including its source location of
personal.xls: =personal.xls!CountAdd(<the cell location with the entry I
needed to parse)

Then it worked beautifully.

But once I closed all my spreadsheets, then I tried to open my data
spreadsheet, I got the prompt: "update" "don't update" links. If I chose
"update", I got the error that the links could not be updated. But, if I
then opened the personal.xls spreadsheet, all of the function calls resolved.

Otherwise, if I chose "don't update links", the data sheet opened, and any
previous calls to "CountAdd" had valid values. But if I entered a new call
to "CountAdd", the new one and any previous ones all became unresolved. In
that case, also, if I opened personal.xls, then everything resolved.

How do I make it so that I can open my data spreadsheet, and issue
additional calls to the "CountAdd" function, and NOT have to open
personal.xls?

- Cathy

"CRH" wrote:

I'd like to count the number of values that a user enters in an excel simple
addition calculation.

For example, if the cell has "=17+256", I'd like to get the count of "2".
If the cell has "=101+2+0+65", I'd like to get the count of "4". Is there a
way to parse this 'function' string?

Everything I've tried so far starts *after* the cell value has been
calculated (e.g. it's working with a single value of 273 and a single value
of 168 for the two examples above, respectively).

Thanks,
- Cathy

Place Personal.xls in your XLSTART folder, not the startup folder.

C:\Documents and Settings\user\Application Data\Microsoft\Excel\XLSTART

It will then open with each session of Excel.

First time you open Excel after moving Personal.xls to the path above,
Personal.xls will open visible.

Go to WindowHide

When you close Excel you will be notofied that you made changes to
Personal.xls and do you want to save these.

Click "Yes".

It will open hidden from now on and your macros and Functions will be
available.

I prefer to place macros and Functions in an Add-in.

Then you don't have to use the workbook name in your formula, just the macro
or Function name.

Only downside of an add-in is you don't see the macros in ToolsMacroMacros.


Gord Dibben Excel MVP

On Fri, 2 Dec 2005 14:33:04 -0800, CRH wrote:

That *almost* worked. Thank you! The only problem is that I have to *OPEN*
'personal.xls' for the function to work.

Based on reading the reference material a few times, this is what I did:
1. Open C;\data\MS\excel\startup\personal.xls (I did a find to locate
'personal.xls')
2. Click on "Tools", "Macro", "Visual Basic Editor"
3. Cut and Paste the "CountAdd" function from your response into the white
board area.
4. Save in Visual Basic Editor, and close the editor.
5. Open my data spreadsheet.
6. Specify the function CountAdd, including its source location of
personal.xls: =personal.xls!CountAdd(<the cell location with the entry I
needed to parse)

Then it worked beautifully.

But once I closed all my spreadsheets, then I tried to open my data
spreadsheet, I got the prompt: "update" "don't update" links. If I chose
"update", I got the error that the links could not be updated. But, if I
then opened the personal.xls spreadsheet, all of the function calls resolved.

Otherwise, if I chose "don't update links", the data sheet opened, and any
previous calls to "CountAdd" had valid values. But if I entered a new call
to "CountAdd", the new one and any previous ones all became unresolved. In
that case, also, if I opened personal.xls, then everything resolved.

How do I make it so that I can open my data spreadsheet, and issue
additional calls to the "CountAdd" function, and NOT have to open
personal.xls?

- Cathy

"CRH" wrote:

I'd like to count the number of values that a user enters in an excel simple
addition calculation.

For example, if the cell has "=17+256", I'd like to get the count of "2".
If the cell has "=101+2+0+65", I'd like to get the count of "4". Is there a
way to parse this 'function' string?

Everything I've tried so far starts *after* the cell value has been
calculated (e.g. it's working with a single value of 273 and a single value
of 168 for the two examples above, respectively).

Thanks,
- Cathy

Whoo-hoo! That got it. Thanks!!
- Cathy

"Gord Dibben" wrote:

Place Personal.xls in your XLSTART folder, not the startup folder.

C:\Documents and Settings\user\Application Data\Microsoft\Excel\XLSTART

It will then open with each session of Excel.

First time you open Excel after moving Personal.xls to the path above,
Personal.xls will open visible.

Go to WindowHide

When you close Excel you will be notofied that you made changes to
Personal.xls and do you want to save these.

Click "Yes".

It will open hidden from now on and your macros and Functions will be
available.

I prefer to place macros and Functions in an Add-in.

Then you don't have to use the workbook name in your formula, just the macro
or Function name.

Only downside of an add-in is you don't see the macros in ToolsMacroMacros.


Gord Dibben Excel MVP

On Fri, 2 Dec 2005 14:33:04 -0800, CRH wrote:

That *almost* worked. Thank you! The only problem is that I have to *OPEN*
'personal.xls' for the function to work.

Based on reading the reference material a few times, this is what I did:
1. Open C;\data\MS\excel\startup\personal.xls (I did a find to locate
'personal.xls')
2. Click on "Tools", "Macro", "Visual Basic Editor"
3. Cut and Paste the "CountAdd" function from your response into the white
board area.
4. Save in Visual Basic Editor, and close the editor.
5. Open my data spreadsheet.
6. Specify the function CountAdd, including its source location of
personal.xls: =personal.xls!CountAdd(<the cell location with the entry I
needed to parse)

Then it worked beautifully.

But once I closed all my spreadsheets, then I tried to open my data
spreadsheet, I got the prompt: "update" "don't update" links. If I chose
"update", I got the error that the links could not be updated. But, if I
then opened the personal.xls spreadsheet, all of the function calls resolved.

Otherwise, if I chose "don't update links", the data sheet opened, and any
previous calls to "CountAdd" had valid values. But if I entered a new call
to "CountAdd", the new one and any previous ones all became unresolved. In
that case, also, if I opened personal.xls, then everything resolved.

How do I make it so that I can open my data spreadsheet, and issue
additional calls to the "CountAdd" function, and NOT have to open
personal.xls?

- Cathy

"CRH" wrote:

I'd like to count the number of values that a user enters in an excel simple
addition calculation.

For example, if the cell has "=17+256", I'd like to get the count of "2".
If the cell has "=101+2+0+65", I'd like to get the count of "4". Is there a
way to parse this 'function' string?

Everything I've tried so far starts *after* the cell value has been
calculated (e.g. it's working with a single value of 273 and a single value
of 168 for the two examples above, respectively).

Thanks,
- Cathy