Search:

# custom Sum function?

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

would you please tell me how to write a custom Sum() function?
that enables me to sum some cells by typing:
=customSum(cell1,cell2,cell3,...)

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

Share:

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

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.

Why can you not use
=SUM(C8,B10,D10,D12,B15,G8,F2,E2)

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
A1:Hello
B1:World
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 ...
combine+=cells(x).value
next x
end function

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

=A1 & B1

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

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")

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 llColour As Long

' What's the colour of the calling cell?

' 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

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

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
up on the screen when I remembered your posts.

msdn.microsoft.com/.../scrip
t56/html/jsmthfolderexists.asp

try the concatenate function.

=CONCATENATE(C8,B10,D10,D12,B15,G8,F2,E2)

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.