CODEWITHSUNDEEP
vbaexcel
DisplayName
DisplayName

Reputation: 13

VBA Full formula to cell

I've written this line of code to put this formula to cell T2:

Sheets("Output").Range("T2").Formula = _ 
     "=CONCATENATE(TEXT(C2;""MM/DD/JJJJ"");""-"";TEXT(S2;""MM/DD/JJJJ""))"

But I'm getting the following error:

Run-time error '1004': Application-defined or object-definded error

The formula is working if I copy-paste it, so I guess this is a syntax error.

Upvotes: 1

Views: 224

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Excel uses the list separator character to delimit the parameters of a function.

Hard-coding a semicolon, or a comma for that matter, into your formula, makes your code vulnerable to changes in regional settings, as a hard-coded list separator character will stop working as soon as the Windows settings are modified, or if your code runs on a computer with different settings.

You can use some Win32 API to get the correct character from the regional settings, by adding this code in a standard module (.bas) that you can name Win32:

Option Explicit

Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" _
    (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long

Private Declare Function GetUserDefaultLCID% Lib "kernel32" ()
Private Const LOCALE_SLIST = &HC

Public Function GetListSeparator() As String

    Dim ListSeparator As String
    Dim iRetVal1 As Long
    Dim iRetVal2 As Long
    Dim lpLCDataVar As String

    Dim Position As Integer
    Dim Locale As Long

    Locale = GetUserDefaultLCID()

    iRetVal1 = GetLocaleInfo(Locale, LOCALE_SLIST, lpLCDataVar, 0)

    ListSeparator = String$(iRetVal1, 0)

    iRetVal2 = GetLocaleInfo(Locale, LOCALE_SLIST, ListSeparator, iRetVal1)

    Position = InStr(ListSeparator, Chr$(0))
    If Position > 0 Then
        ListSeparator = Left$(ListSeparator, Position - 1)
    End If

    GetListSeparator = ListSeparator

End Function

(adapted from this PCReview post)

The correct code would then be:

Dim separator As String
separator = Win32.GetListSeparator

Sheets("Output").Range("T2").Formula = _ 
    "=CONCATENATE(TEXT(C2" + separator + """MM/DD/JJJJ"")" + separator + """-""" + separator + "TEXT(S2" + separator + """MM/DD/JJJJ""))"

...which is utterly annoying. VBA needs a proper StringFormat method (I made one), so that you can do this:

Dim separator As String
separator = Win32.GetListSeparator

Dim formatString As String
formatString = "=CONCATENATE(TEXT(C2{0}\qMM/DD/JJJJ\q){0}\q-\q{0}TEXT(S2{0}\qMM/DD/JJJJ\q))"

Sheets("Output").Range("T2").Formula = StringFormat(formatString, separator)

Upvotes: 3

N. Pavon
N. Pavon

Reputation: 839

Correct code:

Sheets("Output").Range("T2").Formula = _ 
     "=CONCATENATE(TEXT(C2,""MM/DD/JJJJ""),""-"",TEXT(S2,""MM/DD/JJJJ""))"

Upvotes: 1

Related Questions