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