VBA is a powerful language, but it can get you into trouble if you’re not paying attention. Macro viruses, which I never heard of five years ago, have become the bane of my existence! How can I make sure my people only run macros I know are safe?
First, purchase and use antivirus, antiworm, and anti-Trojan software. This is particularly important in Excel 97 because this version has no macro security setting. If you’re running Excel 97 and you absolutely can’t upgrade to a more recent version, make darn sure that your antivirus software is up-to-date and it’s set to actively scan all incoming files. If you have a computer you use as a gateway to the Internet, you should strongly consider putting one antivirus program on the gateway and a package from a different provider on your users’ machines; if a virus or worm slips by one of them, the other will probably swat it.
Second, if you’re running Excel 2000 or later, make sure your users have their macro security level set to High or Very High.
Third, you should strongly consider purchasing a digital certificate from a certificate authority and using it to sign the macros you want your users to be able to run. Then you can prevent your users from running macros that aren’t signed by setting Excel’s macro security level to High.
Once your antivirus software is in place, make sure that every user’s computer has Excel’s macro security level set to High (at least). To do that, ...