CODEWITHSUNDEEP
excelvba
user9758877
user9758877

Reputation:

VBA EXCEL formula in a String

Hi I would like to put into a STRING the following formula

Dim stringAppoggio As String
Dim myMonth As String

myMonth = "January 2020"

stringAppoggio="=DAY(EOMONTH(DATEVALUE(01-'&myMonth&'-&YEAR(myMonth)),0))"

It doesn't really give me a syntax error, but I don't see the result of the formula in the String

Thank you

Upvotes: 1

Views: 99

Answers (2)

Mesut Akcan
Mesut Akcan

Reputation: 897

Dim stringAppoggio As String
Dim myMonth As String

myMonth = Chr(34) & "January 2020" & Chr(34)

stringAppoggio = "=DAY(EDATE(" & myMonth & ",1)-DAY(" & myMonth & "))"

Upvotes: 0

JvdV
JvdV

Reputation: 75840

Well, as per my comment, there are a few mistakes here:

  • You have used single quotes instead of double quotes to seperate your variable from VBA formula syntax
  • You have forgotten the quotes at all around your second myMonth variable
  • You have created a formula that simply won't work

Keep in mind, your variable is not just a month but a string holding a month and year > "January 2020", therefor DATEVALUE won't need the 01- and YEAR(myMonth) to work. Let me explain:

=DATEVALUE("January 2020")

Will return Integer 43831, or in other words: 1-1-2020. Then secondly, EOMONTH will return the end of that same month as an Integer, whereas DAY will return the number of that day. So your formula would read:

=DAY(EOMONTH(DATEVALUE("January 2020"),0))

Now to write this in VBA:

Dim stringAppoggio As String
Dim myMonth As String

myMonth = "January 2020"
stringAppoggio = "=DAY(EOMONTH(DATEVALUE(""" & myMonth & """),0))"

You can check that it works:

Debug.Print Evaluate("=DAY(EOMONTH(DATEVALUE(""" & myMonth & """),0))")

Note: See the triple quotes? That's because we need to feed DATEVALUE a string within quotes to work, otherwise it wouldn't be a string and will return an error

Upvotes: 3

Related Questions