someExcel VBA: Cannot create a range object successfully

Question

This is my first question here, so bear with me. I'm a security consultant working on a huge firewall migration, for which I got my VBA skill from under a thick layer of dust. So far I have managed to get all my issues resolved by searching, but this issue: I get errors when doing exactly how I find it everywhere.

What I want to do: I have an array that contains (among other things), strings formatted like this: "A3:P59", representing a cell range. Now, this are ranges within a table. When I get the address of a certain cell in the table, I want to test if it's in that range.

I wrote a test function:

   Function TestCellRange() As Boolean  
    Dim tbl As ListObject  
    Dim cell, rng, test As range  
    Dim range As range

    Dim bRow, eRow As Integer

    Set tbl = shRulebase.ListObjects("tblBFFirewallRules")
    shRulebase.Activate
    With shRulebase

    cell = tbl.DataBodyRange(5, 1).Address(False, False) 'it's this command that gives me issues
    Set range = .range(.Cells(bRow, 1), .Cells(eRow, 16)) 

    Debug.Print cell
    'Set rng = shRulebase.range(range)
    Debug.Print rng

    Set test = Application.Intersect(cell, range(range(A3), range(P59)))
    If test Is Nothing Then
        MsgBox ("oops")
        TestCellRange = False

    Else
        MsgBox ("yup yup")
        TestCellRange = True
    End If
    End With

End Function

Now whatever I try, I keep getting blocked on the set range:

set range = .Range("A3:P59") -> will return "object required", on the "set test" line (if i use intersect (cell, range))
Set range = range("A3:P59") -> will return object variable or with block variale not set on the same line
Set range = .range(.Cells(bRow, 1), .Cells(eRow, 16)) -> will step through, but debug.print returns a type mismatch and "Set test = Application.Intersect(cell, range)" returns a "object required"

Any help would be really appreciated...I'm all to familiar with networks ip's and the bits and bytes of it, but here I am a bit out of my comfort zone and I need to finish this by tomorrow :(

Greetings, Kraganov

EDIT Some More tries:

rng and cell as variant:
cell = tbl.DataBodyRange(5, 1).Address(False, False)
rng = .range("A3:P59").Address(False, False)
Set test = Application.Intersect(cell, rng)
==>I would get objects required
just using rng as range and trying to set it without "set"
rng = .range("A3:P59")

EDIT 2 : I found a way around using the range.

So what I was trying to do, was the following: I had a table that contains information about firewall rules. However, not every line describes a rule. There are also lines that described the context in which the rules below that line were to be placed. Outside of the table, aside of those lines there would be a cell with the range of cells for that context. I wanted to use that to describe the context for those rules, if I pulled them. I ended up looping through the table rows and identifying those specific rows and setting a "context" variable when, a row like that was met.

First, naming variable same as type is not a good idea, IMHO. Second, in Dim cell, rng, test As range, only test is of type Range, cell and rng are of type Variant. In VBA you need to repeat As type for each variable. — Vincent G
true, naming is terrible.....it was just for a proof of concept function. :-) The actual code would be more aptly named. Now, if I indeed decalred explicitly cell as range, it would fail. if I declared both rng and cell as variant and used the following: cell = tbl.DataBodyRange(5, 1).Address(False, False) rng = .range("A3:P59").Address(False, False) Set test = Application.Intersect(cell, rng) I would get objects required — Kraganov

Vityata Vityata · Accepted Answer · 2016-04-06T08:04:33

Try setting the cell as well as following:

set cell = tbl.DataBodyRange(5, 1).Address(False, False)

What is cell? A Range?

someExcel VBA: Cannot create a range object successfully

4 Answers