0

It would be very basic question.

I want to declare cells as variant, to substitute repeated typing, like

a = cells(1,1) or a = range("a1")

then a.select or a.value, etc..

but error occur with

runtime 424

I want to understand what is problem, and how can i do.

Thanks,

arun v
  • 852
  • 7
  • 19
Bummy
  • 1
  • 1
  • 2
  • 3
    You have to declare a range and then set it. Like `Dim a as Range` and `Set a = .Range("A1")`. Runtime error 424 tells you there is no range object declared. Keyword here is `Set` which is used to actually create a range object. – JvdV Jul 29 '19 at 11:33
  • You are assigning the value of a cell to a variable, which does not have a value property and cannot be selected. – SJR Jul 29 '19 at 11:38
  • Also using `.Cells` or `.Range` without explicitly declaring in what workbook and worksheet they are is prone to errors. You should take a look at https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Luuklag Jul 29 '19 at 11:39

1 Answers1

2

You've run into an interesting 'feature' of VBA - the Default Property. Most objects in the Excel Library have a default property, which is used where no property is specified. In the case of the range object the default property is the Value property This means that the line

    Range("A1") = 4

is interpreted by VBA as meaning

  Let Range("A1").Value = 4

In your case, the line 

   a = cells(1,1)

is ultimately interpreted as 

   Let a = range("a1").value

However, had you written 

   Set a = cells(1,1)

then that set forces vba to treat cells(1,1) as an object and thus to set a to act as a pointer to the cell A1, which is what you wanted.

(Had VBA still insisted on using Let for value assignments no confusion would be possible as your original line would have errored for ambiguity - but the Let statement is now optional so you don't get an error if you leave it off)

Harassed Dad
  • 4,669
  • 1
  • 10
  • 12