Unable to call VBA function from Excel Worksheet Cell

Question

While trying to use a custom made VBA function from an excel worksheet cell, I get the error: "The formula you typed contains an error"

However, when I call the same function from VBA, it works alright.

VBA Function:

Public Function R1C1_value(RowIndex As Integer, ColIndex As Integer, Optional WorkSheetName As String) As Variant
    Dim ws As WorkSheet
    If Trim(WorkSheetName) = "" Then
        Set ws = ActiveSheet
    Else
        Set ws = ActiveWorkbook.Worksheets(WorkSheetName)
    End If
    R1C1_value = ws.Cells(RowIndex, ColIndex).Value
End Function

VBA Function Call:

Sub R1C1_value_test()
    Dim res
    res = R1C1_value(1, 9, "CVP")
    MsgBox "res: " & res
End Sub

Excel Function call

=R1C1_value(1,9)

Ron Rosenfeld · Accepted Answer · 2014-09-06 10:25:15Z

2

The function name is not a Valid name in Excel, even though it is accepted in VBA. If you change it to xR1C1_value, your function should work.

answered Sep 6, 2014 at 10:25

Ron Rosenfeld

62.1k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

luvlogic Over a year ago

Thanks, it worked. BTW, what is the reason behind this?

Ron Rosenfeld Over a year ago

It is not clear to me, from Excel HELP, why this particular string should be rejected by Excel. Names cannot conflict with cell addresses, or with Excel built-in names; but it seems there are other, undocumented, reasons for rejection also.

DLeh Over a year ago

i'm guessing that because R1 is a valid address for a cell, it wouldn't be able to tell if you were talking about a cell value or not

Ron Rosenfeld Over a year ago

@DLeh Your comment does not make sense to me. The function name was not R1 and the string R1C1_value is not a valid cell address. Furthermore, even if you posit that a function name that starts with something that might be a valid cell address will be rejected, a function name like A1C1_value, which is of the same format, will be accepted.

DLeh Over a year ago

You didn't provide much information about what was invalid, I was just taking a guess. So it's something specific to R1C1 that is invalid? I'm having trouble getting excel to find functions I call and was trying to figure out if it was because the function name could be trying to parse as a cell address or something

|

Collectives™ on Stack Overflow

Unable to call VBA function from Excel Worksheet Cell

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related