1

Please forgive if the question has been asked before but I'm new to Excel VBA and stackoverflow.

I'm trying to write a custom function to merge two cells together maintaining the original formatting in the two cells. I.e. Cell1 contains "Hello" in bold and Cell2 " World" in normal then I want Cell3 (containing the formula) to return "Hello World". Obviously I need to use the .Characters call to loop through the original strings and apply that to the final string. So I can't just return a string ('cause I lose the formatting) I have to return a .Range object. But I descended into a world of pain before I even got to the .Characters bit.

My first attempt looked like this:

Public Function Merge_Formats(inCellOne As Range, inCellTwo As Range) As Range

On Error GoTo ErrorTrap

Merge_Formats.Text = inCellOne.Text & inCellTwo.Text

Exit Function
ErrorTrap:

    MsgBox Err.Description

End Function

That returned: Object variable or With block variable not set

Then I tried accessing the cell that contains my function:

Public Function Merge_Formats(inCellOne As Range, inCellTwo As Range) As Range

Dim tempCell As Range

On Error GoTo ErrorTrap

Set tempCell = Application.Caller

tempCell.Text = inCellOne.Text & inCellTwo.Text

Exit Function
ErrorTrap:

    MsgBox Err.Description

End Function

Which throws this: Object Required

I also tried (as a nasty workaround) to pass a third cell to be used as a staging post:

Public Function Merge_Formats(inCellOne As Range, inCellTwo As Range, tempCell As Range) As Range

On Error GoTo ErrorTrap

tempCell.Text = inCellOne.Text & inCellTwo.Text

Exit Function
ErrorTrap:

    MsgBox Err.Description

End Function

This also returns the Object Required error message.

I know I can reference another cell:

Function SimpleTest() As Range

Dim tempCell As Range

Set tempCell = Range("A1")

Set SimpleTest = tempCell

End Function

Clearly I'm out of my depth here. Is it possible to write a custom function returning a .Range object that is not merely referencing another but allowing the contents to be manipulated. If so, how is it done?

Improve this question
8
  • 1
    If you're trying to write a function to call in a cell, this will not work. A function cannot return anything other than a value to its cell, not formatting. Commented Oct 11, 2024 at 8:50
  • How do you want to call the function? As Rory wrote, you can't have it as UDF (a function that you call from a cell), because formatting is not allowed for such a function. You could think about the Worksheet_Change-Trigger so that your code is called every time something is changed in your sheet. Commented Oct 11, 2024 at 8:57
  • Hi Rory. That's exactly what I was trying to do, namely return a formatted string, not just a string. From your comment, you're saying a custom function cannot return a .Range object only a string or a number. Commented Oct 11, 2024 at 9:10
  • Hi FunThomas. My intention was to call the function from a cell using: =Merge_Formats(A1,A2) where the cell with the formula would end up having the concatenated contents of A1 and A2. Commented Oct 11, 2024 at 9:11
  • 1
    Sidenote: Range.Text property is Read only. Commented Oct 11, 2024 at 9:14

2 Answers 2

Reset to default
1

A documented limitations of a UDF can be overpassed in some circumstances, but there are things which can be done and others cannot, using some tricks. I mean, you can write in a different cell, you can change the interior color of that cell and do some other things.

So, copy the next code in a standard module:

Function JoinThem(src As Range, src1 As Range, dest As Range)
    dest.Parent.Evaluate "ChangeOtherCell(" & dest.address(0, 0) & "," _
                        & src.address(0, 0) & "," & src1.address(0, 0) & ")"

    JoinThem = "(" & src.address & " and " & src1.address & " ->" & dest.address & ")" 'or whatever return value is useful...
End Function
Private Sub ChangeOtherCell(C1 As Range, C2 As Range, c3 As Range)
    C1.Value = C2.Value & " " & c3.Value

    getBold C1, C2
End Sub
Sub getBold(C1 As Range, C2 As Range)
    Debug.Print "It is called..." 'the sub is called!
    With C1
        .Interior.Color = vbYellow 'it works
        .Characters(1, Len(C2.Value)).Font.Bold = True 'it does not work...
        .BorderAround , xlMedium                       ''it does not work...
    End With
End Sub

Now place the word "Hello" in and call (I used "I8"), bold it and "World!" in another one (I used "I9"). Then call JoinThem from a cell (as UDF). I called it from "K8". It will join/concatenate the two mentioned cells in "Q8", calling it as:

 =JoinThem(I8,I9,Q8)

It will join the two cells content in "Q8", even if Microsoft documentation states that it is not possible and it will color that cell interior in yellow.

Even if the intermediar called getBold sub contains lines able to format the font characters and add a border, the do not work as it should in call being done from VBA.

For instance, selecting "Q8" and using the next testing Sub:

Sub testGetBold()
   Dim C1 As Range, C2 As Range
   Set C1 = ActiveCell: Set C2 = C1.Offset(, -8)
   
   getBold C1, C2
End Sub

the respective cell will be formatted as required. But, even if some stated limitations can be extended, other ones can not...

It is only a testing solution. If it would (completely) work, it would not be so difficult to bold the characters only for bolded cell, even both of them...

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Considering all limitations, I created the solution to merge cells keeping characters formatting:

Option Explicit

Sub MergePreserveFormatting()
  Dim r As Range, s As String, f As Font
  Dim i As Long, k As Long
  For Each r In Selection.Cells
    If r.Address <> ActiveCell.Address Then s = s & r.Text
  Next
  ActiveCell = s
  For Each r In Selection.Cells
    If r.Address <> ActiveCell.Address Then
      For i = 1 To Len(r)
        Set f = r.Characters(Start:=i, Length:=1).Font
        With ActiveCell.Characters(Start:=k + i, Length:=1).Font
            .Name = f.Name
            .FontStyle = f.FontStyle
            .Size = f.Size
            .Strikethrough = f.Strikethrough
            .Superscript = f.Superscript
            .Subscript = f.Subscript
            .OutlineFont = f.OutlineFont
            .Shadow = f.Shadow
            .Underline = f.Underline
            .Color = f.Color
            .Bold = f.Bold
            .TintAndShade = f.TintAndShade
            .Italic = f.Italic
        End With
      Next
      k = k + Len(r)
    End If
  Next
End Sub

You need to select the range to merge:

  1. Starting from the additional cell on the side of the range (A1:B2 is the range to merge, A1:C1 is selected starting from C1, C1 is the active cell)

enter image description here

OR

  1. Selecting cells holding the Ctrl key and, at the end, select the target cell still holding the Ctrl key (select A1:B1 the press and hold Ctrl and select C4).

enter image description here

Run the macro. The active cell will get the result. The range to merge can contain any number of cells (may be, it can be a limit in the Characters object, I don't know).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.