Protecting parts of VBA code?

  • Thread starter Thread starter Ian
  • Start date Start date
I

Ian

This is a long shot, but I'll ask anyway.

I am developing a Word template for a user who knows nothing about VBA.
The template contains user-specific data structures, implemented as
simple one or two-dimensional string arrays. Nothing complicated.

The user is happy to edit the arrays if required, but is very
uncomfortable about doing this in situ in the VBA code, in case he
breaks something.

Is there any simple way of protecting parts of the VBA code, so that the
user can not change anything except the arrays (without giving a
password, for example)?

Failing that, the only other idea I have come up with is to put the
array data in a separate file, editable by the user, and then read that
file at runtime. But this means we now have two files (the template file
and the data file), which is not very elegant.

Other ideas anyone?
 
Ian

Snap off his typing fingers!

Best response for this would be to post it to one of the Word VBA newsgroups
rather than NewUsers.

--
Terry Farrell - Word MVP
http://www.mvps.org/word/

This is a long shot, but I'll ask anyway.

I am developing a Word template for a user who knows nothing about VBA.
The template contains user-specific data structures, implemented as
simple one or two-dimensional string arrays. Nothing complicated.

The user is happy to edit the arrays if required, but is very
uncomfortable about doing this in situ in the VBA code, in case he
breaks something.

Is there any simple way of protecting parts of the VBA code, so that the
user can not change anything except the arrays (without giving a
password, for example)?

Failing that, the only other idea I have come up with is to put the
array data in a separate file, editable by the user, and then read that
file at runtime. But this means we now have two files (the template file
and the data file), which is not very elegant.

Other ideas anyone?
 
TF said:
Ian

Snap off his typing fingers!
Click to expand...

Funny that. That was my thought as well ...
Best response for this would be to post it to one of the Word VBA newsgroups
rather than NewUsers.
Click to expand...
I am a new user! I've only been using Word since version 2 (in about
1987), and I'm still learning. But I take your point.

Merry Christmas to one and all, and special thanks to the experts here
who have made my life much easier.
 
Ian

I wasn't implying that you are or are not a new user - but trying to say
that the VBA gurus hang out in the VBA newsgroups, so you will have a better
chance of a good response posting there. They don't tend to visit the new
users group very often because new users are not usually into VBA.

BTW, perhaps snapping off fingers may be a bit OTT even if it a satisfying
thought!

Merry Christmas.

Terry


TF said:
Ian

Snap off his typing fingers!
Click to expand...

Funny that. That was my thought as well ...
Best response for this would be to post it to one of the Word VBA newsgroups
rather than NewUsers.
Click to expand...
I am a new user! I've only been using Word since version 2 (in about
1987), and I'm still learning. But I take your point.

Merry Christmas to one and all, and special thanks to the experts here
who have made my life much easier.
 
Hi Ian,

If you tell us what is the purpose of the data structures, we may be able to
give better (less drastic) advice.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP
 
In message <[email protected]>, Doug Robbins - Word
MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS
Hi Ian,

If you tell us what is the purpose of the data structures, we may be able to
give better (less drastic) advice.
Click to expand...

Doug, the data structures contain the names of the client's
organizational groups, departments, etc. For example:

department(1) = "Sales"
department(2) = "R&D"

As simple as that. Basically, the client just wants the ability to edit
these strings himself in the future, without having to call in an
expensive consultant like me, and without the fear of breaking the
template if he makes an editing mistake.

I've scoured the VBA reference books I have here, but can't find
anything that describes protecting parts of VBA code. I suspect in the
end I will have to spend more time educating the client on how to edit
the template without breaking it, rather than building in a complicated
code protection scheme.
 
You still haven't told us very much about how the data is used. But, I
would store it in a table in a separate document and have some code in an
autonew macro in the template open that document and read the information
into whereever it is that you want it to be.

For half you fee I will do it for you <g>

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP
 
Hi Ian,
I've scoured the VBA reference books I have here, but can't find
anything that describes protecting parts of VBA code.
Click to expand...
You can't protect part of any particular project, it's an
all-or-nothing proposition. But you CAN "pull things apart". Here
are a few approaches that come immediately to mind:

1. put the "lists" into functions the user's Normal.dot template.
Since every project automatically has a reference to Normal.dot,
you can call any function or procedure it contains from your
protected project: Departments() = Normal.CreateDepartmentsList

2. Rather than hard-coding the lists in your code, maintain them in
separate files, such as Excel, a Word document or table, or a text
file. Assuming we're discussing Word 2000 or later, there could for
example be a text file:
"Sales";"R&D"

Using the FileSystemObject and TextStream you could get this
information, then use the VB Split function to put it into your
array.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30
2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail :-)
 
Hi, Ian,

Besides the suggestions from Doug and Cindy, there's another
possibility. It involves more up-front work on your part, but will
probably satisfy the customer better.

For each piece of information in the data structures, create a
document variable in the template (see the Help topics "Variables
Collection Object" and "Variable Object" and their methods and
properties). The advantage of document variables is that they're
unchangeable except through VBA.

Adapt your macros to get their values from the document variables
instead of the data structures; or make AutoNew and AutoOpen macros
that load the values from the variables into the structures.

Then add a userform to the template, where the current values of the
document variables are displayed in textbox controls and can be
edited. The code behind the OK button can validate the values (e.g.,
make sure none are blank, words are capitalized, numbers are within
range) and write the new values back to the document variables. Add a
button to a toolbar to call a macro that shows the userform.
 
Ian

After advising you to post to the VBA groups to elicit a better response,
along comes three VBA experts! Rather like London busses - don't see one for
hours then three come along together!

Terry Farrell


In message <[email protected]>, Doug Robbins - Word
MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS
Hi Ian,

If you tell us what is the purpose of the data structures, we may be able to
give better (less drastic) advice.
Click to expand...

Doug, the data structures contain the names of the client's
organizational groups, departments, etc. For example:

department(1) = "Sales"
department(2) = "R&D"

As simple as that. Basically, the client just wants the ability to edit
these strings himself in the future, without having to call in an
expensive consultant like me, and without the fear of breaking the
template if he makes an editing mistake.

I've scoured the VBA reference books I have here, but can't find
anything that describes protecting parts of VBA code. I suspect in the
end I will have to spend more time educating the client on how to edit
the template without breaking it, rather than building in a complicated
code protection scheme.
 
TF said:
Ian

After advising you to post to the VBA groups to elicit a better response,
along comes three VBA experts! Rather like London busses - don't see one for
hours then three come along together!
Click to expand...

Yup. And they're full, so you can't get on them!

Many thanks to everyone for all the inputs. You've given me useful food
for thought.
 
Back
Top