字符串中的VBA EXCEL公式 [英] VBA EXCEL formula in a String

问题描述

我想在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

谢谢

推荐答案

根据我的评论，这里有一些错误:

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

• 您已使用单引号而不是双引号将变量与VBA公式语法分开
• 您完全忘记了第二个 myMonth 变量周围的引号
• 您创建了一个根本不起作用的公式

请记住，您的变量不仅是一个月，而是一个包含月份和年份的字符串> "2020年1月" ，因此 DATEVALUE 不需要 01-和 YEAR(myMonth)正常工作.让我解释一下:

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")

将返回 Integer 43831，或换句话说: 1-1-2020 .然后，第二， EOMONTH 将以 Integer 返回该月的月底，而 DAY 将返回该天的数字.因此您的公式应为:

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))

现在要在VBA中编写此代码:

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))")

_{注意:看到三重引号了吗?那是因为我们需要在引号内输入 DATEVALUE 一个字符串才能起作用，否则它将不是字符串，并且会返回错误}

_{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}

这篇关于字符串中的VBA EXCEL公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！