How to fetch constants in Excel in RC mode

[LAUJJL]

Hi

I want to work with VBA in DDE mode to allow some batch processing. In Excel I prefer to use L1C1 notation than A1 because it seems easier to manipulate the cells indexes with the first one.
But I have not found if it is possible to set any option in Vensim to allow the use of L1C1 (R1C1 in English L1C1 in French) instead of A1, like in 'get xls constants('file','sheet','A1')' replaced by
'get xls constants('file','sheet','L1C1').
I use Vensim DSS 5.11A
Best regards.
JJ

[tomfid]

Unfortunately this is deliberate, because the parser couldn't tell the difference between a letter that represented a column reference ('r') and the start of an 'r1c1' reference. I'm sure we could add similar functions that handled the r1c1 syntax though. I've entered the request.

[Administrator]

You can work with R1C1 style references as you are working in VBA.

Paste the following function into your code.

Code: Select all

Function R1C1converter(Address As String, ByVal R1C1_output As Integer, ByVal bRemoveExtraChars As Boolean) As String
    Dim x As Variant
    'If RefCell Is Nothing Then Set RefCell = ActiveCell
    If R1C1_output = xlR1C1 Then
        x = Application.ConvertFormula(Address, xlA1, xlR1C1, True) 'Convert A1 to R1C1
    Else
        x = Application.ConvertFormula(Address, xlR1C1, xlA1, True) 'Convert R1C1 to A1
    End If
    If IsError(x) Then
        R1C1converter = Address
    Else
         
         'If input address is A1 reference and A1 is requested output, then Application.ConvertFormula
         'surrounds the address in single quotes.
        If Right(x, 1) = "'" Then
            R1C1converter = Mid(x, 2, Len(x) - 2)
        Else
            R1C1converter = x
        End If
        If bRemoveExtraChars = True Then
            R1C1converter = Replace(R1C1converter, "[", "")
            R1C1converter = Replace(R1C1converter, "]", "")
            R1C1converter = Replace(R1C1converter, "$", "")
        End If
    End If
End Function

Then use it like this

Code: Select all

str_A1_StyleReference = R1C1converter("R10C20", xlA1, True)
str_R1C1_StyleReference = R1C1converter("E10", xlR1C1, True)

You can work in R1C1 style references, then before you pass the address to Vensim, call the function to convert it to A1 style.

Tony.