Vba variables in excel functions

[sazzy]

Hi,

How do you insert a variable (e.g. a cell determined by the user) into a worksheet function. i.e.

Function Coding(Unit, cell)

If Len(cell) = 4 And Unit = "E" Then
Coding = "=LEFT(cell,1)" & unit
End If

End Function

Hope that makes sense

Thanks

 

[Jonmo1]

Welcome to the board..

You almost had it..

remove the quotes around the =Left(...) & Unit

Function Coding(Unit, cell)

If Len(cell) = 4 And Unit = "E" Then
Coding = LEFT(cell,1) & unit
End If

End Function

Hope this helps.

 

[sazzy]

Thanks jonmo1, that works.

I've got a general question though. If I wanted to use a macro to insert a formula (not just the calculated value) into a cell but one of the arguments in the formula was a variable i.e.

Function Coding(Unit, cell)

If Len(cell) <> 4 And Unit = "E" Then
Coding = "=LEFT(cell,1)"
End If

End Function

How I write the code so that once executed in excel the cell shows =LEFT(A?, 1), where A? is a variable cell selected by the user?!

Make sense?!