Store and Excel named range as variable in VBA then pass to function

Question

In VBA I am trying to store a named range into a variable then pass it to a function but I get an error "Object doesn't support this property or method"

Function BOM(val, rng)
Dim CR_Path As String
Application.ScreenUpdating = False
CR_Path = ThisWorkbook.Sheets("Request").Cells(val, 1).Value 'Copy that path from a cell
Set mybook = Workbooks.Open(CR_Path)


mybook.Sheets("BOM").Range("A4:G28").Value = ThisWorkbook.Sheets("BOM").Range("rng").Value
mybook.Close SaveChanges:=True
Application.ScreenUpdating = True
End Function

Sub test()
Dim rng As Variant
Dim val as Integer
rng = ThisWorkbook.Sheets("BOM").Range("myTable").RefersToRange.Value
val = 2
Call BOM(3, rng)
End Sub

chris neilsen · Accepted Answer · 2020-08-13 22:30:55Z

1

RefersToRange is a property of a Name object, not a Range.

Change

ThisWorkbook.Sheets("BOM").Range("myTable").RefersToRange.Value

To

ThisWorkbook.Sheets("BOM").Range("myTable").Value

Or

ThisWorkbook.Names("myTable").RefersToRange.Value

Other than refering to a Named range, there are more issues in your code too

Your code, refactored

'Your are not returning anything, so use a Sub
'Define your parameter types
Sub BOM(PathRow As Long, rng As Range)
    Dim CR_Path As String
    Application.ScreenUpdating = False
    CR_Path = ThisWorkbook.Sheets("Request").Cells(PathRow, 1).Value 'Copy that path from a cell
    Set mybook = Workbooks.Open(CR_Path)
    
' Use the range variable Values property
    mybook.Sheets("BOM").Range("A4:G28").Value = rng.Value
    mybook.Close SaveChanges:=True
    Application.ScreenUpdating = True
End Sub

Sub test()
    Dim rng As Range
    Dim PathRow As Long ' use meaningful names for variables
    Set rng = ThisWorkbook.Names("myTable").RefersToRange
    PathRow = 2

    ' use the variable you defined
    BOM PathRow, rng
End Sub

edited Aug 13, 2020 at 22:30

answered Aug 13, 2020 at 21:11

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Perry_M Over a year ago

Tried that and got: Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range. The issue was inside the function. ```mybook.Sheets("BOM").Range("A4:G28").Value = ThisWorkbook.Sheets("BOM").Range("rng").Value

chris neilsen Over a year ago

That's not what I suggested.

Perry_M Over a year ago

This works great. Is it also possible to copy everything in that range, not just the values?

Collectives™ on Stack Overflow

Store and Excel named range as variable in VBA then pass to function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related