MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

custom Sum function?

  Asked By: Boell    Date: Jan 13    Category: MS Office    Views: 1106

would you please tell me how to write a custom Sum() function?
that enables me to sum some cells by typing:

* the number of variants in () depends on user's selection, such as:
=customSum(C8,B10,D10,D12,B15,G8,F2,E2) ... etc.



12 Answers Found

Answer #1    Answered By: Bradley Evans     Answered On: Jan 13

Is there a maximum?????????????????????????????

Answer #2    Answered By: Barak Levi     Answered On: Jan 13

Would putting a background colour on cells  give a better indication of
which cells you are choosing? It's also easier than typing in a string
of references.

function  could then go through each cell in a range and just sum
those of particular colours.

Answer #3    Answered By: Rosalie Holmes     Answered On: Jan 13

Why can you not use

Answer #4    Answered By: Zachary Larson     Answered On: Jan 13

i just want to know how to the SUM function  works. i need a function
that can combine some cells' value in one grid. for instance, there's
C1:=combine(A1,B1) <---- the value of C! will be "HelloWorld"
and the variants in combine() depends on user's selection  then it can
be like this
| A1 |
| B4 |
| G5 |
| ... |

maybe it will like this? but i am not sure.
function combine(a as range)
for each x in ...
next x
end function

Answer #5    Answered By: Russell Burns     Answered On: Jan 13

you dont want a sum, you want to concatonate (sp?) the two values.
Just use a '&' instead of a '+'

=A1 & B1

Answer #6    Answered By: Louis Mason     Answered On: Jan 13

ya... that's it. but can i use something like =A1 & "\n" & B1?
i want this two elements not in a same line.

Answer #7    Answered By: Hehet Chalthoum     Answered On: Jan 13

if you want to use a line break, you have to use "& CHR(10) &",
which looks like:
Range("c1") = Range("a1") & Chr(10) & Range("b1")

Answer #8    Answered By: Sean Grant     Answered On: Jan 13

Showing some un VBA experience there

Hmmmm.... I still like my colour idea...

Function fncCombine(rpCombineRange As Range)

Dim rlCel As Range
Dim slCombined As String
Dim slCallerAddress As String
Dim llColour As Long

' What's the colour of the calling cell?
slCallerAddress = Application.Caller.Address
llColour = Range(slCallerAddress).Font.Color

' Go through the range
' Concatonate all cells  with similar colours.
slCombined = ""
For Each rlCel In rpCombineRange
If rlCel.Font.Color = llColour Then
slCombined = slCombined & rlCel.Value
End If
Next rlCel
fncCombine = slCombined

End Function

Answer #9    Answered By: Huette Miller     Answered On: Jan 13

As far as I experienced, you cannot format cells  (like cahnging
font, colors, etc) within functions. It only works in Subroutines.

Answer #10    Answered By: Maria Miller     Answered On: Jan 13

I think I noticed from your first posts that you had written something
that looked like jscript or C. A lot of code examples in the microsoft
online docs are given not only in VB(Script) but in JScript and
sometimes C and C++ as well. I thought maybe that would be useful to
you. You could for example choose not to use VBA at all or to have a
look at the examples and compare the code to see how VBA/S does the
same as jscript.

The below link is to some microsoft documentation on the
FileSystemObject. Not relevant to your problem... just something I had
up on the screen when I remembered your posts.


Answer #11    Answered By: Elias Turner     Answered On: Jan 13

try the concatenate function.


Answer #12    Answered By: Kifah Malik     Answered On: Jan 13

yes, both Colourcell or Concatenate or "&" works fine! :D.
but is there any way to catch which cells  selected by user? i think
the original Sum function  can be a good example, but we don't know
how does it work...still thinking...

Didn't find what you were looking for? Find more on custom Sum function? Or get search suggestion and latest updates.