Excel VBA Range object as parameter

Question

I've written this pretty short VBA macro in Excel which passes 2 Range objects to my own Function. This is the entire code:

Sub Cmp()

Dim OneMatch As Boolean
Dim NoMatches As Integer
Dim OneCell, TwoCell As Range

For Each OneCell In Range("X030Pols").Cells
   OneMatch = False: NoMatches = 0

   For Each TwoCell In Range("X206Pols").Cells

           TwoCell.Offset(0, 23).Value = 0

   Next

   For Each TwoCell In Range("X206Pols")
       If TwoCell.Offset(0, 22).Value = "" Then
           TwoCell.Offset(0, 23).Value = PolComp(OneCell, TwoCell)
           If TwoCell.Offset(0, 23).Value > 0 Then
               NoMatches = NoMatches + 1
           End If
       End If
   Next

    If NoMatches = 1 Then
       TwoCell.Offset(0, 22).Value = OneCell.Offset(0, -1).Value
       OneCell.Offset(0, 22).Value = TwoCell.Offset(0, -1).Value
    End If
Next

End Sub
Private Function PolComp(Acell As Range, Bcell As Range) As Integer

If Left(Acell.Offset(0, 1).Value, 4) = Left(Bcell.Offset(0, 1).Value, 4) Then
   PolComp = PolComp + 50
End If

If Acell.Offset(0, 6).Value = Bcell.Offset(0, 6).Value And Acell.Offset(0, 6).Value <> "" Then
   PolComp = PolComp + 50
End If

If Acell.Offset(0, 8).Value = Bcell.Offset(0, 8).Value Then
   PolComp = PolComp + 50
End If

End Function

but when I try to run it I get this error:

So OneCell is defined as a Range, my function uses a Range, but there's a mismatch. The error occurs as it tries to parse my code before it runs anything.

I could work around it but I'm more concerned about understanding what I've done wrong.

Not exactly sure what you're trying to do. The problem most likely stems from the fact that you're trying to use an unset range object in a for loop. You Dim the object, but never set it to anything so one of your errors is probably being thrown from an "nothing" object. What are you looking for in Range("x030Pols")? Not to mention I don't think you can iterate through a range with another range object. In my head, it would only execute once since that range would only appear once (if any) in the specified range. — StormsEdge
– StormsEdge, Commented Mar 22, 2016 at 17:47
Also, you're only passing one argument to your function, which is Acell as a range object. — StormsEdge
– StormsEdge, Commented Mar 22, 2016 at 17:49
Your function requires two arguments but you are only passing one. — Excel Developers
– Excel Developers, Commented Mar 22, 2016 at 18:05

JNevill · Accepted Answer · 2016-03-22 18:08:17Z

5

The problem is in your variable declaration:

Dim OneCell, TwoCell As Range

This is the same as writing:

Dim oneCell as Variant
Dim TwoCell as Range

Instead:

Dim oneCell as Range
Dim TwoCell as Range

Or:

Dim OneCell as Range, TwoCell As Range

So that VBA doesn't have to guess at your type.

answered Mar 22, 2016 at 18:08

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

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

1 Comment

Lefty Over a year ago

Thank you very much! I seriously had no idea that Dim worked this way. I've used this syntax on ALL of my VBA projects to date and obviously getting variants all over the place instead of what I actually thought I was getting. I've changed it, tested it and it works, thank you!

Collectives™ on Stack Overflow

Excel VBA Range object as parameter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related