• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How would I put this vba excel together?

A

Ama

Member
Hi all, I have put together a set of sub routines to format a document but how do I put it altogether into one sub routine?

Sub InsertHeader()

With Selection.Font
.Name = "Arial"
.Size = 11
.ColorIndex = 1
End With


End Sub
Sub RigidFormattingProcedure()
ActiveSheet.Range("A:A,B:B,C:C,D:D,M:M,N:N,O:O").EntireColumn.AutoFit
End Sub

Sub Bold()
Dim rngRow As Range
For Each rngRow In Range("A1:O1").Rows
If rngRow.Cells(1).Value > 1 Then
rngRow.Font.Bold = True
Else
rngRow.Font.Bold = False
End If
Next rngRow
End Sub
 
Hi ,

Depending on the order in which you wish to execute them , just use the call statement followed by the procedure name , as in :

Code: 
Public Sub Overall_Formatting()
          Call InsertHeader
          Call RigidFormattingProcedure
          Call Bold
End Sub

You can omit the keyword Call , but this is not good practice :

Code: 
Public Sub Overall_Formatting()
          InsertHeader
          RigidFormattingProcedure
          Bold
End Sub
Apart from your actual question , you need to look at what your code is actually doing , as in this statement :

For Each rngRow In Range("A1:O1").Rows

The reference A1:O1 is exactly one row ; using the Rows construct is redundant. You can use the following with the same result :

Code: 
Sub Bold()
    Dim cell As Range
    For Each cell In Range("A1:O1")
        If cell.Value > 1 Then
          cell.Font.Bold = True
        Else
          cell.Font.Bold = False
        End If
    Next cell
End Sub
Narayan
 
I did exactly what you have given but it is not working. I must be doing something wrong. my previous code was working but I just wanted to know how to put it together into one sub routine if possible.
 
Hi, It works in the Book.xlsm file you sent but not in mine. It comes up as Sub or Function not defined.
 
Hi ,

So where have you put your calling procedure then , in relation to the other subroutines ? Is it in the same module , or the same sheet section or in the workbook section ?

Narayan
 
Hi ,

This is surprising ; where are the individual subroutines themselves ? Are you not supposed to have them in this workbook ?

See your file now.

Narayan
 

Attachments

  • ExcelMacroPrac.xlsm
    61.6 KB · Views: 1
Ok, so I don't need my other sub routines, except for the

Public Sub Overall_Formatting()
Call InsertHeader
Call RigidFormattingProcedure
Call Bold
End Sub

If I wanted to makes columns wider and make the font = Arial, can I add that to the Public Sub Overall_Formatting()? I find that deleting the other subs the Public Sub Overall_Formatting() doesn't want to work.
 
Hi ,

I am confused by what exactly you want me to explain ; from your original post I understood that your requirement was that you had 3 individual subroutines , which you wanted to combine.

The procedure named Overall_Formatting does exactly that , since it calls each of the individual subroutines ; thus for the procedure named Overall_Formatting , the individual subroutines need to exist , since all that the procedure named Overall_Formatting is doing is passing control to each subroutine one after the other. If the individual subroutines do not exist , the procedure named Overall_Formatting will not work.

Alternatively , what you can do is put all of the individual subroutines' statements within the procedure named Overall_Formatting ; then you can dispense with the individual subroutines.

Narayan
 
ah, now I understand what you mean, I am very new at vba, I still haven't got all the concepts. Thanks Narayan, so I put my three sub routines into the module with the Public overall formatting() at the beginning. I will let you know what happens. Thanks
 
Ok, it works but it doesn't change the font style to Arial

Public Sub Overall_Formatting()
Dim rngRow As Range

With Selection.Font
.Name = "Arial"
.Size = 11
.ColorIndex = 1
End With

ActiveSheet.Range("A:A,B:B,C:C,D:D,M:M,N:N,O:O").EntireColumn.AutoFit

For Each rngRow In Range("A1:O1").Rows
If rngRow.Cells(1).Value > 1 Then
rngRow.Font.Bold = True
Else
rngRow.Font.Bold = False
End If

Next rngRow

End Sub

Or Ive done this:

Call InsertHeader

Call RigidFormattingProcedure

Call Bold


Sub InsertHeader()

With Selection.Font

.Name = "Arial"

.Size = 11

.ColorIndex = 1

End With



End Sub


Sub RigidFormattingProcedure()

ActiveSheet.Range("A:A,B:B,C:C,D:D,M:M,N:N,O:O").EntireColumn.AutoFit

End Sub


Sub Bold()

Dim rngRow As Range

For Each rngRow In Range("A1:O1").Rows

If rngRow.Cells(1).Value > 1 Then

rngRow.Font.Bold = True

Else

rngRow.Font.Bold = False

End If

Next rngRow

End Sub
 
Last edited:
Hi ,

The problem with the InsertHeader subroutine is that it is referring to the Selection object ; what this means is that before you run this procedure , you need to select the range where you want this formatting to be applied ; suppose you want the formatting to be applied to the column headers , which are in the range A1:O1 , select this range and then run the procedure.

Otherwise , define the range , where you want the formatting to be done , within the code , and then run the procedure.

Remember , the code has to be in the sheet section where you want all this to happen.

See the attached file.

Narayan
 

Attachments

  • Book4.xlsm
    13.7 KB · Views: 3
You must log in or register to reply here.
Back
Top