CODEWITHSUNDEEP
excelvba
Perry_M
Perry_M

Reputation: 27

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

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

Upvotes: 0

Views: 883

Answers (1)

chris neilsen
chris neilsen

Reputation: 53166

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

Upvotes: 1

Related Questions