SOLVED

Why does copying cells work in a Sub but not in a Function?

%3CLINGO-SUB%20id%3D%22lingo-sub-1332155%22%20slang%3D%22en-US%22%3EWhy%20does%20copying%20cells%20work%20in%20a%20Sub%20but%20not%20in%20a%20Function%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332155%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20new%20to%20VBA%20and%20have%20come%20across%20a%20problem%3A%20why%20does%20copying%20values%20from%20one%20cell%20to%20another%20work%20in%20a%20Sub%20but%20not%20in%20a%20Function%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20simplified%20the%20problem%20to%20this%20description%20and%20sample%20code%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20enter%20a%20value%20in%20A2%20and%20have%20it%20copied%20to%20D2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20B2%20says%20%22%3DAutoCopyCells(A2)%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFunction%20AutoCopyCells(iSource%20as%20Integer)%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20Range(%22A2%22).Copy%20Range(%22D2%22)%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20AutoCopyCells%20%3D%20True%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20code%20in%20this%20function%20is%20executed%20(I%20have%20stepped%20through%20it%20many%20times)%20but%20it%20does%20not%20copy%20what%20I%20type%20into%20cell%20A2%20into%20D2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20added%20a%20button%20(positioned%20vaguely%20in%20C2)%20that%20calls%20ButtonClickUpdateCells%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20ButtonClickUpdateCells()%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20Range(%22A2%22).Copy%20Range(%22D2%22)%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20clicking%20the%20button%20calling%20the%20Sub%20the%20contents%20of%20A2%20is%20copied%20to%20D2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEffectively%20the%20code%20in%20the%20function%20and%20Sub%20is%20the%20same%20(except%20the%20function%20has%20to%20return%20a%20value).%20So%20why%20does%20clicking%20on%20the%20button%20(which%20calls%20the%20Sub)%20work%20when%20the%20function%20doesn't%3F%20What%20am%20I%20missing%3F%3C%2FP%3E%3CP%3EMany%20thanks%2C%3C%2FP%3E%3CP%3ELuke%20Dukie%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1332155%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-1332467%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20does%20copying%20cells%20work%20in%20a%20Sub%20but%20not%20in%20a%20Function%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1332467%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F633305%22%20target%3D%22_blank%22%3E%40Lukie_Dukie%3C%2FA%3E%26nbsp%3BI%20guess%20that's%20because%20a%20function%20works%20%3CSTRONG%3Einside%3C%2FSTRONG%3E%20a%20cell.%20Just%20like%20you%20can't%20put%20e.g.%20%3DSUM(A1%3AA10)%20in%20cell%20A11%20and%20have%20the%20result%20returned%20in%20B11.%20But%2C%20with%20a%20SUB%20you%20can%20say%20%22sum%20%26nbsp%3BA1%3AA10%20and%20put%20the%20result%20somewhere%20else%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello,

 

I am new to VBA and have come across a problem: why does copying values from one cell to another work in a Sub but not in a Function?

 

I have simplified the problem to this description and sample code below.

 

I want to enter a value in A2 and have it copied to D2

 

Cell B2 says "=AutoCopyCells(A2)"

 

Function AutoCopyCells(iSource as Integer)
    Range("A2").Copy Range("D2")
    AutoCopyCells = True
End Function

 

The code in this function is executed (I have stepped through it many times) but it does not copy what I type into cell A2 into D2

 

I have added a button (positioned vaguely in C2) that calls ButtonClickUpdateCells

 

Sub ButtonClickUpdateCells()
    Range("A2").Copy Range("D2")
End Sub

 

When clicking the button calling the Sub the contents of A2 is copied to D2

 

Effectively the code in the function and Sub is the same (except the function has to return a value). So why does clicking on the button (which calls the Sub) work when the function doesn't? What am I missing?

Many thanks,

Luke Dukie

2 Replies
Highlighted
Best Response confirmed by Lukie_Dukie (New Contributor)
Solution

@Lukie_Dukie I guess that's because a function works inside a cell. Just like you can't put e.g. =SUM(A1:A10) in cell A11 and have the result returned in B11. But, with a SUB you can say "sum  A1:A10 and put the result somewhere else".

Highlighted

@Riny_van_Eekelen- Thank you, it is the first time I have seen that as an explanation of the difference between functions and subs - I must be trying to learn VBA from the wrong resources!