VBA 最终用户将 Microsoft Office 2007 升级到 2010（VBA 6 到 VBA7）问题以及需要文档、帮助、提示。（足够的关键字）

VBA End-user upgrading Microsoft Office 2007 to 2010 (VBA 6 to VBA7) problems and need for documentation, help, tips. (Enough with keywords)

提问by Mika H?m?l?inen

As typical small-business user, when my laptop broke I HAD to upgrade to Office 2010 (2007 isn't on market anymore). Now I have a bunch of quick-and-dirty VBA to go with my old worksheets. None of them were programming masterpieces, but those worked and got work done with excel, and office 2007.

作为典型的小型企业用户，当我的笔记本电脑坏了时，我不得不升级到 Office 2010（2007 不再上市）。现在我有一堆又快又脏的 VBA 来处理我的旧工作表。它们都不是编程杰作，但那些使用 excel 和 office 2007 工作并完成工作。

Now I tried to use my old stuff on Office 2010, and everything is just little bit off to be usable. For two days I searched web, trying to find any documentation for the end user, no luck. I found a lot of blogs aimed to professional programmers who are hoping to make bullet proof code to serve anything from Office 0 to Office2010, 32 bits to 64 bits (No 8 bits here?), but none of the articles talked about the main business, The End User with whole lot of homemade coding to survive a day.

现在我尝试在 Office 2010 上使用我的旧东西，但一切都变得可用了。我在网上搜索了两天，试图为最终用户找到任何文档，但没有运气。我发现了很多针对专业程序员的博客，他们希望制作防弹代码以服务于从 Office 0 到 Office2010、32 位到 64 位（这里没有 8 位？）的任何内容，但没有一篇文章讨论主要业务, 拥有大量自制编码的最终用户可以度过一天。

How do I make my old 2007 VBA to work with 2010?

如何让我的旧 2007 VBA 与 2010 一起使用？

No, I don't need it to backwards compatible with anything, it is enough that it works with my new computer, and new software (In my case it is 64 bits, if anyone wonders). I just need to know why my code doesn't work anymore, and what to do about it. What I really want is some pointers to real information, if there is any! Of course I could copy-paste every piece of my code to programmers discussion forums, get laughed at, and after some weeks I might get some of them to work, but what I really need is real information written to basic VBA user. I need to learn this.

不，我不需要它向后兼容任何东西，它足以与我的新计算机和新软件配合使用（在我的情况下它是 64 位，如果有人想知道的话）。我只需要知道为什么我的代码不再起作用，以及如何处理它。我真正想要的是一些指向真实信息的指针，如果有的话！当然，我可以将我的每一段代码复制粘贴到程序员讨论论坛，被嘲笑，几周后我可能会让其中的一些工作，但我真正需要的是写给基本 VBA 用户的真实信息。我需要学习这个。

So What Is Your Problem?

那么你的问题是什么？

What has changed?

发生了什么变化？

Calendar active-X component was missing. I found solution: http://answers.microsoft.com/en-us/office/forum/officeversion_other-customize/missing-calendar-control/03ad5d05-ca3f-4081-9989-e757223ebddenow I just have to redo every calendar on all my forms… Thanks.

缺少日历 active-X 组件。我找到了解决方案：http: //answers.microsoft.com/en-us/office/forum/officeversion_other-customize/missing-calendar-control/03ad5d05-ca3f-4081-9989-e757223ebdde现在我只需要重做每个日历我所有的表格……谢谢。

Textbox.Text wasn't working, I found and run Microsoft Excel Code Compatibility Inspector (CII), and it showed that I have few thousand of those Textbox.Text elements that are “Deprecated” – “Potentially contains removed items the object model” – What?

Textbox.Text 不起作用，我找到并运行了 Microsoft Excel 代码兼容性检查器 (CII)，它显示我有几千个“已弃用”的 Textbox.Text 元素——“可能包含对象模型中已删除的项目” - 什么？

It keeps getting better, same Microsoft software stated: “TYPE: DEPRECATION ITEM: [xls]SmartTagRecognizer.FullName URL: http://go.microsoft.com/?linkid=9719761CODE: MyFullName = ThisWorkbook.FullName”” No explanation… (Btw. only way to stop the Inspector and see the results was CRT-ALT-DEL –Stop…)

它变得越来越好，同样的微软软件声明：“类型：弃用项目：[xls]SmartTagRecognizer.FullName URL：http: //go.microsoft.com/?linkid=9719761 CODE：MyFullName = ThisWorkbook.FullName”“没有解释...... （顺便说一句。停止 Inspector 并查看结果的唯一方法是 CRT-ALT-DEL –Stop...）

I visited the links given by the Inspector, no help there.

我访问了检查员提供的链接，没有任何帮助。

I run the Inspector few times, and It gave different results every time. Now that is interesting.

我运行 Inspector 几次，每次都给出不同的结果。现在这很有趣。

I hear your problem, now show me your problem

我听到了你的问题，现在告诉我你的问题

Where do I start. Here is one. I have a form where user can make a new ‘appointment', when saving, VBA creates piece of code and new Shape into calendar worksheet (and to Outlook, but that is not the point here). When user comes back and clicks that shape – button – Button runs a piece of code that was created. here is the code:

我从哪里开始。这是一个。我有一个表单，用户可以在其中进行新的“约会”，保存时，VBA 创建一段代码和新的形状到日历工作表（和 Outlook，但这不是这里的重点）。当用户返回并单击该形状 – 按钮 – 按钮运行一段创建的代码。这是代码：

Private Sub myMacro2001_Click()
    Dim meetingId As Integer
    meetingId = 2001
    Load formHours
    Call formHours.selectMeeting(meetingId)
    formHours.Show vbModeless
End Sub

Which is supposed, and did back in 2007, open up form formHours with information stored for that meeting 2001 (meetingId)

这应该是，并且在 2007 年做了，打开表单 formHours，其中包含为该会议 2001 存储的信息 (meetingId)

And the code in formHours starts as:

formHours 中的代码如下：

Sub selectMeeting (ByRef IdNo As Integer) 'Bring in the meeting ID
Meeting = IdNo
    ….

Not very elegant, but it worked. Doesn't do that anymore.

不是很优雅，但它奏效了。不再这样做了。

If you can help me with this case, I appreciate, But it would be even better if you inform me where was I supposed to find the answer. What has changed? Worked before, doesn't anymore. What to do?

如果你能帮助我解决这个问题，我很感激，但如果你告诉我我应该在哪里找到答案会更好。发生了什么变化？以前工作过，现在不行了。该怎么办？

(PS. I know my code is crappy, but it worked… And I just would like to find out what has changed, and how do I need to change.

（PS。我知道我的代码很糟糕，但它有效......我只想找出发生了什么变化，以及我需要如何改变。

Ps.Ps Yes I know I should be asking Microsoft, but you know how that is…)

Ps.Ps 是的，我知道我应该问微软，但你知道那是怎么回事……）

FOLLOWUP

跟进

After some kind advice from Barrowc and others I managed to fix some of problems. Changing Text.Text to Text.Value created bunch of new problems, some of Text-Values were used on formulas, and now I needed to change them Val(FooTextBox.Value)

在 Barrowc 和其他人的一些善意建议之后，我设法解决了一些问题。将 Text.Text 更改为 Text.Value 产生了一系列新问题，一些文本值用于公式，现在我需要更改它们 Val(FooTextBox.Value)

What is really surprising is that 2010 seems very SLOW! I ran 2007 and 2010 side by side at office, and 2007 won hands down. One of my workers had already invoiced a customer using 2007, when 2010 was still opening up! Funny, since 2007 was running on AMD Athlon X2 Dual-Core with limited memory, and 2010 was on my new laptop with Core i7-740QM, 6 GB, both on win7-64. I surfed the net and didn't find any complaints that VBA7 on Office 2010 is so much slower than VBA6 on Office2007. I don't know if this is just my problem, but my employees voted for 2007 single minded...

真正令人惊讶的是，2010 年似乎很慢！我在办公室并肩跑了 2007 年和 2010 年，2007 年赢了。我的一位员工已经使用 2007 向客户开具发票，而 2010 年仍然开放！有趣的是，自从 2007 年运行在内存有限的 AMD Athlon X2 双核上，而 2010 年运行在我的带有 Core i7-740QM、6 GB、win7-64 的新笔记本电脑上。我在网上冲浪，并没有发现任何关于 Office 2010 上的 VBA7 比 Office2007 上的 VBA6 慢得多的抱怨。我不知道这是否只是我的问题，但我的员工投票支持 2007 一心一意......

采纳答案by barrowc

For all of your TextBoxinstances change .Textto .Value

对于您的所有TextBox实例更改.Text为.Value

The SmartTagRecognizerissue would appear to be a false positive so no action needed there

该SmartTagRecognizer问题似乎是误报，因此无需采取任何措施

---

---

On the error with the form, you could try creating the form this way to see if it helps:

对于表单错误，您可以尝试以这种方式创建表单以查看是否有帮助：

Private Sub myMacro2001_Click()

Dim meetingId As Integer
Dim fmHours As formHours

meetingId = 2001
Set fmHours = New formHours

fmhours.selectMeeting meetingId
fmHours.Show vbModeless

End Sub

Also, you could change Sub selectMeetingto Public Sub selectMeeting

另外，您可以更改Sub selectMeeting为Public Sub selectMeeting

---

---

Documentation on the actual differences in VBA between Office 2007 and Office 2010 seems to be a bit sparse. This pagespecifically mentions that 32-bit ActiveX controls (both custom and built-in) will not workin the 64-bit version of Office 2010. Therefore, this may well be the root of your problem.

关于 Office 2007 和 Office 2010 之间 VBA 实际差异的文档似乎有点稀少。此页面特别提到 32 位 ActiveX 控件（自定义和内置）在 64 位版本的 Office 2010 中不起作用。因此，这很可能是您问题的根源。

There is also a quote from Microsoft here:

还有微软的报价在这里：

Will a 64-bit version of the Office 2010 product be available?

Yes, 64-bit Office 2010 product upgrades will be available. However we strongly recommend most users install 32-bit version of Office 2010 on both 32 and 64-bit Operating Systems because currently many common add-ins for Office will not function in the 64-bit edition.The 64-bit installation of Microsoft Office 2010 products will be available for users who commonly use very large documents or data set and need Excel 2010 programs to access greater than 2GB of memory. There may be technical issues with the 64-bit version and in order to install a 64-bit version of Office 2010 product users must have a 64-bit supported operating system on their PC.

是否会提供 64 位版本的 Office 2010 产品？

是的，将提供 64 位 Office 2010 产品升级。但是，我们强烈建议大多数用户在 32 位和 64 位操作系统上安装 32 位版本的 Office 2010，因为目前许多常见的 Office 加载项在 64 位版本中无法运行。Microsoft Office 2010 产品的 64 位安装将适用于通常使用非常大的文档或数据集并需要 Excel 2010 程序访问大于 2GB 内存的用户。64 位版本可能存在技术问题，为了安装 64 位版本的 Office 2010 产品，用户必须在其 PC 上安装支持 64 位的操作系统。

(my emphasis)

（我的重点）

If you can, try installing to the 32-bit version of Office 2010 instead

如果可以，请尝试安装到 32 位版本的 Office 2010

回答by Askjerry

I am having issues too... for decades I've used this to create a string of "=" characters for a visual break in text etc...

我也有问题......几十年来我一直用它来创建一串“=”字符，用于文本等的视觉中断......

STRING$(80,"=")

Now people are telling me that the code fails in Excel 2010.

现在人们告诉我该代码在 Excel 2010 中失败了。

What's the replacement for the above? I mean... I could do this...

上面的替代品是什么？我的意思是...我可以这样做...

For X = 1 to 80
print "=";
next x
print

but that's a real step backwards.

但这是真正的倒退。

There is one potential fix... that is to change it to reference VBA like this...

有一个潜在的解决方法......那就是将其更改为像这样引用VBA......

VBA.STRING$(80,"=")

It appeared to still work on 2007.

它似乎在 2007 年仍然有效。

回答by Nigel Heffernan

Picking up on this point from @barrowc:

从@barrowc 那里了解到这一点：

This page specifically mentions that 32-bit ActiveX controls (both custom and built-in) will not work in the 64-bit version of Office 2010.

此页面特别提到 32 位 ActiveX 控件（自定义和内置）在 64 位版本的 Office 2010 中不起作用。

I have a partial solution to that - replacing ActiveX buttons with shapes embedding images - and some of the answers you need for that may be in an answer I gave about faking 'button click' behaviour

我有一个部分解决方案 - 用形状嵌入图像替换 ActiveX 按钮 - 你需要的一些答案可能在我给出的关于伪造“按钮点击”行为的答案中

This is a lot of work, and you're regressing from event-driven code to less-capable objects and an outdated 'assign macro' design. But you can at least maintain the visual usability of your worksheet design.

这是一项大量工作，您正在从事件驱动代码回归到功能较弱的对象和过时的“分配宏”设计。但是您至少可以保持工作表设计的视觉可用性。