Home

how to return a value from VBA to XL?

%3CLINGO-SUB%20id%3D%22lingo-sub-532053%22%20slang%3D%22en-US%22%3Ehow%20to%20return%20a%20value%20from%20VBA%20to%20XL%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-532053%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20better%20way%20than%20copying%20and%20pasting%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-532053%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-532122%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20return%20a%20value%20from%20VBA%20to%20XL%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-532122%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333860%22%20target%3D%22_blank%22%3E%40Heckmate%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20wish%20to%20replicate%20formulas%2C%20formats%2C%20conditional%20formats%2C%20number%20formats%20etc.%20copy%20and%20paste%20will%20do%20it.%26nbsp%3B%20If%20you%20simply%20wish%20to%20transfer%20a%20value%20there%20are%20far%20more%20efficient%20and%20specific%20approaches.%3C%2FP%3E%3CP%3EI%20would%20always%20name%20the%20'source'%20and%20'destination'%20ranges%20to%20avoid%20problems%20when%20rows%20are%20inserted%20on%20the%20sheet.%26nbsp%3B%20An%20example%20might%20be%3C%2FP%3E%3CP%3ERange(%22destination%22).Value%20%3D%20Range(%22source%22).Value%3C%2FP%3E%3CP%3Eor%20there%20is%20another%20notation%3C%2FP%3E%3CP%3E%5Bdestination%5D.Value%20%3D%20%5Bsource%5D.Value%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20wish%20to%20use%20variables%20in%20the%20VBA%20then%20declare%20the%20ranges%3C%2FP%3E%3CP%3EDim%20sourceRng%20as%20Range%3C%2FP%3E%3CP%3Eand%20then%20set%20the%20range%20object%3C%2FP%3E%3CP%3ESet%20sourceRng%20%3D%20Range(%22source%22)%3C%2FP%3E%3CP%3EIntelliSense%20will%20then%20suggest%20properties%20when%20you%20add%20each%20period%20%22.%22%20as%20in%3C%2FP%3E%3CP%3EsourceRng.Value%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Heckmate
Occasional Visitor

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

 

Related Conversations