0 votes
in VBA by Beginner (2 points)
I've a VBA that i'd like to host in a server. So the spreadsheet itself would have to have a code that downloads this VBA from the server and executes it locally. Is this possible?
by Skilled (419 points)

Hi lnjaine,

How extensive is the downloaded VBA? Your local spreadsheet can indeed run such a string, once you set up a process for importing or dynamically creating a module. This requires the LOCAL spreadsheet to "Trust access to the VBA project object model."

Are you familiar with the following?

Application.VBE.ActiveVBProject.VBComponents.Import

I asked how extensive is the downloaded VBA string because, there is an alternative: write a preprocessor. A pre-processor is an interpreter that takes a downloaded string, converts it into PREDEFINED macro calls and then either stores those calls to be executed later or simply executes them as soon as the string is completely converted.

The benefit to using a preprocessor is that your download string can be very simple. In addition, your local spreadsheet will not have to enable access to the VBProject object model. (This is more of a concern if you are distributing the local spreadsheet.)

The downside to a preprocessor is that you have to write all the macros, parse all the arguments and handle any errors. Plus, if you need to create new macro calls, the local spreadsheet will have to be updated.

With either approach, you're looking at a major investment in development time.

Cheers,

Mitch

 

by Beginner (2 points)
Hi Mitch,

Thanks for the answer! On my previous attempts, it was been caught as "virus threat" by Windows Defender, do you thing your solution will prevent it?

Moreover, would you be so kind to show me a very simple sample just running a "hello world", so i can understand properly and try to replicate to my own macro?

Thanks!
by Skilled (419 points)

Hi lnjaine,

A preprocessor will avoid virus threats and, the users of your local spreadsheet only have to do the "normal" things to enable content and macros. (Usually, just once, unless the spreadsheet is resaved under a different name or moved to a different folder.)

Anyway, a "Hello, World" exmple would not do justice to the concept of a preprocessor; however, here is a brief outline of what you might do to recreate "Hello, World":

1. Server provides a string. "DISPLAY,<quote>Hello<comma>World<quote>,Destination"

2. Local spreadsheet downloads string aand passes it to the preprocessor

3. Preprocessor parses the string as COMMAND, arguments

   a. COMMAND  = DISPLAY

   b. argument 1 = <quote>Hello<comma>World<quote>

   c. argument 2 = Destination

4. Preprocessor looks up COMMAND in its dictionary (or simple Case statements)

5. When Preprocessor finds DISPLAY, it calls the Display macro

   a. The preprocessor doesn't know how many arguments any command needs

   b. It is best to let each command macro grab the arguments

6. The Display Macro looks for two arguments and throws an exception if one is missing

   a. Alternatively, each macro can have defaults

   b. For example, if only one argument is available to the Display Macro, it is assumed to be the content and the default destination might be the ActiveCell.

7. The Display macro outputs the content to the valid destination

 

That's it. LOL

Of course, there is error-checking, argument stacking, nesting levels tracking (to allow for IF-THEN-ELSE and DO LOOP constructs.)

 

This is not trivial. I am very curious as to what use case requires an interpreter or a macro injection scheme? I am willing to wager that, if your project involves data manipulation, you can achieve your goals without overtaxing Excel VBA, which is slow enough as it is.

If you can share a bit of information about your project, perhaps we can suggest alternate ways to do things.

I do realize that, sometimes, you just have to write your own scripting language. I've done a couple, but they were mostly a labor of love. And, they never got completely done. I started in VB6 and, by the time it was useful, VB.net had been released and it was doing all the stuff I had to write code for! DOH!

Cheers,

Mitch

 

by Beginner (2 points)
Hi Mitch,

Thanks so much for all the thoughtfull comments, we will definately look into it. About my project: i've an e-commerce site that sells ready-to-use excel spreadsheets for SMB, and so far we didn't had any kind of license control. So, we are developing it now to increase the security of our intelectual property. We already have a small service that creates and validates the licenses and a macro on the sheet side to make that connection. We want to keep this specific macro out of the spreadsheets so it can be updated from time to time and to also make it safer.

Do you need any more info? I would gladly share the macro if you need to make any tests with it.

Thanks again!
by Skilled (419 points)

Hi lnjaine,

That sounds cool. I like that you've outsourced the license-generation. While I would be happy to look at the macro, if the connection is set to the 3rd-party standards (API?), then the only improvements I could suggest is to use Unviewable+ to keep both that macro and your spreadsheet IP from prying eyes.
 
Cheers,
 
Mitch
 

Please log in or register to answer this question.

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...