How to write function to select worksheets from workbook and change font

[ThrottleWorks]

Hi,

First my apologies for asking this question. I am trying to learn VBA function.
I want to apply formatting changes to n number of worksheets in a workbook.

I have written a simple code to do it. However I am trying to do it by function.
However, I am not able to understand how to do it.

Can anyone please help me in this, will use help function as reference for future learning. Thanks.

I am using below mentioned normal code as of now. How to achieve this by using function.

'+Assign font, wrap text

    ESRP_Sht.Select
    ESRP_Sht.Cells.Font.Size = 10
    ESRP_Sht.Cells.WrapText = False
    Range("A1").Select

    Laser_Before_Sht.Select
    Laser_Before_Sht.Cells.Font.Size = 10
    Laser_Before_Sht.Cells.WrapText = False
    Range("A1").Select

    P_I_Check_Sht.Select
    P_I_Check_Sht.Cells.Font.Size = 10
    P_I_Check_Sht.Cells.WrapText = False
    Range("A1").Select

    Note_Rate_Check_Sht.Select
    Note_Rate_Check_Sht.Cells.Font.Size = 10
    Note_Rate_Check_Sht.Cells.WrapText = False
    Range("A1").Select

    Workbook_Sht.Select
    Workbook_Sht.Cells.Font.Size = 10
    Workbook_Sht.Cells.WrapText = False
    Range("A1").Select

    Sold_Sht.Select
    Sold_Sht.Cells.Font.Size = 10
    Sold_Sht.Cells.WrapText = False
    Range("A1").Select
    '=Assign font, wrap text

 

[Marc L]

Hi !

As a function returns something, the question is
what your function must return ?
​

 

[NARAYANK991]

Hi ,

If you are interested to read , here are a few links :

http://analystcave.com/vba-function-vs-vba-sub-procedures/

https://stackoverflow.com/questions...ifference-between-a-function-and-a-subroutine

https://stackoverflow.com/questions/11539838/purpose-of-using-sub-routines-over-functions

Narayan

 

[Chihiro]

From what I see, what you need isn't really function since you don't need any value returned, but another sub?

 

[shrivallabha]

As has been explained by others function will return something and you are not expecting a result to be returned.

I think you have got the correct idea from programming viewpoint. It can be implemented like below.

In below example, ApplyShtFmtGen is where you write a general function like code to do repetitive and common tasks for multiple objects. Function Demo
gives call.

Public Sub Demo()
Dim ESRP_Sht As Worksheet
Dim Laser_Before_sht As Worksheet

Set ESRP_Sht = ThisWorkbook.Sheets(1)
Call ApplyShtFmtGen(ESRP_Sht, 10, False) '\\ Pass arguments for ESRP_Sht

Set Laser_Before_sht = ThisWorkbook.Sheets(2)
Call ApplyShtFmtGen(Laser_Before_sht, 11, False) '\\ Pass arguments for Laser_Before_sht
End Sub


Public Sub ApplyShtFmtGen(wks As Worksheet, lngFontSize As Long, blWrapText As Boolean)
With wks.Cells
    .Font.Size = lngFontSize
    .WrapText = blWrapText
End With
End Sub

 

[ThrottleWorks]

Hi @shrivallabha , @NARAYANK991 sir, @Chihiro , and @Marc L , extreamally sorry for late reply. Was away due to medical reasons. Please give me some time to revert.

Thanks a lot for the help.