1

In my excel sheet, I have a cell called su_callLog.

My VBA code is:

Private Sub su_callLogCopy_Click()
    CopyCell (Range("su_callLog")) 'Error Here
End Sub
Sub CopyCell(cell As Range)
    MsgBox cell.Value
End Sub

I'm trying to use the function to process the info in that cell. Even when i try to merely print the contents of the Range, I get the VBA Run-time error '424' Object Required error. I'm new to VBA. Please tell me what I'm doing wrong.

NOTE: MsgBox (Range("su_callLog")) produces the expected results.

Somenath Sinha
  • 1,174
  • 3
  • 16
  • 35

2 Answers2

3

Remove the parentheses:

CopyCell Range("su_callLog")

If you use parentheses, you are forcing that the arguments are taken ByVal. And the Range() is an object type, which is passed ByRef.

ByVal vs ByRef VBA

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 1
    That worked!! I'm new to VBA and this is extremely counter intuitive. Could you please tell me why the parenthesis caused the error?! – Somenath Sinha Aug 07 '18 at 11:15
  • @SomenathSinha - Welcome. In general it depends from which programming world are you coming from. If you have coded in C or C++, this is a normal, expected error. If VBA is your first programming language, then this is a bit unexpected and counter intuitive indeed. See the link and the explantion I have added. :) – Vityata Aug 07 '18 at 11:17
1

As @Vityata correctly pointed oute, you're forcing ByVal to be taken, while you need to be passing a reference to a range.

While that much is true and CopyCell Range("su_callLog") will produce the desired result, there is one more way to achieve this:

in which you implicity reference the Range correctly while in parentheses
so in other words, you (knowingly or unknowingly) pass the reference properly. 

 CopyCell (Sheets("Sheet1").Range("A1"))

Not only you maintain your desired (although unnecessary) braces syntax, but it actually works. Additionally it's a good VBA coding practice to be using a specific Sheet every time you're working with a Range object

So to speak, you sort of kill two birds with one stone here

Samuel Hulla
  • 6,617
  • 7
  • 36
  • 70