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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies