0

Problem

I have an Excel worksheet with a column of text which is data, and some rules in mind for what text values should be formatted with what font and font size.

Specific Example

Cell A1: "Text"

Cells A2 .. (Col. A): strings of text (from a few words to a few sentences) to be optionally formatted with applicable font and font size

Cell B1: "Type of Text"

Cells B2 ... (Col. B): single-word strings such as "heading", "requirement", "note"

Rules in mind:

If string in Col. B is "heading", make string in Col. A font size 18

If string in Col. B is "requirement", make string in Col. A font Calibri

If string in Col. B is "note", make string in Col. A style "italic"

Constraint

Excel, as a spreadsheet, is all about functional programming (formulas) and parallel processing (recalculation). Yes I can write procedural VBA but, for me it goes against the spirit of spreadsheets (even though procedural programming is so popular that Excel and predecessors have offered that for a long time).

Question

Any ideas on how to do it other than special-purpose VBA procedural programming?

What does not seem to solve

Excel conditional formatting does not solve because it expressly does not allow setting those particular cell attributes.

Excel UDFs (User Defined Functions) do not solve because they do not affect other cells. As expected for functions. And even if I wanted to make a function which duplicates the unformatted text but with a style or size based on value, UDFs do not affect the "environment" - only can return data.

And I found here that I can't even put VBA code as text in Excel worksheet cells and then evaluate the text as VBA -- to make a simple generic VBA procedural processing engine for font style and size VBA statements that I would put in cells in my worksheet.

(If any of this negative information is incorrect, and one of the above will work, please correct me.)

VBA special-purpose, procedural programming example -- what works but trying to avoid

Public Sub IterateThroughRangeSetFontStyleSize()

Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range

Set wb = Application.ActiveWorkbook
Set ws = wb.Worksheets("myWorkSheet")
Set rng = ws.Range("b2", "b4")

For Each cell In rng.Cells
    If LCase(cell.Text) = "bold" Then cell.Offset(0, -1).Font.Bold = True
    If LCase(cell.Text) = "italic" Then cell.Offset(0, -1).Font.Italic = True
    If LCase(cell.Text) = "large" Then cell.Offset(0, -1).Font.Size = 18
Next cell

End Sub

Some ideas that seem too difficult to me but maybe someone will say how they are not

  • Configure Excel to allow VBA to write to VBA editor (VBE), read the worksheet column of data, calculate desired font style and size based on the rules, and autogenerate VBA procedural code to then update the font style and size of the column of data

  • Export or Save As the Excel file as XML, write an XSL style sheet that would format per the rules, open (import?) the XML and tell Excel to use the XSL style sheet

What's Needed

Some outside-the-box, keep-it-simple thinking!

Community
  • 1
  • 1
talkaboutquality
  • 1,312
  • 2
  • 16
  • 34
  • On the autogenerated code idea, I realize I don't have to adjust Excel to allow writing to VBE. I could autogenerate VBA code to an external text file, then copy/paste into VBE and run it. But ugly! – talkaboutquality Dec 27 '12 at 21:50
  • Some thoughts before going to sleep: Sheet 1: Data; Sheet 2: Function describing the cell formatting for a corresponding cell on Sheet 1 (resulting in `bgcolor="#ff00ff", font size="3"` etc.); Sheet 3: Data and format merged, giving XSL. The only VBA needed is to write one UDF for sheet 2 and the other one for Sheet 3. – Jüri Ruut Dec 27 '12 at 22:07
  • Thanks @Jüri Ruut I like the separation of concerns by worksheet! I see how to write UDF to populate cells in sheet 2. But if Excel UDFs cannot affect the "environment" of a cell -- font and font size -- then how would I write the UDF for sheet 3? But great start. And if answer is "can't write a _UDF_ for sheet 3" but can write a _generic_ _procedural_ macro that copies all filled cells in sheet 1 to sheet 3 and then formats all cells in sheet 3 according to cell formatting in sheet 2, then please post that as an answer instead of a comment and I'll accept it! Or I'll do it and credit you. :-) – talkaboutquality Dec 28 '12 at 10:54
  • Are you completely against creating an excel add-in to do the formatting? If you program it correctly, it can work like conditional formatting. – scott Dec 28 '12 at 14:17
  • Hi @scott, well, staying within Excel's built-in functionality (formulas, UDFs) is much easier and more portable. It is true that, with the solution in the chosen answer, the generic but domain-specific VBA procedure is an extension to Excel. Your idea sounds conceptually just as simple: if conditional formatting as-is is not good enough for me, write my own conditional formatting menu feature as an Excel add-in. I suppose if it were not too difficult, I would consider it. Have a single excellent Excel add-in tutorial link? I would look at it for future learning. – talkaboutquality Dec 29 '12 at 16:32

2 Answers2

1

I'm not sure if this question is a level or not -- you don't want to write VBA code because "it goes against the spirit of Excel"? VBA was introduced in Excel 5.0, and prior to that there was a procedural macro language. Automation has always been integral to Excel's popularity (and the primary reason for the rise of Lotus 1-2-3 before it).

Bold and italics can be manipulated via Conditional Formatting.

For font size, yes, you'll need to write some code. Perhaps some sample data or indications of the type of data (text, integers, real numbers, ...) will initiate some suggestions.

ExactaBox
  • 3,235
  • 16
  • 27
  • +1 for the correction regarding conditional formatting of bold and italic properties. The rest of your answer makes good sense too. – Doug Glancy Dec 27 '12 at 23:13
  • Thanks @ExactaBox for the correction about font style! (It's actually font -- not font style -- and font size, that are grayed out in Excel Conditional Formatting dialog.) Regarding "spirit of Excel" I really meant "spirit of spreadsheets" as I see it, even though procedural programming so swamps functional programming in popularity that your comments about procedural automation in successful spreadsheet products are totally correct. I updated the question a bit in recognition of your comments. I will add a specific example too as you suggest. – talkaboutquality Dec 28 '12 at 10:35
1

It could be possible to keep data and formatting information separately:

Sheet 1: Data;
Sheet 2: Function describing the cell formatting for a corresponding cell on Sheet 1 (resulting in bgcolor="#ff00ff", font size="3" etc.);
Sheet 3: Data and format merged, giving XSL.

The only VBA needed is to write one UDF for Sheet 2 and the other one for Sheet 3.

Jüri Ruut
  • 2,500
  • 17
  • 20
  • Thanks @Jüri Ruut! This looks like the way to go. I will make some edits to your answer based on what I've realized. – talkaboutquality Dec 29 '12 at 16:12
  • And here's what I learned. The problem was an incomplete Excel feature: neither conditional formatting nor UDF allow formatting the font or font size. So the solution did not need to be something totally new -- just needed to complete these features. If we couldn't apply the formatting, even via UDF, at least calculate it via UDF. And if VBA for Excel can't execute text as code, write a generic but domain-specific VBA procedure that will use 'if' or 'case' statements to interpret and execute the formatting codes in the intermediate worksheets. – talkaboutquality Dec 29 '12 at 16:26