References problem in Excel 1

[N1GHTEYES]

I wonder if anybody can help me solve this puzzle.

I have a colleague who uses an add-in of mine, and he gets a problem with references. When he runs a macro (via a toolbutton) from the add-in, which calls some code that includes the "Chr()" function, he gets an error message ("Can't find function or library"). If I go through his references and check the "Microsoft Visual Basic for Application Extensibility" reference, it then runs OK without an error.

When he closes Excel and re-opens it, he again has the problem - i.e. when I look at his references again, I find that it is unchecked again. So his system is not retaining the selected state. As far as I can tell however, my system does retain reference selections. I have no idea what the difference between his setup and mine might be (especially as we nominally have systems with common images provided by the same IT group.

Furthermore, when I look at my references, I find that I do not need to have "Microsoft Visual Basic for Application Extensibility" for "Chr" to work.

So, is "Chr" part of the MSVBA ext library or not? If it is, how come my system does not complain. If not, why does his?

Secondly, how do I get his system to retain selected references?

Finally, is there any way the add-in can check the state of the refs on startup and make sure that any necessary ones are checked?

I would really appreciate any help or suggestions on this one. If it were a coding problem per se, I'd at least know where to start, but I'm groping in the dark in this case.

Thanks,

Tony

 

[combo]

In this case references are missing in the add-in. References are stored in the VBA project of the workbook, so add a reference to the VBIDE to your source workbook and re-create the add-in.
This kind of error message is reported also when calling available functions.


combo

 

[N1GHTEYES]

Thanks combo.

Could you please help me through this just a bit?

I think the steps you are suggesting are:

1 In the IDE: select the addin, Microsoft Excel Objects, Thisworkbook.

2 In menu tools, references, check the VBIDE ref (I'm not sure how this is refered to - are you talking about the MS VBA extensibility item?)

3 Save the addin.

Is that what you are suggesting?

Will that ensure that when this colleague opens Excel with my addin installed, it will automatically ensure that all the refs required by the addin are checked?

Have you any idea why MY refs do NOT show the above ref to be checked, but it runs the Chr function quite happily?

Thanks again. I appreciate the help.

Tony

 

[combo]

Yes, save (3) the add-in in IDE. The reference name to VBIDE: Microsoft Visual Basic for Applications Extensibility 5.3.
In case of problems either take the source workbook or convert back the add-in to a workbook (in VBE IDE select the add-in, unlock if password-protected, select ThisWorkbook object, and set the IsAddIn to False) and save. If you convert the add-in to regular workbook check the extension when saving it.

Do you have the same excel versions?


combo

 

[N1GHTEYES]

Thanks for your help combo.

To answer your last question first - yes, we have the same versions. We all also reference the self-same addin. I keep a copy of the latest version of the addin in a common location on a networked drive we can all see - and I instruct all of my users to refer to that exact file when installing their addin (i.e. they do NOT select the option to "copy to local folder" during the installation process).

What puzzled me was that neither of us had the VBIDE ref checked, but mine did not throw an error when it ran the Chr() function and Nick's (my colleague) did. We DID both have the Visual Basic for Applications reference checked, but not the extensibility one. When I checked the VBIDE ref in his <tools, refs> his then ran OK. How come mine does not need it and his does? Odd.

As far as instruction 3 is concerned. Are you saying I should save the addin as a workbook, but call the extension .xla? Or save it as a workbook, then re-check the addin = true and save it again as an addin? I guess I can see how that might help, but I'm not really sure I understand it. Or are you saying I should save the workbook as a workbook, but call THAT as an addin? If so, I did not know you could do that.

In case it is not clear from my previous posts, the addin is used by several colleagues and myself. Most of the time, most of us have no problems. Nick's set-up for some reason does seem to have more than it's fair share, but, as I say, we nominally all have the same setup with the same permissions etc.

Tony

 

[combo]

In case of missing references, vba can point to missing references for random vba functions used (as Left, Mid, Chr etc.), this has nothing to do with real missing of VBA library - it's not possible to remove it.

? The VBIDE library extends office application, for instance in excel object model VBProject is a part of Excel library, however its details are not here. In my case (excel 2003) I can see hints and select items after 'ThisWorkbook.VBProject.' without referencing VBIDE. No problem with displaying help for VBComponents.

If you have source workbook - just save it as an add-in.
The conversion in the VB IDE by changing the IsAddIn property is useful when there is no source workbook. This kind of conversion does not change extension so my remark to trace it.

combo

 

[N1GHTEYES]

OK, as I understand it, the situation is:

1 VBIDE library (Microsoft Visual Basic for applications extensibility 5.3) should not be needed to use functions such as Chr etc. However, if Nick has problems when it is not referenced, then reference it.

2 To do so, I'll open the add-in and check the reference and then save the addin (as an addin).


I will do that tomorrow when I can ensure that nobody is using the addin (I need to make sure none of the users are using the addin at the time otherwise I know from experience that I won't be allowed to save it).

I'll let you know how it goes.

Thanks again combo,

Tony

 

[Gavona]

Nighteyes: I think that you can make your add-in read only (from windows explorer file properties). Then other users won't lock it.

Gavin

 

[N1GHTEYES]

Gavona,

Thanks for the suggestion. It is obvious, but it had not ocurred to me. Doh!

I think the same problem might exist though: to save any changes I make, I'll need to make it NOT read-only first, BUT, if others are referencing it, I'm not sure if I'll be able to change the properties.

I'll give it a go & see what happens.

Tony

 

[combo]

Tell users to uninstall the add-in for a while, their excel will close it.

combo

 

[N1GHTEYES]

Actually, what I do is phone those who have Excel open if they have AL_XL_Tools installed, and ask them to quit excel for a few minutes. How do I know they fall into this category? The answer is that I added a function into AL_XL_Tools which mods a log file whenever they start or stop Excel.

So all I need to do is look at the log file, and see who has started and not yet stopped Excel. This targetted approach tends to be more effective than a global email to all AL_XL_Tools users. However, the problem occurs if they have started Excel then left it running when they go home! Or even if they are just away from their phone for a bit.

Eventually, I always manage to find some time when nobody is using it, so there is no major problem. It is just that sometimes the enforced delay can be a bit annoying.

Tony

 

[Gavona]

Either the read only solution I posted works or I have been very lucky!

Gavin

 

[N1GHTEYES]

I've just tried it at home and it looks like you are right. I think the process needs to be:

1 Make the addin read only
2 When I want to make a change, do the coding and save the new version as something else, say AL_XL_Tools_new.xla.
3 Remove the read-only status of the AL_XL_Tools_new.xla file
4 Delete the current version of AL_XL_Tools.xla
5 Rename AL_XL_Tools_new.xla to AL_XL_Tools.xla

I just tried this, and even with Excel open, it let me do it, so I guess it works. I was surprised the system let me delete the addin while it was in use, but it looks like it does.

Thanks Gavin, have a star.

Tony