Excel VBA code to concatenate with custom characters

Sometimes it is necessary to join the contents of cell with custom characters like +,-,:,|, etc. This can be achieved by using excel inbuilt function CONCATENATE. The problem with this function is you will have to specify the character every time. The function listed below will help to simplify this problem.

The function can take multiple arguments just like =SUM() function.

Example:

=CONCATENATE_WITH_CUSTOM_CHARACTER("+", a1:c1,b8)

the result: all the cell values of a1:c1 and b8 will be displayed separated by “+” sign.

The code:

Function CONCATENATE_WITH_CUSTOM_CHARACTER(SEPERATOR, ParamArray args() As Variant)
' this function can take multiple parameters and return each cell value seperated by "+"
Dim i, j
Dim out
For i = LBound(args) To UBound(args)
    If IsNull(args(i)) = False Then 'check null value
            If IsArray(args(i)) = False Then 'check if arg is array
            'no array do usual
                If out = "" Then
                    out = args(i)
                ElseIf args(i) = "" Then
                    out = out
                 Else
                    out = out & SEPERATOR & args(i)
                End If
            Else  ' arg is an array, do for array
                For j = 1 To args(i).Count
                    If out = "" Then
                        out = args(i)(j)
                    ElseIf args(i)(j) = "" Then
                        out = out
                    Else
                        out = out & SEPERATOR & args(i)(j)
                    End If
                Next j
            End If ' array check end
    End If 'null check end

Next i
CONCATENATE_WITH_CUSTOM_CHARACTER = out

End Function

Advertisement

Breaking Excel passoword

The password protection of MS-Excel are not intended for security of data but for security of input and output of worksheet.

You can crack the password of the protected worksheet by using following VBA procedure.


Sub PasswordBreaker()
'Author unknown
'Breaks worksheet password protection.
Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub

This piece of code was found here