当前位置: 动力学知识库 > 问答 > 编程问答 >

VBA Excel password to lock macro from editing but still allow users to see the code

问题描述:

I cannot find any information if it is possible to allow users to see the macro code, but at the same time, make them unable to change the VBA code. I need to lock it for editing only.

I have an excel file which is used by many users. I would like them to see the code to learn, but at the same time I would like to secure and protect the code from any changes.

Is that possible?

网友答案:

I would like them to see the code to learn, but at the same time I would like to secure and protect the code from any changes.

This is an X-Y problem: what you need is not to "lock modules for edits", what you need is a way to "revert" any changes that might have been done inadvertently by inexperimented viewers.

The only built-in, legit and 100% safe way to do what you want to do is to print out the code and hand out hard copies for study.

The solution you actually need exists, it's called source control.

Without source control, the "truth" resides inside the document host; whatever code exists in that document is the single version of the truth, and any changes made to it become the new truth. One way to circumvent that is to use the file system: make versioned copies of the macros you distribute, so that if some user changes something and breaks it, you can always re-send the original version you had given them.

With source control, the "truth" resides outside the document host, in a repository. The source control system is responsible for keeping track of all changes, when they're made, and by whom. The "truth" becomes a tree, and you can create branches and start implementing a new feature without affecting the "trunk" until you merge your changes.

Unfortunately, the VBE isn't very source control friendly: for source control to work well, you need it to track each individual source code file/module separately - and VBA code is, by definition, embedded in a host document, which can itself contain things that refer to VBA code (e.g. a shape/button that's assigned to a macro), and the VBA code can refer to things in the host document (e.g. code that refers to a specific Worksheet object), so while there are plenty of more or less simple solutions to export the code from the project into the file system, and import it back... I don't know of any solution that covers every single use case.

I have an open-source VBE add-in project going on (see Rubberduck on GitHub) that includes an IDE-integrated Git source control panel:

There are a number of issues with it - for example it's completely host-agnostic, so there is no support [yet] for anything that's host-specific (i.e. Rubberduck does not use the host application's object model for this). But the project is open-source, and extremely active, so all bugs will eventually be squashed :)

Notice the included changes section in the above screenshot:

The "undo" source control command literally reverts any changes made to a file since it was pulled from the repository: with source control you no longer have to worry about local changes breaking your single version of the truth. And even if "bad" changes get committed to the repository, you can always revert them, and "go back" to any previous commit.

You can control who can and cannot push changes to the repository too, so you don't have to worry about one user pushing "bad" changes and breaking your project. And their local changes can be reverted quite easily.

Now, whether introducing inexperimented would-be-coders to source control concepts is a good idea, is another debate... but IMO it can't hurt to be exposed to things like source control and unit testing from the start - especially if VBA is to be an entryway into the bigger, wider and wilder world of programming.

网友答案:

This is not possible by any conventional means. Some crazy solution maybe possible by subclassing (synonym for intercepting) the windows message queue and cancelling any keyboard strokes but that is way way too complicated.

Sorry, it is not like VB.NET of C#.Net where perhaps the modules are source controlled and if not checked out read-only.

You could create a second workbook on the fly and copy the modules there so you won;t have to worry about others changing the code.

网友答案:

As the other answerers have pointed out, this simply isn't possible without taking some extraordinary action like installing a plugin that monitors all keystrokes, or some other heavy handed solution. If all you want to do is allow users to view the code, show them the code. You don't have to do it with the VBE at all:

Public Sub DisplayWorkbookCode(wb As Workbook)
    Dim i As Long
    With wb.VBProject.VBComponents
        For i = 1 To .Count
            With .Item(i).CodeModule
                If .CountOfLines > 0 Then
                    MsgBox .Lines(1, .CountOfLines), , .Name
                End If
            End With
        Next
    End With
End Sub

Sample Usage:

DisplayWorkbookCode ThisWorkbook

Note that the sample above just uses a message box to display the code for each module - if there's a ton of code you might want to build a custom UserForm to display it or something like that.

网友答案:

There is, first:

And then you'll see this (tick/untick the box as needed):

分享给朋友:
您可能感兴趣的文章:
随机阅读: