2

I'm using another workbook as a library of common routines (Tools - References - etc). It works just fine.

However, when the caller file loads it as a reference it also opens the library file on the background. Two questions:

  • Is it possible to make the loaded library read only?
  • Is it possible to hide the loaded library hidden from the user?
Community
  • 1
  • 1
hardish
  • 145
  • 2
  • 11
  • I thought about this but I'm unsure about this if the library will need to be refreshed rather regularly. How will the user know that an updated addin must be installed before running a macro? – hardish Jan 17 '17 at 05:12
  • How does a user know now that the library reference file needs updating? – chris neilsen Jan 17 '17 at 05:33
  • my current thinking that they are saved on a shared file system location, and can be replaced by developers as needed. hence users are not even aware that libraries have been updated under this scenario. – hardish Jan 17 '17 at 05:45
  • Same rational can be used for AddIns, however both will run into problems of being "in use" when you go to update them if a user has it open. – chris neilsen Jan 17 '17 at 05:59
  • You could build into the AddIn a feature that checks for a more recent version at a common location, and prompts the user to update, or even does the update itself – chris neilsen Jan 17 '17 at 06:01
  • @chris, this may be a compromise. And can users with minimal rights on windows install those themselves as required without having an admin doing this for them? – hardish Jan 17 '17 at 21:27
  • yes, normal users can install AddIns (unless it's specifically restricted by policy - not sure if that's even possible) – chris neilsen Jan 18 '17 at 02:47

1 Answers1

0

You can set the read-only attribute (in File Explorer's File Properties dialog) on any of the workbooks, including the referenced workbook, regardless of whether that is a normal macro-enabled workbook, or a macro-enabled add-in.

There isn't anyway to make a referenced workbook not appear in the VBE Project Explorer, but you can "protect" it with a password so that it can't easily be explored, but note that password protections are very easily defeated, so assume that determined users will be able to browse and change your code.

If you need better protections, and you'd like the reference to only appear in the references list and not in the Project Explorer, then you'll need to implement the reference as a COM Type Libarary, and install it on each machine upon which it is required. It's more effort, but it also offers much better protection.

ThunderFrame
  • 9,352
  • 2
  • 29
  • 60
  • Thanks for that. Two questions - can this (i.e. COM Type lib implementation) be done on PCs with no admin rights? Two - as libraries are regularly refreshed, will it be possible to prevent users from running an outdated library? – hardish Jan 17 '17 at 05:20
  • Any user can edit the attributes on a file, if they have permissions to modify the file - it's not determined by admin rights, it's determined by File System Access Control. If you're talking about libraries being referenced Excel workbooks, you can force the users to reference the workbook in a shared/network location (rather than deploying to their local drive), and then you only need to update the workbook in one location. – ThunderFrame Jan 17 '17 at 05:25
  • I edited my comments while you were writing... Anyways, i meant COM type libraries, not read only attributes. Thanks – hardish Jan 17 '17 at 05:28
  • depends on the permissions granted by your IT team. Typically, in corporate environments, standard users are not able to register Type Libraries without having admin rights, so upgrades need to be managed and deployed. That, unfortunately, is the trade-off: Lack of secure code in a referenced workbook that can be easily deployed; or secure code in a COM add-in, but that requires better planning around deployment. – ThunderFrame Jan 17 '17 at 05:31
  • we won't be able to do this then – hardish Jan 17 '17 at 05:32
  • VSTO solutions are supposed to fill the void, but I'm unsure about how reliable the forced up-to-date capabilities are. It may need to be logic that you build in yourself. i.e. stop the VSTO from loading/working until the user upgrades. Downside is that VSTO can't do User Defined Functions for use in worksheets, IIRC. – ThunderFrame Jan 17 '17 at 08:44