Inherit bolded substring from another cell

[dexiongmao]

Hello,
I want to do something like the image attached: I have a couple of columns to insert and store data, and a large column to display the data combined together for a better view, which is generated by VBA. There are some substrings in the input cells that are bolded, and I want to inherit the "bold" in my display cell.

I don't know if there is a method to get the string from a cell and put it to another, without messing up the partially bold format.

What I have in mind is to loop through every single character in every single input cells, check if bolded, then bold the corresponding character in the display cell. This should be able to solve the problem, but I just don't really like this character-by-character method (feels like a slow program). Are there better ways to do this?

Sample code for my thought

Please Login or Register  to view this content.

Thank you very much.

[6StringJazzer]

Formatting like this is applied as an attribute of individual characters or strings of characters. There is no way to find out what characters are bold without checking each one. For example, there is no built-in way to get the index of the first and last bold characters.

[dexiongmao]

Thank you for reply.
So this means my approach is actually the best we can do?

[6StringJazzer]

What you did is how I would do it. I will add that someone may have an idea of how to improve it, but like I said there is nothing built into to relieve the pain.

It would be interesting to write a class that would provide a utility for formatting text like this, but it would be a lot more code and less efficient. What you have is probably optimized for your particular situation.

[Artik]

dexiongmao, in my opinion, the code execution time can be significantly shortened.

Notice:
1. When no characters in Cell are bold, Cell.Font.Bold returns False.
2. When all characters are bold, Cell.Font.Bold returns True.
3. When some characters are bold, Cell.Font.Bold returns Null.

With this knowledge, you can speed up the execution of the code when the second condition is met, because we know that the whole string from 1 to Len(Cell.Value) should be bold. If the third condition is met, you can also speed up the code. Do not change sign by sign, but search and change entire fragments (reading a property itself is several times faster than changing this property). You read many times but only change once.
By following the above rules, I think that the code execution time can be reduced by at least half.

I don't like the following code, but let it be an inspiration to optimize your code.

Please Login or Register  to view this content.

In my tests, I got a performance increase of 2-3 times (and even 6 to 13, depending on the complexity of the input texts). So it's probably worth fighting for.

Artik

[dexiongmao]

Thanks a lot!
This is exactly what I want. I had a strong feeling that my original code could be improved a lot.