Make some words in Excel cell bold Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
User avatar
zvit
Posts: 224
Joined: 07 Nov 2017, 06:15

Make some words in Excel cell bold

Post by zvit » 28 Jun 2022, 13:55

I'm looping a selection of cells in Excel via For c In xlApp.selection.cells.
I'm writing text to each cell via a simple c.value:= FinalWord " — " glossory
It's then simple to make any looped cell have bold text via c.Cells.Font.Bold := True.

However, I can't figure out how to make only the first word of a cell bold. (The FinalWord " — " part)

Any ideas?

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Make some words in Excel cell bold  Topic is solved

Post by flyingDman » 28 Jun 2022, 14:41

Try:

Code: Select all

#\::
xl := ComObjActive("excel.application")
FinalWord := "testing"
Glossary := "test glossary"
input := FinalWord " — " Glossary
msgbox % len1 := strlen(strsplit(input, " — ").1)
for c in xl.selection.cells
	{
	c.value := input
	c.Characters(1, len1).Font.fontstyle := "Bold"
	}
Assuming you want all before " _ " in bold. To include " _ " add 3 to len1.
14.3 & 1.3.7

User avatar
zvit
Posts: 224
Joined: 07 Nov 2017, 06:15

Re: Make some words in Excel cell bold

Post by zvit » 28 Jun 2022, 15:39

Thanks, it worked!!
Probably won't help anyone, but here's the full code:

Code: Select all

FixGlossoryCase:

    SelectedCellsCount:= xlApp.selection.count
    For c In xlApp.selection.cells
    {
        if (c.column != 21) ;Fix later to: EX_ColHeaderName(rng) != "Glossory"
        {
            msgbox % "Only choose Glossory column"
            goto GetOutOfHere
        }
    }
    ;=========================
    ;// NOTE FOR PROGRESS BAR
    ;=========================
    global ProgressBar
    Gui, Add, Progress, xm w300 h20 vProgressBar -Smooth
    Gui, Show,x1456 y575, Fixing Glossory case...
    Gui, +AlwaysOnTop
    No := 100/SelectedCellsCount, N := 0

    For c In xlApp.selection.cells
    {
        if InStr(c.value," — ")
            Continue

        FinalWord := xlApp.Range(c.Cells.Offset(0,-14),c.Cells.Offset(0,-14)).value ;Fix later to column rng name
        STR_LowerCaseFirstLetter(FinalWord)

        glossory:= c.value
        glossory := Trim(glossory, " ")
        glossory:= STR_UpperCaseFirstLetter(glossory)
        FinalOutput:= FinalWord " — " glossory
        len1 := strlen(strsplit(FinalOutput, " — ").1)
        c.value:= FinalOutput
        c.Cells.Interior.Color := 0xC6EFCE
        c.Characters(1, len1+3).Font.fontstyle := "Bold"
        ;=========================
        ;// NOTE FOR PROGRESS BAR
        ;=========================
        GuiControl,, ProgressBar,% N += No
    }
    Gui, Destroy
GetOutOfHere:
return

User avatar
zvit
Posts: 224
Joined: 07 Nov 2017, 06:15

Re: Make some words in Excel cell bold

Post by zvit » 28 Jun 2022, 19:58

flyingDman wrote:
28 Jun 2022, 14:41
Try:

Code: Select all

#\::
xl := ComObjActive("excel.application")
FinalWord := "testing"
Glossary := "test glossary"
input := FinalWord " — " Glossary
msgbox % len1 := strlen(strsplit(input, " — ").1)
for c in xl.selection.cells
	{
	c.value := input
	c.Characters(1, len1).Font.fontstyle := "Bold"
	}
Assuming you want all before " _ " in bold. To include " _ " add 3 to len1.
Can you please tell me how you knew about putting a .1 at the end of the StrSplit? I don't see this mentioned in the documents here.

gregster
Posts: 8921
Joined: 30 Sep 2013, 06:48

Re: Make some words in Excel cell bold

Post by gregster » 28 Jun 2022, 20:26

The function StrSplit returns an array object. How to use objects is documented centrally on the Objects page:
https://www.autohotkey.com/docs/Objects.htm#Usage_Objects wrote:For all types of objects, the notation Object.LiteralKey can be used to access a property, array element or method, where LiteralKey is an identifier or integer and Object is any expression. Identifiers are unquoted strings which may consist of alphanumeric characters, underscore and, in [v1.1.09+], non-ASCII characters. For example, match.Pos is equivalent to match["Pos"] while arr.1 is equivalent to arr[1]. There must be no space after the dot.
This means, you are retrieving element 1 from that returned array...

User avatar
AlphaBravo
Posts: 586
Joined: 29 Sep 2013, 22:59

Re: Make some words in Excel cell bold

Post by AlphaBravo » 28 Jun 2022, 20:28

zvit wrote:
28 Jun 2022, 19:58
Can you please tell me how you knew about putting a .1 at the end of the StrSplit? I don't see this mentioned in the documents here.
whether you save the result to an object and then call the object or work on StrSplit directly is the same, I guess you just learn that by experience.

Code: Select all

FinalWord := "testing"
Glossary := "test glossary"
input := FinalWord " — " Glossary
word_array := strsplit(input, " — ")
MsgBox % word_array.1 "`n" word_array[1] "`n" strsplit(input, " — ").1 "`n" strsplit(input, " — ")[1]


@flyingDman I am just picking on you ;) , what is the purpose of StrSplit the variable you've just concatenated?

Code: Select all

FinalWord := "testing"
Glossary := "test glossary"
input := FinalWord " — " Glossary
MsgBox % len1 := strlen(strsplit(input, " — ").1) " vs " StrLen(FinalWord)

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Make some words in Excel cell bold

Post by flyingDman » 28 Jun 2022, 21:06

Ha Ha!. The idea was that the input does not come in the form of var1 " _ " var2 but as one string that has to be parsed. I was copying and pasting from 2 old scripts and the result is a bit strange...

This was the idea:

Code: Select all

#\::
xl := ComObjActive("excel.application")
Delimiter := " _ "
input := "testing _ test glossary"
len1 := strlen(strsplit(input, Delimiter).1)        ; + strlen(Delimiter)
for c in xl.selection.cells
	{
	c.value := input
	c.Characters(1, len1).font.fontstyle := "Bold"
	}
14.3 & 1.3.7

User avatar
AlphaBravo
Posts: 586
Joined: 29 Sep 2013, 22:59

Re: Make some words in Excel cell bold

Post by AlphaBravo » 28 Jun 2022, 21:55

flyingDman wrote:
28 Jun 2022, 21:06
Ha Ha!. The idea was that the input does not come in the form of var1 " _ " var2 but as one string that has to be parsed. I was copying and pasting from 2 old scripts and the result is a bit strange...
I am glad you got a kick out of it :D

User avatar
zvit
Posts: 224
Joined: 07 Nov 2017, 06:15

Re: Make some words in Excel cell bold

Post by zvit » 29 Jun 2022, 04:36

Thanks guys, very helpful. I'm only wondering why it seems to be working slower than I'd hope. Is there an alternative to using objects that might speed up the process?

Image

User avatar
AlphaBravo
Posts: 586
Joined: 29 Sep 2013, 22:59

Re: Make some words in Excel cell bold

Post by AlphaBravo » 29 Jun 2022, 12:11

have this line SetBatchLines -1 in your "Auto-execute Section"
also

Code: Select all

xl.Application.DisplayAlerts := false
xl.Application.ScreenUpdating := false
xl.Application.Interactive := false

; do you excel automation stuff here

xl.Application.DisplayAlerts := true
xl.Application.ScreenUpdating := true
xl.Application.Interactive := true

User avatar
zvit
Posts: 224
Joined: 07 Nov 2017, 06:15

Re: Make some words in Excel cell bold

Post by zvit » 29 Jun 2022, 12:51

AlphaBravo wrote:
29 Jun 2022, 12:11
have this line SetBatchLines -1 in your "Auto-execute Section"
also

Code: Select all

xl.Application.DisplayAlerts := false
xl.Application.ScreenUpdating := false
xl.Application.Interactive := false

; do you excel automation stuff here

xl.Application.DisplayAlerts := true
xl.Application.ScreenUpdating := true
xl.Application.Interactive := true
All of these are already included:

Code: Select all

;OPTIMIZATIONS START
#NoEnv
#MaxHotkeysPerInterval 99000000
#HotkeyInterval 99000000
#KeyHistory 0
#NoTrayIcon
#SingleInstance Force ;Skips the dialog box and replaces the old instance automatically.
ListLines Off
Process, Priority, , A
SetBatchLines, -1
SetKeyDelay, -1, -1
SetMouseDelay, -1
SetDefaultMouseSpeed, 0
SetWinDelay, -1
SetControlDelay, -1
SendMode Input
;OPTIMIZATIONS END
And I also have this function to speedup Excel:

Code: Select all

XL_Speedup(PXL,Status){ ;Helps COM functions work faster/prevent screen flickering, etc.
	if(!Status){
		PXL.application.displayalerts := 0
		PXL.application.EnableEvents := 0
		PXL.application.ScreenUpdating := 0
		PXL.Application.Interactive := 0
		;PXL.application.Calculation := -4135
	}else{
		PXL.application.displayalerts := 1
		PXL.application.EnableEvents := 1
		PXL.application.ScreenUpdating := 1
		PXL.Application.Interactive := 1
		;PXL.application.Calculation := -4105
	}
}
which I usually turn on with XL_Speedup(xlApp,0) but had forgotten to with this new label. After turning it on, all was good again with the world. Thanks!

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Make some words in Excel cell bold

Post by flyingDman » 29 Jun 2022, 17:39

Also, do not forget that having excel loop through the cells in a range is slow. If you append or prepend a string to the content of a cell, it is even slower. Now, this should not be a huge issue when your range is 50 cells, but it is very noticeable when it is several hundred and it is super slow when you have 10,000 cells. Using a safearray is very significantly faster. Compare:

Code: Select all

st := A_TickCount
xl := ComObjActive("excel.application")
for c in xl.activesheet.usedrange.cells
	c.value := "aaaaaa" c.value
msgbox % A_TickCount - st         ;11172 ms on a 10,000 cell spreadsheet
;or
st := A_TickCount
xl := ComObjActive("excel.application")
sarr := xl.activesheet.usedrange.value
loop, % cnt := sarr.maxindex(1)
	sarr[a_index,1] := "bbbbb" . sarr[a_index,1]
xl.range("A1:A" cnt) := sarr
msgbox % A_TickCount - st         ;31 ms on a 10,000 cell spreadsheet
The bad news is, I do not have a similar solution when it come to changing the text attributes of an existing string in a cell. This took over 18s:

Code: Select all

st := A_TickCount
xl := ComObjActive("excel.application")
for c in xl.activesheet.usedrange.cells
	c.Characters(1, 5).Font.fontstyle := "Bold"
msgbox % A_TickCount - st 
Last edited by flyingDman on 29 Jun 2022, 21:52, edited 3 times in total.
14.3 & 1.3.7

User avatar
zvit
Posts: 224
Joined: 07 Nov 2017, 06:15

Re: Make some words in Excel cell bold

Post by zvit » 29 Jun 2022, 19:20

Thanks, I'll use the safearray on other projects. This one only has about 1800 rows and I only need to run it once a month on the entire sheet, so it's not a big deal. However, I run 20-50 rows at a time all day, so that's why I wanted it to be a bit faster and the previous solution was great for that.

Post Reply

Return to “Ask for Help (v1)”