Select and apply formula in named range using VBA Excel

select named range VBA Excel

Objective

To showcase how to refer and use named ranges by VBA Excel.

Approach

In the below shown VBA Excel automation, we have considered 3 scenarios –

  • Refer to named range which has entire workbook as scope
  • Refer to name named range which has a specific worksheet as scope
  • Refer to named range which has scope in one sheet ( e.g. sheet2) and that named range is referred in another sheet ( e.g. sheet1)

Case 1

  • Refer to named range which has entire workbook as scope

In the first case we have data in 2 columns, we have considered the cell B2 to B5 as a named range. Here we have created the named range “wbookRange”, it has range as B2 to B5. We have set the scope of this named range as “workbook”, which means we can access or use this named range anywhere in the workbook. We can see the same in the image below. To see how to create a named range, you can check the link provided in the reference section.

 

select named range VBA Excel

 

Code

In the below shown code we have aimed to apply sum formula in cell B6 and then format the text inside the cell range B2 to B5.Here first we have applied the formula in cell B6, then we have made the font of cell as bold ,changed its colour and made the font style as italic.

Sub AcceessWorkbookRangeByVBA()

Dim SumOfRange As Integer

'Adding the values in the named range
Range("B6").Formula = "=sum( " & "wbookRange" & ")"

'Change the colour and make font as bold and italic in range

Range(ThisWorkbook.Name & "!" & "wbookRange").Select

With Selection
.Font.Bold = True
.Font.ColorIndex = 10
.Font.Italic = True
End With

End Sub

 

Below shown is the image before and after executing the VBA code.On right side on the image, we can see that sum formula is applied in cell B6 and, cells are also formatted.

select named range VBA Excel

 

Case 2

  • Refer to named range which has a specific worksheet as scope

In this case first we have created a named range “wsheetRange” which has scope as “sheet2”, which means that this named range can only refer to cells in “sheet2”. This named range refers to range B2 to B5 in sheet2. We can see the same in the image below.

 

select named range VBA Excel

 

Code

In the below shown code we aim to do the same things as we did in the code above. Here we want to apply sum formula in cell B6 and additionally we want to format the text which falls in named range cells. We have applied this code in sheet2.

Sub AcceessWorksheetSpecificRangeByVBA()

Sheets("sheet2").Select

'Adding the values in the named range
Range("B6").Formula = "=sum( " & "wsheetRange" & ")"

'Change the colour and make font as bold and italic in range
Range("Sheet2" & "!" & "wsheetRange").Select

With Selection
.Font.Bold = True
.Font.ColorIndex = 10
.Font.Italic = True
End With

End Sub

 

select named range VBA Excel

 

Case 3

  • Refer to named range which has scope in one sheet ( e.g. sheet2) and that named range is referred in another sheet ( e.g. sheet1)

There is also another possibility in which we can use worksheet specific named range. In the code below we have used named range “wsheetRange” in sheet1.It is important to know that named range “wsheetRange” was set for “sheet2”, but here we have used it in “sheet1”.

In the code below we have shown the sum of cells in named range “wsheetRange”. This named range in specific to “sheet2”. To use this named range in sheet1 we must use sheet2 along with “!” sign. We can see the result in image shown below.

Code

Sub AcceessWorksheetSpecificRangeInAnotherSheet()

Sheets("sheet1").Select

'Adding the values in the named range
Range("B6").Formula = "=sum( " & "sheet2!" & "wsheetRange" & ")"

End Sub

 

select named range VBA Excel

 

Reference

https://docs.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/refer-to-named-ranges
https://support.office.com/en-ie/article/define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64


Post you may like

Find date difference using datediff function in VBA Excel | debugvba.com

 

 

 

1 comment on “Select and apply formula in named range using VBA Excel

Comments are closed.