VBA End-user upgrading Microsoft Office 2007 to 2010 (VBA 6 to VBA7) problems and need for documentation, help, tips. (Enough with keywords)
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.
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.
How do I make my old 2007 VBA to work with 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.
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-e757223ebdde now I just have to redo every calendar on all my forms… Thanks.
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?
It keeps getting better, same Microsoft software stated: “TYPE: DEPRECATION ITEM: [xls]SmartTagRecognizer.FullName URL: http://go.microsoft.com/?linkid=9719761 CODE: MyFullName = ThisWorkbook.FullName”” No explanation… (Btw. only way to stop the Inspector and see the results was 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.
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:
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)
And the code in formHours starts as:
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 Yes I know I should be asking Microsoft, but you know how that is…)
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)
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...
Answer
For all of your TextBox instances change .Text to .Value
The SmartTagRecognizer issue would appear to be a false positive so no action needed there
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 selectMeeting to Public Sub selectMeeting
Documentation on the actual differences in VBA between Office 2007 and Office 2010 seems to be a bit sparse. 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. Therefore, this may well be the root of your problem.
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.
(my emphasis)
If you can, try installing to the 32-bit version of Office 2010 instead