CODEWITHSUNDEEP
ms-accessvbalate-binding
Doug Coats
Doug Coats

Reputation: 7117

Dynamically Create Range Late Binding VBA

trying to dynamically create ranges using this:

 ws.Range(Cells(1, 1), Cells(q + 1, z + 1)).Value2 = xaRR

this works in excel like a charm, but access vba keeps giving me issue. It doesnt like the cells part. The annoying thing is this doesnt give me any fuss

 ws.Cells(1,1).Value = blah blah blah

When I try this:

        Dim oXL As Object: Dim wb As Object: Dim ws As Object: Dim rNg As Object: Dim cl As Object
        Set wb = .Workbooks.Add
        Set ws = wb.Sheets
        Set rNg = ws.Range
        Set cl = rNg.Cells
        ws.rNg(cl(1, 1), cl(q + 1, z + 1)).Value2 = xaRR

it says that rNg doesnt support this property or method.

Can someone help me see the obvious issue?

Upvotes: 0

Views: 1164

Answers (1)

ThunderFrame
ThunderFrame

Reputation: 9461

Your original line is using an implicit reference to the Activesheet. In Excel, that works (but is a bug if you don't mean to use the Activesheet), but in Access it will be an error. Qualify the Cells call with the sheet reference, for an explicit call:

I assume ws is a Worksheet object and not a Worksheets object

ws.Range(ws.Cells(1, 1), ws.Cells(q + 1, z + 1)).Value2 = xaRR

Or in your second example:

I've removed the Cells variable, and I've also substituted the : Dim instruction separators for commas. Instruction separators are evil.

Your ws variable is all sheets, so I've just grabbed the first one, but your implementation may need to vary.

Dim oXL As Object, wb As Object, ws As Object
Set wb = .Workbooks.Add
Set ws = wb.Sheets(1)
ws.Range(ws.Cells(1, 1), ws.Cells(q + 1, z + 1)).Value2 = xaRR

Upvotes: 3

Related Questions