使用VBA在一个单元格中放置多个公式 [英] Using VBA to place multiple formulas in one cell

问题描述

我正在开发一个宏以在一个单元格中放置多个公式.在这种情况下，它的 3 个彭博公式合并到一个单元格中.该公式在手动复制或输入时在 Excel 中有效，但在 VBA 模块中输入时无效，它会产生错误(编译错误:预期:语句结束)并突出显示rtg_mdy_sen_unsecured_debt".我认为问题在于公式中使用了引号，但我找不到有关如何解决它的任何信息.

I'm working on a macro to place multiple formulas in one cell. In this case, its 3 Bloomberg formulas into one cell. The formula works in Excel when copied or typed manually but not when type it in the VBA module, where it creates an error (Compile error: Expected: End of statement) and highlights "rtg_mdy_sen_unsecured_debt". I believe the problem is the use of quotation marks in the formula but I can't find any information on how to work around it.

适用于 Excel 的公式(手动输入或复制到单元格时)如下:
=BDP($O4,"rtg_mdy_sen_unsecured_debt")&"/"&BDP($O4,"rtg_sp_lt_lc_issuer_credit")&"/"&BDP($O4,"rtg_fitch_sen_unsecured")

The formula that works in Excel (when input manually or copied into a cell) follows:
=BDP($O4,"rtg_mdy_sen_unsecured_debt")&"/"&BDP($O4,"rtg_sp_lt_lc_issuer_credit")&"/"&BDP($O4,"rtg_fitch_sen_unsecured")

尝试以下格式时，它在 VBA 中不起作用:

It does not work in VBA when attempting the following format:

Cells(4, 21).Value = "=BDP($O4,"rtg_mdy_sen_unsecured_debt")&"/"&BDP($O4,"rtg_sp_lt_lc_issuer_credit")&"/"&BDP($O4,"rtg_fitch_sen_unsecured")"

更简单的彭博公式工作得很好:($O4 引用包含BA Equity"的单元格.Q2 包含industry_sector")

Simpler Bloomberg formulas work just fine: ($O4 references a cell which contains "BA Equity". Q2 contains "industry_sector")

Cells(4, 17).Value = "=BDP(O4,Q2)"

理想的最终状态是 Cells(4, 21) 将显示波音来自三大评级机构的信用评级，如下所示:A2/A/A

The desired end state is that Cells(4, 21) will display Boeing's credit rating from the three major rating agencies, like this: A2/A/A

推荐答案

当创建包含 " 字符的 VBA 字符串时，你必须用另一个 "

When creating VBA strings that contain " characters you must delimit them with another "

这将适用于构成的字符串

This will work for the poseted string

Cells(4, 21).Value = _
"=BDP($O4,""rtg_mdy_sen_unsecured_debt"")&""/""&BDP($O4,""rtg_sp_lt_lc_issuer_credit"")&""/""&BDP($O4,""rtg_fitch_sen_unsecured"")"

这篇关于使用VBA在一个单元格中放置多个公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！