SOLVED

Named Range behaviour in VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-2794114%22%20slang%3D%22en-US%22%3ENamed%20Range%20behaviour%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2794114%22%20slang%3D%22en-US%22%3E%3CP%3EPC%20running%20Windows%2010%20Pro%20Version%2020H2%3C%2FP%3E%3CP%3EMicrosoft%20365%20Apps%20for%20business%2C%20Excel%20Version%202108%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3EI'm%20trying%20to%20understand%20the%20behaviour%20of%20named%20ranges%20when%20used%20in%20VBA%20compared%20to%20directly%20in%20the%20sheet.%3C%2FP%3E%3CP%3EI%20have%20named%20ranges%3A%3C%2FP%3E%3CUL%3E%3CLI%3ERangeA%2C%20Sheet1!C8%3C%2FLI%3E%3CLI%3ERangeB%2C%20Sheet2!B2%3AL2%3C%2FLI%3E%3C%2FUL%3E%3CP%3EWhen%20I%20enter%20'%3DRangeB'%20at%20Sheet1!C10%2C%20the%20entire%20range%20is%20entered%20on%20the%20sheet.%20If%20I%20use%20VBA%20to%20put%20'%3DRangeB'%20in%20the%20formula%20for%20different%20cells%20I%20get%20different%20results%3A%3C%2FP%3E%3CUL%3E%3CLI%3Eat%20Sheet1!B3%2C%20the%20value%20is%2010%20and%20the%20formula%20is%20'%3D%40RangeB'%3C%2FLI%3E%3CLI%3Eat%20Sheet1!E4%2C%20the%20value%20is%2013%20and%20the%20formula%20is%20'%3D%40RangeB'%3C%2FLI%3E%3CLI%3Eat%20RangeA%2C%20the%20value%20is%2011%20and%20the%20formula%20is%26nbsp%3B%20'%3D%40RangeB'%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Named%20Range%201.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F313508i28CFAF3702AA9AE1%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Named%20Range%201.png%22%20alt%3D%22Entered%20in%20the%20sheet.%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EEntered%20in%20the%20sheet.%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Named%20Range%203.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F313510i6D1BAC9DF3F2E72F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Named%20Range%203.png%22%20alt%3D%22By%20VBA%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EBy%20VBA%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20code%20is%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Named%20Range%205.png%22%20style%3D%22width%3A%20897px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F313511i9F8151F648FE035A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Named%20Range%205.png%22%20alt%3D%22Code%20for%20editing%20formulas%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ECode%20for%20editing%20formulas%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI've%20attached%20the%20workbook.%3C%2FP%3E%3CP%3EI'd%20like%20to%20use%20the%20named%20range%20on%20one%20worksheet%20to%20transfer%20the%20data%20to%20another%20sheet.%20I%20was%20expecting%20the%20formula%20when%20created%20through%20VBA%20to%20behave%20the%20same%20way%20as%20when%20entered%20in%20the%20sheet.%3C%2FP%3E%3CP%3EI'm%20not%20sure%20why%20the%20'%40'%20is%20being%20inserted%2C%20or%20how%20to%20turn%20it%20off.%3CBR%20%2F%3EI'd%20appreciate%20any%20guidance%20on%20this.%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3ENic%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2794114%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2794255%22%20slang%3D%22en-US%22%3ERe%3A%20Named%20Range%20behaviour%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2794255%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1169621%22%20target%3D%22_blank%22%3E%40NicJ999%3C%2FA%3E%26nbsp%3Buse%20%3CSTRONG%3E.Formula2%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESee%20attached%20article.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fvba%2Fapi%2Fexcel.range.formula2%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fvba%2Fapi%2Fexcel.range.formula2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

PC running Windows 10 Pro Version 20H2

Microsoft 365 Apps for business, Excel Version 2108

 

Hi All,

I'm trying to understand the behaviour of named ranges when used in VBA compared to directly in the sheet.

I have named ranges:

  • RangeA, Sheet1!C8
  • RangeB, Sheet2!B2:L2

When I enter '=RangeB' at Sheet1!C10, the entire range is entered on the sheet. If I use VBA to put '=RangeB' in the formula for different cells I get different results:

  • at Sheet1!B3, the value is 10 and the formula is '=@RangeB'
  • at Sheet1!E4, the value is 13 and the formula is '=@RangeB'
  • at RangeA, the value is 11 and the formula is  '=@RangeB'

Entered in the sheet.Entered in the sheet.By VBABy VBA

The code is:

Code for editing formulasCode for editing formulas

I've attached the workbook.

I'd like to use the named range on one worksheet to transfer the data to another sheet. I was expecting the formula when created through VBA to behave the same way as when entered in the sheet.

I'm not sure why the '@' is being inserted, or how to turn it off.
I'd appreciate any guidance on this.

Regards,

Nic

2 Replies
best response confirmed by NicJ999 (New Contributor)
Hi Riny, thanks for your help. Perfect solution. Exactly what I needed.