How to add font and font size module

[shrivallabha]

No doubt there is probably a better code structure that could be made than what I cobbled together, but at this point in time, I'm going for "does it work correctly?" I have not found any errors, yet. It does take about 70 seconds to run, but that's ok. It saves me incalculable time of manual labor. Without going into detail, do you see anything that I could change in the code to make it run faster? Yes or no, would be fine at this point in time. I can ponder over making it faster later.

I performed a "Record Macro" to figure out what lines I needed to know what to put in the code to make the data do what I needed and then put those recorded segments in the existing VBA code and crossed my fingers.

My question, hopefully more to the point, is "why" did it work without putting something like ".font" in front of each segment line? For example, something like .Section? As you can tell, I know just enough about VBA (and Excel) (painfully self-taught with a lot of help from Chandoo folks like yourself) to be dangerous.

Are you reading any book related to VBA? If not then please start reading one as it will give you a lot more structured approach and understanding of VBA.

Coming back to your question, Excel is an application (akin to whole body).

Application comprises of several components called objects (body parts) which either perform an action(method/procedure/function) or store information(property). Understanding and manipulating an application through programming needs knowledge of this model, Component Object Model (COM). An object may be parent of another object or vice versa.

In your case you want to control Cell behavior. Cell or Range is an object. Its place in COM hierarchy would be as below:
Application >> Workbook >> Worksheet >> Range
i.e. you can completely qualify the object like below

- Create one workbook with three sheets
- Select third sheet and then step through below code using F8

Public Sub ExampleRangeFont()
Dim xlApp As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rngOne As Excel.Range, rngTwo As Excel.Range, rngThree As Excel.Range

'\\ Bind Application to running instance of application
Set xlApp = GetObject(, "Excel.Application")
'\\ Set reference to workbook
Set wbk = xlApp.ActiveWorkbook
'\\ Set reference to worksheet
Set wks = wbk.Sheets(1)
'\\ Set reference to range
Set rngOne = wks.Range("A1")

'\\ Equivalent code of above will be as below
Set rngTwo = xlApp.ActiveWorkbook.Sheets(2).Range("A1")

'\\ If we do not define preceding objects then Code assumes action relates to current application, activeworkbook and activesheet
Set rngThree = Range("A1")

'\\ Now if we want to manipulate Range object's font behavior then

'Style one
With rngOne.Font
    'here you can manipulate font behavior only inside With
    .Bold = True
End With

'Style two
With rngTwo
    'Here you can use all properties belonging to Range object
    .Font.Bold = True
    'Interior is another property
    .Interior.Color = vbYellow
End With

'It is an independent statement as there's no loop
rngThree.Font.Bold = True
End Sub

So at any given point of time you need to use "valid" preceding object or property or else VBA will raise an error i.e. font expects Range to be its preceding object. If we write like below where wks is sheet object then code will go in error.

Dim wks as Worksheet
With wks
     .Font.Bold = True
End With

I'd just hope that it makes things clear to you. Let me know if it confuses you more

 

[Eloise T]

Would this cause an error or just be superfluous?

With rngFnt.Font

      rngFnt.Font.Size = 11

      rngFnt.Font.Name = "Calibri"

End With

 

[shrivallabha]

Did you test it or just asking?

 

[SirJB7]

Hi, Eloise T!

Are you reading any book related to VBA? If not then please start reading one as it will give you a lot more structured approach and understanding of VBA.

Where shrivallabha wrote one, please read two or three. I.e., read more & write less.
Regards!

 

[Eloise T]

Did you test it or just asking?

I attempted to test but kept getting error 424. Compile VBAProject worked, but got:


...when Stepping Into.
I tried:

Sub TEST()

Dim  rngFnt

With rngFnt.Font

      rngFnt.Font.Size = 11

      rngFnt.Font.Name = "Calibri"

End With
End Sub

 

[Hui]

Your code has defined rngfont as a variant not as a Range
and then it hasn't been set to anything which is where the error is coming from

so this will work

Sub TEST()

Dim rngFnt As Range
Set rngFnt = Range("B2:B10")

With rngFnt.Font
  rngFnt.Font.Size = 11
  rngFnt.Font.Name = "Calibri"
End With
End Sub

but these next two would be preferred

Sub TEST()

Dim rngFnt As Range
Set rngFnt = Range("B2:B10")

With rngFnt.Font
  .Size = 11
  .Name = "Calibri"
End With
End Sub

or

Sub TEST()

Dim rngFnt As Range
Set rngFnt = Range("B2:B10")

rngFnt.Font.Size = 11
rngFnt.Font.Name = "Calibri"

End Sub

 

[Eloise T]

Your code has defined rngfont as a variant not as a Range
and then it hasn't been set to anything which is where the error is coming from

so this will work

Sub TEST()

Dim rngFnt As Range
Set rngFnt = Range("B2:B10")

With rngFnt.Font
  rngFnt.Font.Size = 11
  rngFnt.Font.Name = "Calibri"
End With
End Sub

but these next two would be preferred

Sub TEST()

Dim rngFnt As Range
Set rngFnt = Range("B2:B10")

With rngFnt.Font
  .Size = 11
  .Name = "Calibri"
End With
End Sub

or

Sub TEST()

Dim rngFnt As Range
Set rngFnt = Range("B2:B10")

rngFnt.Font.Size = 11
rngFnt.Font.Name = "Calibri"

End Sub

THANK YOU! I'm seeing a light at the end of the tunnel. ...hopefully it's not a train!

 

[SirJB7]

Hi, Eloise T!
Beware if it's the roadrunner disguised as train driver and you're still playing the coyote role.
Regards!

 

[Eloise T]

Are you reading any book related to VBA? If not then please start reading one as it will give you a lot more structured approach and understanding of VBA.

Coming back to your question, Excel is an application (akin to whole body).

Application comprises of several components called objects (body parts) which either perform an action(method/procedure/function) or store information(property). Understanding and manipulating an application through programming needs knowledge of this model, Component Object Model (COM). An object may be parent of another object or vice versa.

In your case you want to control Cell behavior. Cell or Range is an object. Its place in COM hierarchy would be as below:
Application >> Workbook >> Worksheet >> Range
i.e. you can completely qualify the object like below

- Create one workbook with three sheets
- Select third sheet and then step through below code using F8

Public Sub ExampleRangeFont()
Dim xlApp As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rngOne As Excel.Range, rngTwo As Excel.Range, rngThree As Excel.Range

'\\ Bind Application to running instance of application
Set xlApp = GetObject(, "Excel.Application")
'\\ Set reference to workbook
Set wbk = xlApp.ActiveWorkbook
'\\ Set reference to worksheet
Set wks = wbk.Sheets(1)
'\\ Set reference to range
Set rngOne = wks.Range("A1")

'\\ Equivalent code of above will be as below
Set rngTwo = xlApp.ActiveWorkbook.Sheets(2).Range("A1")

'\\ If we do not define preceding objects then Code assumes action relates to current application, activeworkbook and activesheet
Set rngThree = Range("A1")

'\\ Now if we want to manipulate Range object's font behavior then

'Style one
With rngOne.Font
    'here you can manipulate font behavior only inside With
    .Bold = True
End With

'Style two
With rngTwo
    'Here you can use all properties belonging to Range object
    .Font.Bold = True
    'Interior is another property
    .Interior.Color = vbYellow
End With

'It is an independent statement as there's no loop
rngThree.Font.Bold = True
End Sub

So at any given point of time you need to use "valid" preceding object or property or else VBA will raise an error i.e. font expects Range to be its preceding object. If we write like below where wks is sheet object then code will go in error.

Dim wks as Worksheet
With wks
     .Font.Bold = True
End With

I'd just hope that it makes things clear to you. Let me know if it confuses you more

Public Sub ExampleRangeFont()
Dim xlApp As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rngOne As Excel.Range, rngTwo As Excel.Range, rngThree As Excel.Range

'\\ Bind Application to running instance of application
Set xlApp = GetObject(, "Excel.Application")
'\\ Set reference to workbook
Set wbk = xlApp.ActiveWorkbook
'\\ Set reference to worksheet
Set wks = wbk.Sheets(1)
'\\ Set reference to range
Set rngOne = wks.Range("A1")

'\\ Equivalent code of above will be as below
Set rngTwo = xlApp.ActiveWorkbook.Sheets(2).Range("A1")

'\\ If we do not define preceding objects then Code assumes action relates to current application, activeworkbook and activesheet
Set rngThree = Range("A1")

'\\ Now if we want to manipulate Range object's font behavior then

'Style one
With rngOne.Font
    'here you can manipulate font behavior only inside With
    .Bold = True
End With

'Style two
With rngTwo
    'Here you can use all properties belonging to Range object
    .Font.Bold = True
    'Interior is another property
    .Interior.Color = vbYellow
End With

'It is an independent statement as there's no loop
rngThree.Font.Bold = True
End Sub

I created a workbook with the above VBA code. I went to worksheet 3 and selected ALT + F8. Next I selected Step Into, in which case it jumped to the ALT+F11 screen/page. I stepped through using F8 and the yellow arrow moved downward through the code until it finished. Should I have expected something else to happen? AAAAAAAAAAH! But I did notice it yellow-highlighted cell A1 in worksheet 2. ...and finally sifting through the code, I noted that putting text in A1 on worksheet 2, it made the text Bold as well....and noted cell A1, worksheet 1, is Bold as well!

BONUS: Putting the ".Interior.Color = vbYellow" in the VBA test file, I determined that once it ran out of data, it moved on. In other words, it didn't color/yellow-highlight all the way to the 1.048576 millionth row.

 

[shrivallabha]

Code by itself doesn't look around. It just follows what's been instructed. In this case, we are asking it to work with cell A1.

Make sure you read all comments provided in the code.

 

[Eloise T]

Code by itself doesn't look around. It just follows what's been instructed. In this case, we are asking it to work with cell A1.

Make sure you read all comments provided in the code.

OK. Thank you.