how to return a value from VBA to XL?

Copper Contributor

Is there a better way than copying and pasting?

1 Reply

@Heckmate 

If you wish to replicate formulas, formats, conditional formats, number formats etc. copy and paste will do it.  If you simply wish to transfer a value there are far more efficient and specific approaches.

I would always name the 'source' and 'destination' ranges to avoid problems when rows are inserted on the sheet.  An example might be

Range("destination").Value = Range("source").Value

or there is another notation

[destination].Value = [source].Value

 

If you wish to use variables in the VBA then declare the ranges

Dim sourceRng as Range

and then set the range object

Set sourceRng = Range("source")

IntelliSense will then suggest properties when you add each period "." as in

sourceRng.Value