0

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?

Erik A
  • 31,639
  • 12
  • 42
  • 67
Doug Coats
  • 6,255
  • 9
  • 27
  • 49

1 Answers1

3

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
ThunderFrame
  • 9,352
  • 2
  • 29
  • 60
  • apparently set rNg = ws.Range isnt a supported method or property either. I will keep trying things until i figure it out. – Doug Coats Feb 11 '17 at 21:43
  • 1
    see my edit, you need to set `ws` to a *single* sheet, with `Set ws = wb.Sheets(1)` instead of `Set ws = wb.Sheets` – ThunderFrame Feb 11 '17 at 21:46
  • Ah. I had to do that and take out the rNg reference and it worked like a chamr. thanks – Doug Coats Feb 11 '17 at 21:47
  • why are instruction separators evil? I am genuinely curious about this – Doug Coats Feb 11 '17 at 21:48
  • @DougCoats - I this case they just add 4 unnecessary characters. In most other cases they make code less readable. Can you tell at a glance what this line outputs? `If True Then: Debug.Print "foo": Debug.Print "bar"` – Comintern Feb 11 '17 at 21:53
  • When reading code, I expect 1 statement per logical line, but instruction separators allow multiple statements per line. If the instruction separator is off to the right of the screen, or on a continued line, or adjacent to certain other characters, it can be hard to spot, and makes code hard to understand and debug. Furthermore, the VBE doesn't always preserve them, which can lead to badly compiled code like this: http://stackoverflow.com/questions/37147979/why-does-this-code-compile-when-pasted-in-but-fail-otherwise – ThunderFrame Feb 11 '17 at 21:54
  • @ThunderFrame Ah Ok that makes sense. I shall reframe from doing with variable declarations. Is it a sin to do something like this? Dim c as string: c = "this" ? As long as thats the only thing on the line? – Doug Coats Feb 11 '17 at 21:56
  • 2
    @DougCoats Best practice is to just not use them at all. If you see a line starting with `Dim`, you know you can just skip reading it, but if you use instruction separators, you have to read the entire line, just to make sure you're not missing something important, like `Dim c As String: c = Now() : If Instr(1,c,"1") > 0 Then End Else : Do While True : Loop 'Your Code just terminated or hung the VBE` – ThunderFrame Feb 11 '17 at 22:02
  • @ThunderFrame fine geez. I thought with simple stuff using the same line for simple variable assignments was more readable. My opinion clearly differs from most. oh well. – Doug Coats Feb 11 '17 at 22:07