Using defined names in VBA...

[Guest]

Hi All

When writing excel formulas I use defined ranges that are named (eg
"Schedule") instead of stipulating the range (eg "$AC$22:$DO$100").

How do I use defined names instead of actual ranges when writing code?

I need this because the range on the excel spreadsheet keeps changing when
users insert/delete rows/columns/cells and the range needs to move with the
changes.

Here is an example of code where the ranges would need to be replaced with
the defined name...

Sub Example()
Sheet("Sheet2").Select
Range("AC22O100").Select 'This is called "Schedule"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.NumberFormat = "#,##0.00"
With Selection.Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Sheet("Sheet1").Select
Range("A13Y100").Select
Selection.Replace What:="0", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

 

[Jan Karel Pieterse]

Hi BeSmart,

Change your code to this:

Sub Example()
With Thisworkbook.Names("Schedule").ReferstoRange
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
.NumberFormat = "#,##0.00"
With .Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Guest]

Thanks for that Jan
I tested the replace functions on the second defined range in the original
code and it worked beautifully.
That helps heaps and teaches me something.
Cheers
BeSmart

Hi BeSmart,

Change your code to this:

Sub Example()
With Thisworkbook.Names("Schedule").ReferstoRange
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
.NumberFormat = "#,##0.00"
With .Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Guest]

A late question....

In the name of protecting code, I have to save the code in a different
workbook ("Masterfile") to the one it actually has to apply too ("Template").

The master workbook auto opens when the template is opened.
The user clicks on a button in the "template" to active and apply the macro.

When testing the code in the Masterfile, "ThisWorkbook" doesn't work because
the formatting is not suppose to apply to the Masterfile - it has to happen
to the current workbook selected. How do I change the code to accommodate
this?

Note: the name of the template will alway change, so I need to apply the
macro to the "workbook currently selected" not a specific workbook name.

TFYH
BeSmart

Here is the code again:
Sub Example()
With Thisworkbook.Names("Schedule").ReferstoRange
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
.NumberFormat = "#,##0.00"
With .Font
.Name = "Tahoma"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End With
End Sub

 

[Jan Karel Pieterse]

Hi BeSmart,

Note: the name of the template will alway change, so I need to apply the
macro to the "workbook currently selected" not a specific workbook name.

Use

Activeworkbook

instead of Thisworkbook

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com