To modify Chandoo's macro for 3rd parameter (omittig cell value)

[sdfjh87687]

Please help me modify this:

Function concat(useThis As Range, Optional delim As String) As String
' this function will concatenate a range of cells and return one string
' useful when you have a rather large range of cells that you need to add up
Dim retVal, dlm As String
retVal = ""
If delim = Null Then
dlm = ""
Else
dlm = delim
End If
For Each cell In useThis
If CStr(cell.Value) <> "" And CStr(cell.Value) <> " " Then
retVal = retVal & CStr(cell.Value) & dlm
End If
Next
If dlm <> "" Then
retVal = Left(retVal, Len(retVal) - Len(dlm))
End If
concat = retVal
End Function

To able enter third optional omitting parameter.
I would like to omit cells with FALSE value. But I don't know whether to set in as FALSE or "FALSE". Is it excel's keyword ? (no string "string") Also does makes difference when I manually type into cell FALSE, and when formula makes it ?
I read CONCATENATE function has limit to operate with max string length at 255 signs.
If I use operator &, it has no limit. Please tell me, which thing use Chandoo's macro ?


Thanks for help



//edit:
I was googling meanwhile and found these 2 code lines:
If cell.Value = "FALSE" Then
cell.Ignore
Look how I put inside, but it didn't work, didn't crash either.

Function concat(useThis As Range, Optional delim As String) As String
' this function will concatenate a range of cells and return one string
' useful when you have a rather large range of cells that you need to add up
Dim retVal, dlm As String
retVal = ""
If delim = Null Then
dlm = ""
Else
dlm = delim
End If
For Each cell In useThis
If cell.Value = "FALSE" Then
cell.Ignore
If CStr(cell.Value) <> "" And CStr(cell.Value) <> " " Then
retVal = retVal & CStr(cell.Value) & dlm
End If
Next
If dlm <> "" Then
retVal = Left(retVal, Len(retVal) - Len(dlm))
End If
concat = retVal
End Function

 

[Luke M]

UDf would be:

Function concat(useThis As Range, Optional dlm As String) As String
' this function will concatenate a range of cells and return one string
' useful when you have a rather large range of cells that you need to add up
Dim retVal As String
Dim cell As Range
retVal = ""

For Each cell In useThis
   'Omit cells with blanks and that equal FALSE

    If CStr(cell.Value) <> "" And _
    CStr(cell.Value) <> " " And _
    UCase(CStr(cell.Value)) <> "FALSE" Then
        retVal = retVal & CStr(cell.Value) & dlm
    End If
Next
concat = Left(retVal, Len(retVal) - Len(dlm))

End Function

Note that you are limited to 32,767 characters in each cell, so this would be the upper limit you can do.

 

[John()]

Ok after going through the code this is how i changed it .... might be suitable to what you need ....
it works for a range of cells in a column using just the first option in the function and not using the optional part eg used like =concat(a1:a10)
if a4 is FALSE its not concated into the string

Luke M got his in ahead of me wasnt there when i was posting ... so go with his as he would know more than me ... mine works but limited to using only the first option in the function

Function concat(useThis As Range, Optional delim As String) As String
' this function will concatenate a range of cells and return one string
' useful when you have a rather large range of cells that you need to add up
Dim retVal, dlm As String
retVal = ""
If delim = Null Then
dlm = ""
Else
dlm = delim
End If

For Each cell In useThis
If CStr(cell.Value) <> ""And CStr(cell.Value) <> " "Then
'Added this line ......
If CStr(cell.Value) = False Then
'Added this line and the else
retVal = retVal & dlm
Else
retVal = retVal & CStr(cell.Value) & dlm
End If
End if
Next
If dlm <> "" Then
retVal = Left(retVal, Len(retVal) - Len(dlm))
End If
concat = retVal
End Function

 

[sdfjh87687]

Thank you both. However, I would need it for future like I said above. Three parameters. First is standard as range of cells to concatenate, second is optional as delimiter, third will be also optional as for value to be ommited.
Please keep in code also that hint text when I start typing values into that function.

 

[John()]

LOL sorry misunderstood the first time ... understand now ....
have adjusted to incoprate a third option .... so you can use just the first option on its own / first option with second and third option /first option with second / first option with third ..... if not using second do like concat(first,,third)

Function concat(useThis As Range, Optional delim As String, Optional opt3 As String) As String
' this function will concatenate a range of cells and return one string
' useful when you have a rather large range of cells that you need to add up
Dim retVal, dlm, pram3 As String
retVal = ""
If pram3 = Null Then
pram3 = ""
Else
pram3 = opt3
End If
If delim = Null Then
dlm = ""
Else
dlm = delim
End If
For Each cell In useThis
If CStr(cell.Value) <> "" And CStr(cell.Value) <> " " Then
If CStr(cell.Value) <> pram3 Then
'Added this line and the else
retVal = retVal & CStr(cell.Value) & dlm
Else
retVal = retVal
End If
End If
Next
If dlm <> "" Then
retVal = Left(retVal, Len(retVal) - Len(dlm))
End If
concat = retVal
End Function

 

[Luke M]

Modifying what I had above:

Function concatIf(useThis As Range, Optional dlm As String, Optional ignoreThis As String) As String
' this function will concatenate a range of cells and return one string
' useful when you have a rather large range of cells that you need to add up
' Doesn't include any cells matching 3rd parameter
Dim retVal As String
Dim cell As Range
retVal = ""

For Each cell In useThis
   'Omit cells with blanks and that equal FALSE

    If CStr(cell.Value) <> "" And _
    CStr(cell.Value) <> " " And _
    UCase(CStr(cell.Value)) <> UCase(ignoreThis) Then
        retVal = retVal & CStr(cell.Value) & dlm
    End If
Next
concatIf = Left(retVal, Len(retVal) - Len(dlm))

End Function

For more ConcatIf type examples, see Hui's comment from original post:
http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/#comment-958816

 

[sdfjh87687]

Thanks, used Luke's code.
To be more specific, what I meant by 'hinting text' while writing function into cell is this:

and this:

Because for later times, when I will forget about this. Hints are usefull. However, is a way how get a look into excel's original built in functions, to see how they coded it in ? (that hints)

 

[Luke M]

Check out this thread for several good ideas:
http://stackoverflow.com/questions/4262421/how-to-put-a-tooltip-on-a-user-defined-function

 

[sdfjh87687]

I have read it all, tried some things. But it didn't work to me.
Object browser, right click properties, description :text. It was saved but didnt show.
Then thing with ctrl+shift+a works just only if I press it and shows not really a hints. It does this: =concatxx(useThis;delim)

Really not possible to do it ? Shame microsoft (I use excel 2010)