VBA reference setting not working as expected on Excel 2003 on XP Pro

[Guest]

I have found what appears to be an error in the VBA
environment running a macro under Excel 2003 on Windows
XP Professional. The same macro runs fine under Excel
2000 on Windows 2000 which I think does indicate that
this is a genuine error in the VBA environment.

I'm using the code below to check if there is a reference
to the Solver addin. If there isn't a reference, the code
adds the reference. This works fine the first time, but
gives an "already defined" error if run again. I would
expect the code to correctly determine that the reference
already exists and exit the subroutine without attempting
to define the already existing reference - and indeed
this is the case on Excel 2000 on Windows 2000 - but it
is failing on the PC running Excel 2003 and Windows XP
Professional.

Interestingly - and I think this is relevant - the
ActiveVBProject and VBProject are both shown as "VBA
Project" the first time the code is run but the
ActiveVBProject changes to "SOLVER" for subsequent calls
to the code when the error message appears.

So to produce the error on the PC running Excel 2003 and
XP Pro I uncheck the reference to Solver in the VBA
editor, quit Excel completely, start Excel by double
clicking the spreadsheet in Explorer and click the button
that calls the code. The code works fine the first time
but gives the error on subsequent calls.

Does anyone know how I go about trying to get MS to fix
this problem? I've rung local support who say that for
$50 I can speak to an MS professional who I guess woudl
verify what I'm saying and start the bug report process.
Does anyone know if this process is likely to be
fruitful? Or do MS employees monitor these newsgroups?
(And maybe I can save my $50 )

Cheers

David


Code:

Sub Button2_Click()
On Error GoTo ErrorHandler ' Enable error-handling
routine.

MsgBox Prompt:="VBProject name is " +
ThisWorkbook.VBProject.Name, Buttons:=vbCritical

MsgBox Prompt:="ActiveVBProject name is " +
Application.VBE.ActiveVBProject.Name, Buttons:=vbCritical

i = 1
Do Until ((i = AddIns.Count) Or _
(StrConv(Left(AddIns(i).Name, 6),
vbUpperCase) = "SOLVER"))
i = i + 1
Loop

If (StrConv(Left(AddIns(i).Name, 6), vbUpperCase)
= "SOLVER") Then
AddIns(i).Installed = True
j = 1
Do Until (j =
Application.VBE.ActiveVBProject.References.Count Or _
(StrConv(Left
(Application.VBE.ActiveVBProject.References(j).Name, 6),
vbUpperCase) = "SOLVER"))
j = j + 1
Loop
If (StrConv(Left
(Application.VBE.ActiveVBProject.References(j).Name, 6),
vbUpperCase) <> "SOLVER") Then

Application.VBE.ActiveVBProject.References.AddFromFile
AddIns(i).FullName
End If
Else
MsgBox Prompt:="Solver not found. This workbook
will not WORK", Buttons:=vbCritical
End If
MsgBox "finished"

Exit Sub ' Exit Sub to avoid error handler.

ErrorHandler: ' Error-handling routine.
MsgBox Prompt:="Error in Button2_Click() = " + Str
(Err.Number) + Error(Err.Number) + " Please report to
Analytical Engines", Buttons:=vbCritical

Resume Next ' Resume execution at line after error

End Sub

 

[David M.]

Does anyone know how I go about trying to get MS to fix
this problem? I've rung local support who say that for
$50 I can speak to an MS professional who I guess woudl
verify what I'm saying and start the bug report process.
Does anyone know if this process is likely to be
fruitful? Or do MS employees monitor these newsgroups?
(And maybe I can save my $50 )

I used to work in the MS support center, and I can guarantee you that the
person you get over the phone for your $50 is not going to know more about
what to do in this situation than you do. What will happen is it will get
elevated and elevated and you will probably get nowhere fast. Fortunately,
MS isn't very stingy about their pay for support fees and you could get a
refund on that, but I suggest you save yourself the time and money of going
that route.

If you can find a reference to this bug in an actual KB article that has a
hotfix, you could call the same pay for support number and get free support
in implementing the hotfix only, but I highly doubt that you'll find that
either.

You can check out KB212623 and it will tell you about half way down that
article that Office developers can now submit bug reports, but the link that
is provided is dead. A link to that article is here:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;212623

I managed to find the section of the MS site where you can submit bug
reports for Visual Basic. You could possibly submit the bug under the VB6
section and hope it gets routed accordingly, but don't expect much feedback.
The site is here:
http://support.microsoft.com/default.aspx?scid=/support/vbasic/vbbugrep.asp

When I worked at the MS support center, they implemented an email address
(e-mail address removed). This address appears to still be good from doing a
quick google search. You could also try emailing your concerns there. You'd
have more of a chance of getting feedback.

I've been doing Office development for about 2 years at my current job, and
have found a bug or two myself (Props to MS for such bug free software. One
bug a year is very little.) My suggestion is to just work around it as best
you can. Include information about it in your help documentation if needed.
Sorry that I can't provide any help on this bug, but hopefully you found
some of this info helpful.

 

[Hmmm]

Hi,

Your code is quite complex and you are probably using
features "borrowed from other applications or other
versions of excel.

To ensure code works for nearly allversions of excel, ie
97 plus, keep it simple.

example:

MsgBox "Hello",vbOkOnly + vbInformation,"Message from
Above"

I do not know where the reference to Buttons:=vbCritical
or MsgBox Prompt: comes from, maybe excel4 or Lotus.


Excel 2003 does not like that stuff. It cannot cope with
all the jargon of yesteryear. Keep it simple and it will
work. Also, you will have to put in the extensions of your
objects where yesteryear allows you not to have them.
Example: txtTextbox1.Text

Also with objects, use the instance name. Example
txtTextbox not textbox1, lblName not label1 etc

regards

Hmmm

 

[Stephen Bullen]

Hi David

This isn't a bug; you're just seeing that the ActiveVBProject is the
project in the VBIDE that was last clicked by the user, and has *no*
relationship to the workbook active selected in the Excel window. It
never has and the fact that it worked in previous versions was nothing
more than coincidence. Perhaps in Excel 97, you only had the Solver
addin installed, while in Excel 2003 you have more installed?

If you want the VBProject for a specific workbook, you should *always*
use Workbook.VBProject (or ThisWorkbook.VBProject) and not rely on the
VBE's ActiveVBProject property.

FWIW, the ActiveVBProject is very useful when writing addins for the
VBE, giving you the VB project that the user is currently working on,
which is often different to Excel's ActiveWorkbook.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie