: is there a way or code to convert A1 reference style to C1R1 style in vb.net. or is there a method like ConvertFormula as used in VBA, in vb.NET?
I guess you'll have to implement your own logic for this (if no excel dll is present on your running machine).
This function should look somehow like this:
Public Function ConvertA2RC(ByVal AForm As String, ByVal CurRow As Integer, ByVal CurCol As Integer) As String
Dim r, c, Ret As String : r = Nothing : c = Nothing
If AForm Is Nothing OrElse AForm.Length = 0 OrElse CurRow = 0 OrElse CurCol = 0 Then Return Nothing
AForm = AForm.ToUpper 'A1, B2, $A1, A$1, $A$1
For i As Integer = 1 To AForm.Length - 1
If Char.IsDigit(AForm(i)) Or AForm(i) = "$"c Then r = AForm.Substring(i) : c = AForm.Substring(0, i) : Exit For
If r Is Nothing OrElse c Is Nothing Then Throw New Exception("Unsupported A-Form: " & AForm)
Ret = GenRC(CInt(r.Trim("$"c)), CurRow, r(0) = "$"c, "R"c)
Ret &= GenRC(ColIndex(c.Trim("$"c)), CurCol, c(0) = "$"c, "C"c)
Private Function GenRC(ByVal A As Integer, ByVal Cur As Integer, ByVal IsFixed As Boolean, ByVal T As Char) As String
If IsFixed Then Return T & A
If A = Cur Then Return T Else Return T & "[" & A - Cur & "]"
Public Function ColIndex(ByVal Col As String) As Integer
If Col Is Nothing OrElse Col.Length = 0 Then Return 0
Col = Col.ToUpper
If Col.Length = 1 Then
Return AscW(Col(0)) - 64
Return (AscW(Col(0)) - 64) * 26 + AscW(Col(1)) - 64
Function ConvertA2RC accepts A1-form address and converts it to R1C1 style.
I did not perform too much tests, but when you have B22:
it returns correct answer.
This can convert only basic single addresses. It can not convert ranges (like entire rows, entire columns - 1:1, or A:A etc.. )
MsgBox(ConvertA2RC("B22", 23, 2) & "*" & ConvertA2RC("$C$23", 23, 2) & "+" & ConvertA2RC("B$27", 23, 2) & "+" & ConvertA2RC("$D25", 23, 2))
I hope this will inspire you
Don't take life too seriously anyway you won't escape alive from it!