VBA referencing a cell inside of a function

BobSullivan
3StarLounger
Posts: 235
Joined: 08 Jun 2010, 20:03
Location: Morgantown, PA

VBA referencing a cell inside of a function

Post by BobSullivan »

I'm trying to write a custom function into a cell in a worksheet. the custom function uses information from a user form. I write the information from the user form into column A, then want to put the custom function into Column B to work with the information already written into Column A. The row number is a variable, but the Function column is column B. I tried to use these two lines of code:

Code: Select all

contents = "=GetProductName(" & "A" & RowNum & ")"
Cells(RowNum, 2).FormulaR1C1 = contents
The result that I get in the cell is =GetProductName('A8') (if the row number variable is 8). The single quotes are causing a value error in the cell. How can I remove the single quotes, or, is there a better way to insert a custom function in a cell?
Cordially,

Bob Sullivan
Elverson, PA

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA referencing a cell inside of a function

Post by HansV »

You try to use an A1 style cell address in FormulaR1C1, so Excel interprets A8 as a name instead of a cell reference. Use

Code: Select all

Cells(RowNum, 2).Formula = contents
Best wishes,
Hans

BobSullivan
3StarLounger
Posts: 235
Joined: 08 Jun 2010, 20:03
Location: Morgantown, PA

Re: VBA referencing a cell inside of a function

Post by BobSullivan »

Thank you!
Cordially,

Bob Sullivan
Elverson, PA

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA referencing a cell inside of a function

Post by HansV »

Alternatively, you could use R1C1:

Code: Select all

contents = "=GetProductName(R" & RowNum & "C1)"
Cells(RowNum, 2).FormulaR1C1 = contents
And if RowNum is the row number of the cell with the formula, it could even be

Code: Select all

contents = "=GetProductName(RC1)"
Cells(RowNum, 2).FormulaR1C1 = contents
Best wishes,
Hans

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: VBA referencing a cell inside of a function

Post by DocAElstein »

Hello
Just out of passing interest:
If you use .Value in VBA to assign a text to a cell, then it seems as though that does something similar to as if you manually write that text into a cell. (In Excel, or when using VBA to put a string into a cell , it seems to be the presence of a = in the fist character which is the important thing telling Excel that you have a formula.)

So in the example you could use .Value in place of .Formula
I think we have discussed this here before, and the majority opinion was that .Formula “looks” more correct. So for Aesthetic gratification you might want to stay with .Formula, but its worth noting that .Value would work as well.


_.________________________________________

Something new and interesting I just noticed: If you use .Value in this way, in VBA, then you can use in the string text you assign either
the column letter and row number convention ( the “A1” like way )
or
the column number and row number convention ( the “R1C1” way )
, and the results will be the same, regardless of what convention you are using in your Excel. (What appears in the cell will depend on your chosen convention in your option settings. ( Usually if you never changed this and are using Excel “out of the box”, then you will have the column letter and row number convention ( the “A1” like way ) ) )

So in this demo code snippet I use the .Value way in VBA. For the string formula function text to be put in the cell, (what you put in that variable Contents), you can use the original string or one of Hans column number and row number convention examples. You will get similar results from either in the cells, and the convention it appears in the formula bar will depend on your settings. Your settings, as I memtioned, will, unless you have specifically in Excel Options changed them, be the typical column letter and row number convention ( the “A1” like way ). That will mean that in the cell you get

=GetProductName(A8) , if you use Let Contents = "=GetProductName(" & "A" & RowNum & ")"

And if you use "=GetProductName(RC1)" , you will get in the cell =GetProductName($A8)

( The extra $ is coming in because C1 is absolute.
( R is relative, - more fully it should be R[0] , but R is one of those implicit default things defaulting to R[0] ) )

Code: Select all

 '  https://eileenslounge.com/viewtopic.php?f=27&t=38379
Function GetProductName(ByVal Rng As Range) As String
 Let GetProductName = "Product " & Rng.Value
End Function
Sub WriteCustomFunctionIntoCell()
 Let ActiveSheet.Range("A8").Value = "Name"  ' Just for demo purposes
Dim RowNum As Long: Let RowNum = 8           ' Just for demo purposes

Dim Contents As String:
 Let Contents = "=GetProductName(" & "A" & RowNum & ")"  '  Same as  "=GetProductName(A" & RowNum & ")"
 Let Contents = "=GetProductName(RC1)"  ' Note: that is the same as  "=GetProductName(R[0]C1)" - relative row amd absolute column
 Let ActiveSheet.Cells(RowNum, 2).Value = Contents
End Sub



Alan
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: VBA referencing a cell inside of a function

Post by rory »

Using .Value for formulas and relying on Excel knowing what you meant is not a terribly good idea though, as the results will depend on what is in the formula. So if your code ends up passing something as an R1C1 formula that could also be an A1 formula, you'll get weird results with no warning. For example,
=SUMIF(C3,RC1,C2)
intended as R1C1 syntax would be entered exactly like that in A1 format, even though it is probably not what you'd want.
Regards,
Rory

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: VBA referencing a cell inside of a function

Post by DocAElstein »

My gut feel is that its one of those things where you would only get a problem in the practice if you tried hard to get a problem. But I could be wrong, as I don’t have much experience with many formulas other than simple ones.
I think I may have experienced problems when Excel took a range reference in a different convention as I wanted to, but I don’t think it ever came up when using .Value in the way I was talking about


A rule of thumb perhaps could be to say, (in particular perhaps for someone starting with these sort of things),
If ….
_ you would put the following manually in your Excel in a cell and it works like you wont
= sldddlkw(asnskja)
Then…
_ in VBA you can do this
…. .Value = "= sldddlkw(asnskja)"
..which is saying again something like .. using .Value = “=X“ in VBA has a similar effect to you physically writing =X in the cell

(
Usually the only subtlety to remember as a beginner is that if in Excel you had some quotes, like
= sldddlkw(asnskja),"Hi")
Then that chucks a spanner in the works in VBA since you already use enclosing quotes in the VBA code line to tell VBA you are giving a text string. Usually doubling up the quotes seems to get over that problem and VBA is happy then to put in the cell the single quotes you wanted. So usually this would work in that example
…. .Value = "= sldddlkw(asnskja),""Hi"")"
)

I think later when you fully understand what all the differences and uses fully of
the column letter and row number convention ( the “A1” like way )
or
the column number and row number convention ( the “R1C1” way )
, then maybe you can think about using
.Forumula
Or
.FormulaR1C1
( Or maybe even
.FormulaLocal )
Or God knows what else is lurking about to confuse further....

I say all that because, The original problem of this Thread was a result of the OP using .FormulaR1C1
I recall myself making some similar mistakes myself early on, and I think I have seen similar problems crop up. I think it was because the macro recorder was chucking up a lot of R C stuff, and somewhere along the way I wrongly got the idea that using the R C stuff was how you should do it in VBA. It was sometime before I really fully understood what the different ways to reference cells was all about.
I think when you get as far as using .Formula or .FormulaR1C1 et al in VBA, then you really should be approaching the level of really understanding fully what the range object and range referencing is all about, as well as getting clear in your mind the 4 ways in Excel to reference a cell,
_ Absolute referencing with the column letter and row number convention
_ Relative referencing with the column letter and row number convention
_ Absolute referencing with the column number and row number convention
_ Relative referencing with the column number and row number convention

That’s a level a lot of Excel uses never seem to get to.

Alan

_.____________________

Ref
https://teylyn.com/2017/03/21/dollarsigns/#comment-191
https://www.excelfox.com/forum/showthre ... ue-Anomaly
https://www.excelfox.com/forum/showthre ... cing/page8
Rory RC https://eileenslounge.com/viewtopic.php ... 59#p296859
https://www.excelfox.com/forum/showthre ... #post24006
https://www.excelfox.com/forum/showthre ... #post23185
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(