Evaluating current user name in ambitious formula string via VBA

[dougbert]

Hi all,

Perhaps this is a little too ambitious for a formula string, but it seems like it should be made able to work.

Set up: I'm trying to place numerous items into the single cell "A1" of worksheet "Sheet1" via a formula produced by a VBA macro. Here's a visual sample of the output of the items I'm currently able to produce in this single cell:

Data last updated:
10/3/2012 @ 11:29 AM Mountain Time
By:
90

Seems simple enough, right? The 90 at the bottom is a count of non-blank cells in a range beginning a couple of rows below this cell. However, note the conspicuous absence of anything after "By: " on that line. What do I want there? The current username of the person about to save and timestamp cell A1. However, adding the variable for the user's name breaks the macro and consequently the formula I expect to produce.

I've boiled my macro code that produces the output above down to the basics:

Sub SavedTimeStamp()
Dim CurrUser As String
CurrUser = Environ("UserName")
MsgBox(CurrUser)   'just to prove it is working
Sheets("Sheet1").Range("A1") = "=""Data last updated: ""& CHAR(10) & Text(Now(),""m/d/yyyy"")&"" @ ""& TEXT(NOW(),""h:mm AM/PM;@"")&"" Mountain Time""&CHAR(10)&""By: ""&CHAR(10)&SUMPRODUCT(--(A3:A93<>""""),--($X$3:$X$93=1))"
End Sub

I apologize for the long string w/o using underscore line breaks, but I couldn't figure out where to place them to avoid "breaking" the current working code. Running the macro produces the following formula in cell A1 of Sheet1:

="Data last updated: "& CHAR(10) & TEXT(NOW(),"m/d/yyyy")&" @ "& TEXT(NOW(),"h:mm AM/PM;@")&" Mountain Time"&CHAR(10)&"By: "&CHAR(10)&SUMPRODUCT(--(A3:A93<>""),--($X$3:$X$93=1))

The formula now displays the output I displayed at the top of this message.

So, here's the problem. In the macro, when I attempt to insert the variable CurrUser into the string just after the &""By: "" portion of the string, the macro will no longer execute, producing a "Run-time 1004" error.

I realize that the CurrUser = Environ("UserName") must be evaluated separately from the rest of the string to receive the value for CurrUser while still in the macro. Essentially, this is a string, followed by a variable CurrUser that must be evaluated, followed by the remainder of the string that comprises the formula I'm trying to produce in cell A1. So, here's what the macro looks like when I attempt to insert the CurrUser variable into the string, trying to place it between 2 strings:

Sub SavedTimeStampUser()
Dim CurrUser As String
CurrUser = Environ("UserName")
MsgBox(CurrUser)   'just to prove it is working
Sheets("Sheet1").Range("A1") = "=""Data last updated: ""& CHAR(10) & Text(Now(),""m/d/yyyy"")&"" @ ""& TEXT(NOW(),""h:mm AM/PM;@"")&"" Mountain Time""& CHAR(10) & ""By: """ & """ & CurrUser & """  & "CHAR(10) & SUMPRODUCT(--(A3:A93<>""""),--($X$3:$X$93=1))"
End Sub

In the portion of the string & ""By: """ & """ & CurrUser & """ , I believe the triple " after the By: is necessary to close the first string in the formula. This allows CurrUser to be evaluated. However, the formula being produced in cell A1 requires quotes around the derived CurrUser name, in order to display properly via the formula I'm attempting to produce.

BTW: I've tried numerous ideas, including placing .formula, .value, .text right after Sheets("Sheet1").Range("A1"), but the first version of the macro above works fine w/o them.

It seems like this is a case of one or more syntax errors. Any help is very appreciated.

 

[tusharm]

When you want to add a double-quote to a string you have to put in two quotes. So, your

& ""By: """ & """ & CurrUser & """  & "CHAR(10) &

should be

& """By: """ & """" & CurrUser & """"  & "CHAR(10) &

Here's how to interpret your three double-quotes: The first starts the string literal. Then the next two become a single one in the output string. But, now the string is not closed.
That's what adding the fourth double-quote, as I did, accomplishes.

You could simplify the string some more but I like the way you build the string and the formula. They are both descriptive to someone reading the code / worksheet.

Hi all,

Perhaps this is a little too ambitious for a formula string, but it seems like it should be made able to work.

Set up: I'm trying to place numerous items into the single cell "A1" of worksheet "Sheet1" via a formula produced by a VBA macro. Here's a visual sample of the output of the items I'm currently able to produce in this single cell:

Data last updated:
10/3/2012 @ 11:29 AM Mountain Time
By:
90

Seems simple enough, right? The 90 at the bottom is a count of non-blank cells in a range beginning a couple of rows below this cell. However, note the conspicuous absence of anything after "By: " on that line. What do I want there? The current username of the person about to save and timestamp cell A1. However, adding the variable for the user's name breaks the macro and consequently the formula I expect to produce.

I've boiled my macro code that produces the output above down to the basics:

Sub SavedTimeStamp()
Dim CurrUser As String
CurrUser = Environ("UserName")
MsgBox(CurrUser)   'just to prove it is working
Sheets("Sheet1").Range("A1") = "=""Data last updated: ""& CHAR(10) & Text(Now(),""m/d/yyyy"")&"" @ ""& TEXT(NOW(),""h:mm AM/PM;@"")&"" Mountain Time""&CHAR(10)&""By: ""&CHAR(10)&SUMPRODUCT(--(A3:A93<>""""),--($X$3:$X$93=1))"
End Sub

I apologize for the long string w/o using underscore line breaks, but I couldn't figure out where to place them to avoid "breaking" the current working code. Running the macro produces the following formula in cell A1 of Sheet1:

="Data last updated: "& CHAR(10) & TEXT(NOW(),"m/d/yyyy")&" @ "& TEXT(NOW(),"h:mm AM/PM;@")&" Mountain Time"&CHAR(10)&"By: "&CHAR(10)&SUMPRODUCT(--(A3:A93<>""),--($X$3:$X$93=1))

The formula now displays the output I displayed at the top of this message.

So, here's the problem. In the macro, when I attempt to insert the variable CurrUser into the string just after the &""By: "" portion of the string, the macro will no longer execute, producing a "Run-time 1004" error.

I realize that the CurrUser = Environ("UserName") must be evaluated separately from the rest of the string to receive the value for CurrUser while still in the macro. Essentially, this is a string, followed by a variable CurrUser that must be evaluated, followed by the remainder of the string that comprises the formula I'm trying to produce in cell A1. So, here's what the macro looks like when I attempt to insert the CurrUser variable into the string, trying to place it between 2 strings:

Sub SavedTimeStampUser()
Dim CurrUser As String
CurrUser = Environ("UserName")
MsgBox(CurrUser)   'just to prove it is working
Sheets("Sheet1").Range("A1") = "=""Data last updated: ""& CHAR(10) & Text(Now(),""m/d/yyyy"")&"" @ ""& TEXT(NOW(),""h:mm AM/PM;@"")&"" Mountain Time""& CHAR(10) & ""By: """ & """ & CurrUser & """  & "CHAR(10) & SUMPRODUCT(--(A3:A93<>""""),--($X$3:$X$93=1))"
End Sub

In the portion of the string & ""By: """ & """ & CurrUser & """ , I believe the triple " after the By: is necessary to close the first string in the formula. This allows CurrUser to be evaluated. However, the formula being produced in cell A1 requires quotes around the derived CurrUser name, in order to display properly via the formula I'm attempting to produce.

BTW: I've tried numerous ideas, including placing .formula, .value, .text right after Sheets("Sheet1").Range("A1"), but the first version of the macro above works fine w/o them.

It seems like this is a case of one or more syntax errors. Any help is very appreciated.

 

[dougbert]

Tusham,

Thanks for the quick reply. I tried your suggestion, but still receive 1004 error. I really don't think I want double-quotes around the CurrUser variable. In a normal formula expressing the results of a string, I'd only want single quotes, which is why I used """. No doubt, you understand that CurrUser must be evaluated while the macro runs, but must produce the resultant string in the formula being produced in cell A1.

If you or anyone have more ideas, bring 'em on!

 

[dougbert]

[SOLVED] Re: Evaluating current user name in ambitious formula string via VBA

Yay! I just figured out the syntax! Here is just that line in the macro corrected to work properly:

Sheets("Sheet1").Range("A1") = "=""Data last updated: ""& CHAR(10) & Text(Now(),""m/d/yyyy"")&"" @ ""& TEXT(NOW(),""h:mm AM/PM;@"")&"" Mountain Time"" & CHAR(10) & ""By: "" & """ & CurrUser & """ & CHAR(10) & SUMPRODUCT(--(A3:A93<>""""),--($X$3:$X$93=1))"

Now, I'll try to figure out how to add underscore line breaks!

Thanks for your efforts!

 

[dougbert]

Re: [SOLVED] Re: Evaluating current user name in ambitious formula string via VBA

I cleaned up the string for readability, in case someone else could benefit from this. I also added an extra line to separate the timestamp from the data.

Sheets("Sheet1").Range("A1") = "=""Data last updated: "" & CHAR(10) & Text(Now(),""m/d/yyyy"") & "" @ "" & TEXT(NOW(),""h:mm AM/PM;@"") & "" Mountain Time"" & CHAR(10) & ""By: "" & """ & CurrUser & """ & CHAR(10) & CHAR(10) & SUMPRODUCT(--(A3:A93<>""""),--($X$3:$X$93=1))"

 

[Marcelo Branco]

Re: [SOLVED] Re: Evaluating current user name in ambitious formula string via VBA

Maybe this

Sub aTest()
    Dim CurrUser As String
    CurrUser = Environ("UserName")
    MsgBox (CurrUser)  'just to prove it is working
    Sheets("Sheet1").Range("A1") = "Data last updated: " & Chr(10) & Application.Text(Now(), "m/d/yyyy") & " @ " & _
        Application.Text(Now(), "h:mm AM/PM") & " Mountain Time" & Chr(10) & "By: " & Chr(10) & _
        Evaluate("SUMPRODUCT(--(A3:A93<>""""),--($X$3:$X$93=1))")
End Sub

M.