Advice about use of .value in VBA

[SR1]

Hi,

I am working on simplifying my code to make it easier to follow and check for errors, and I am wondering how often the suffix .Value is needed to determine the value of a cell reference.

I would imagine you need the .Value to assign variables (eg x = Cells (r, c).Value) but is it necessary for functions (eg Instr(Cells(x, y).Value, "String") - wouldn't just Cells(x,y) suffice)?

I'm pretty sure you'd need it for other purposes, eg textbox inputs, but is it needed in If statements (like If Cells (x, y).Value = "")?

Any advice on this would be much appreciated.

 

[Fazza]

it seems you already have a good understanding of when .value is needed

and basically you can omit the .value as you demonstrate

it comes down to a personal choice as to whether one uses the .value (and same for .value2) when it is not required

personally, I always use the .value
it reminds me everytime that I'm using the value of the range & avoids me accidentally ever trying to assign a range when I should be using the value
for example, variableX = cells(x,y)
when I really want SET rngVariableX = cells(x,y)

often people explain that the quicker omission assuming the default property could be an issue in the future if the default property changes. not a big risk, I think
still I'd rather be explicit

for example
dim rInputTable as Excel.Range
dim vArray as variant

set rInput = range...
vArray = rInput.value2

up to you really, hth