[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 880
  • Last Modified:

Using VBA and Word, how can I find out whether a subroutine exists on a given computer?

I have a routine that will decrypt or encrypt messages. It works with Word Object variables. I want to have this available for special persons (professors) working on their computers but not on computers used by students. I therefore put the decrypt routine in a subroutine on computers that should access the information, but not on other computers. How can I check whether the subroutine is on a computer before calling it? I get an error message when the decrypt routine is not on a computer.

Thanks for help. I am sure the solution is quite simple and commonplace.

j.r.a.
0
JohnRobinAllen
Asked:
JohnRobinAllen
  • 14
  • 11
  • 2
5 Solutions
 
syeager305Commented:
did you want this written in VBA, or Perl?
0
 
syeager305Commented:
I have one written that works in Excel VBA, I can modify it for word, but I want to make sure it is VBA that you want it written in.  (I don't know perl)

Sub DeleteProcedureFromModule()
On Error Resume Next
ActiveWorkbook.VBProject.References.AddFromGuid GUID:="{0002E157-0000-0000-C000-000000000046}", _
        Major:=5, Minor:=3
On Error GoTo 0

        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim StartLine As Long
        Dim NumLines As Long
        Dim ProcName As String
       
        Set VBProj = Workbooks("Book1").VBProject
        Set VBComp = VBProj.VBComponents("Module2")
        Set CodeMod = VBComp.CodeModule
   
        ProcName = "Test2"
        On Error GoTo Startline0:
        With CodeMod
        StartLine = CodeMod.ProcStartLine(ProcName, vbext_pk_Proc)


            If StartLine > 0 Then
                MsgBox (ProcName & " procedure exists.")
            Else
Startline0:
            StartLine = 0
                MsgBox (ProcName & " procedure DOESN'T exist.")
            End If
        End With
           


End Sub
0
 
JohnRobinAllenAuthor Commented:
I need the routine written in Word VBA. After writing my question, I accidentally added Perl as a zone, and then found I could not delete that zone.

I hope you can translate your routine into Word VBA, for I cannot understand the Excel code you supplied.

Thanks for such a rapid response to a question posed on a Sunday, when most persons are giving all their attention to making their spouse happy.

j.r.a.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JohnRobinAllenAuthor Commented:
I also had hoped there would be a one-line function that could return a True/False value as to whether a subroutine or function existed.

Perhaps that is too much to ask, especially on a Sunday, which is, in theory, a day of rest.

j.r.a.
0
 
syeager305Commented:
Okay, without knowing all the details, I had to make some assupmtions.  I just assumed a module, "Module2", this can replaced with whatever module the procedure exists in.  I also assumed that the Visual Basic Object VBE object library would be turned off on most computers, so I turn it on in the beginning of the Sub.  Also, I added message boxes-- these can be deleted. I only needed them to determine that the code was working.  Lastly, the "Test" name needs to be replaced with the Procedure you're looking for.

Regards,


Sub ProcedureExists()
On Error Resume Next

ActiveDocument.VBProject.References.AddFromGuid GUID:="{0002E157-0000-0000-C000-000000000046}", _
        Major:=5, Minor:=3
On Error GoTo 0

        Set VBProj = ActiveDocument.VBProject
        Set VBComp = VBProj.VBComponents("Module2")
        Set CodeMod = VBComp.CodeModule
       
        ProcName = "Test"
        On Error GoTo Startline0:
        With CodeMod
        StartLine = CodeMod.ProcStartLine(ProcName, vbext_pk_Proc)

            If StartLine > 0 Then
                MsgBox (ProcName & " procedure exists.")
            Else
Startline0:
            StartLine = 0
                MsgBox (ProcName & " procedure DOESN'T exist.")
            End If
        End With
           


End Sub



0
 
Adam314Commented:
You can have the zone changed by asking a moderator or zone advisor to change it.  To do this, click the "Help" link at the top-right of the page, then post a question asking that this question be moved.

You may already have your solution, but if not, you will likely get better support by having the question in the correct zone.
0
 
JohnRobinAllenAuthor Commented:
Couple of things. First  Adam314's suggestion on how to remove a question from a certain zone: I went to the help page and nowhere on that page could I see where to post a question. There must be someplace to click in the Help page. I could repost the same question under "Ask a question" but that would not involve going to the Help page, so it seemed unlikely that that was what Adam314 wanted.

Second, the code posted by syeager would not work for me. I suspect the code is not written in VBA.
The line

Set VBProj = ActiveDocument.VBProject

elicits the message "Compile error: variable not defined." If I comment out that line the next line will not work either, for it relies on the line above being active.

I appreciate the work that syeager did to help solve the problem, but my VBA editor in Word 2007 could not handle it. Am I supposed first to add some Add-In module to make it work? Could the code supplied not be in VBA?

     j.r.a.


0
 
Adam314Commented:
To have a question moved:
Click the Help link at the top-right
In the "community support" section, click the "Community Support" link
Click the "Ask a Question" link.
In this new question, state that you would like this question (by question number) moved to the "Word" and "Visual Basic" zones (and any others you think would be appropriate).



For your question, someone else will likely be able to help you much better.  But, if not, here are some ideas:
For the computers that have the subroutine, you could also have some particular file or registry key.  Then, have your VBA code look for that file/registry key to determine if the subroutine exists.  Then, to prevent compiler errors, have the actual call to the subroutine in another subroutine.  See the code.
' The subroutine ThisMightNotExist is the subroutine that might not exist.
' The Call_ThisMightNotExist would always exist, and the DoSomething is the sub
' that would be calling this.
' The Call_ThisMightNotExist is only called if ThisMightNotExist exists, determined
' by looking at a file or registry key.

Sub DoSomething
    If FileOrRegistryKeyIsPresent Then
        Call_ThisMightNotExist
    End If
End Sub
Sub Call_ThisMightNotExist()
    ThisMightNotExist
End Sub

Open in new window

0
 
syeager305Commented:
It was supplied in VBA. :) You will need to allow reference to the VBA modules with your code, which can be turned on in the VBA module inside word. Aslo make sure these references are added in the VB editor... tools- references
I'm sorry, but it's working for me, but I will keep trying...
Trust-settings.png
references.png
0
 
JohnRobinAllenAuthor Commented:
I appreciate syeager315's extra help, particularly the graphics. It was hard to see the last item checked in the references graphics, but I guessed that it was
     Microsoft Visual Basic for Applications Extensibility 5.3
My references contain that plus four of the remaining five references shown in syeagear's picture. The one I do not have is "Normal", and I cannot find that on my references menu, which shows
    Node Manager 1.0 Type Library
    Not Public Internal interface used by Windows Media Player
I presume that "Normal" would fall between those two entries.

I also have

Needless to say, with my present configuration,

Absence-of-Normal-reference.png
0
 
JohnRobinAllenAuthor Commented:
Apologies for the previous, incomplete message. As soon as I tried to add an image, somehow the message got sent before I had finished it.

I appreciate syeager315's extra help, particularly the graphics. It was hard to see the last item checked in the references graphics, but I guessed that it was
     
    Microsoft Visual Basic for Applications Extensibility 5.3

My references contain that plus four of the remaining five references shown in syeagear's picture.

The one I do not have is "Normal", and I cannot find that on my references menu

I also have the Trust Access to the VBA Project object module checked, as shown in syeager's first image.

Needless to say, with that configuration, the code refuses to run, probably because I do not have Normal in my references or because I have added an incorrect reference to the VB for [Applause.] Extensibility 5.3.


Reference-choices.png
Absence-of-Normal-reference.png
Compile-error-msg.png
0
 
syeager305Commented:
Are you using a PC or Mac?
2003, or 2007?...
I think it could be the "Normal" reference, because I have now tried it on 2 different computers running two different OS, both of which have that library. (Vista and 7) I'm trying to find a place to download that library.
0
 
JohnRobinAllenAuthor Commented:
I am running a PC with Windows XP and Word 2007 with a Word 2003 document open in the compatibility mode. You are probably right about the Normal mode, for that appears to be the only place where our computers differ.

I am sorry to give you so much  trouble with what I had thought was a simple question. It seems that every day, with the help of Experts Exchange and persons like yourself, I am learning more and more about programming. I am certainly not a programmer. My day job is teaching medieval French literature, but I certainly wish I knew as much about programming as you and others on the EE do.
         j.r.a.
0
 
JohnRobinAllenAuthor Commented:
I realize that syeager305 is running Vista and Windows 7. I have the latter but have not yet installed it. When I do, would the code still work with Windows XP and someone using Word 2003 or, worse, Word 2000?

My goal is to write a program for professors who do not have anythng exotic. Just Word and XP.
        j.r.a.
0
 
syeager305Commented:
Are you using "option explicit"?
0
 
syeager305Commented:
If so, just dim the variables
Option explicit requires all variables to be declared..
add this code...
The "Normal" object library has nothing to do with this. :)

Dim vbProj, VBComp, CodeMod
0
 
JohnRobinAllenAuthor Commented:
Progress! I use Option Explicit as a spell check in case I mistype a variable name. I did as you suggested, and also dimmed StartLine and ProcName and with those additions, the code runs, sort of.

     I want to put an encryption procedure in the "Montaigne" module under Normal (see the Project-explorer.png image). A profesor will have access to it and can decrypt private information, i.e., the name of the student who wrote the essay. Other students looking at the same essay will not have access to that module and so will not be able to run the procedure that reveals the name of the student who wrote the essay.

     However, I cannot get the code to recognize "Montaigne" or even "Normal," and therefore cannot know whether there is an "Encrypt/Decrypt" module available.

    The code does recognize the "MontInit" module under the "Louis Réard, Essai 1" project. It successfully recognizes subs and functions in that module. See the Error-msg image.

    I suspect that you almost have the solution. FYI, "Louis Réard" was the automobile engineer who, in 1946, invented the bikini, so beloved today by readers of Sports Illustrated.
   j.r.a.
Project-explorer.png
Error-msg.png
0
 
syeager305Commented:
Okay, change
Set VBProj = Documents("Normal").VBProject

or something like that, the error is coming because your refencing the activedocument rather that the document that contains the "montainge" module. :)
0
 
JohnRobinAllenAuthor Commented:
The code supplied still does not work. As shown in the image with comment 26141660, supplying "Normal" does not identify the module. I tried "Normal.Montaigne" (Montaigne is a module in "Normal" and stops on that line with an appropriate message.

     I realize that I am referencing the activedocument, but I do not know how to reference a module in "Normal."

        j.r.a.
ProcedureExists.png
0
 
JohnRobinAllenAuthor Commented:
The image I posted in my last comment was not clear. This is another try, but if this image is too blurred, the major items are (a) I get a "Subscript out of range" error when I reach the line that says "Set VBComp = VBProj.VBComponents("Normal.Montaigne")" and (b) the Project explorer shows two modules under "Normal": "Montaigne" and "NewMacros".

        j.r.a.
ProcedureExists.png
0
 
syeager305Commented:
Okay, the code I sent will search whateve rmodule you specify, in this case, VBProj.VBComponents("Montaigne")  would be searched, the object your reference with VBComp is the Module, the module name in this case is "NewMacros" or "Montaigne", it will search those modules for the code that you're looking for.  

change it to    Set VBComp = VBProj.VBComponents("NewMacros")  or montainge, wherever the code is at. :)

If you want to search all VBProjects would require you to iterate through all object within the Normal document....

0
 
JohnRobinAllenAuthor Commented:
This looks like the solution I need, but I first have to test it, and I have finished for the day. I will let you know the results before tomorrow noon (Mountain Time in the U.S.
          j.r.a.
0
 
JohnRobinAllenAuthor Commented:
I tried the suggested change and it still does not work. I want to return "Project does not exist" when there is no "Montaigne" module under Normal.

I suspect the problem may lie in the line
     Set VBProj = ActiveDocument.VBProject

     If VBProj is set to the ActiveDocument
then
     Set VBComp = VBProj.VBComponents("Montaigne")
will not find "Montaigne" in the active document. It is in the Normal template.

I tried guessing at how to change the VBProj to point to Normal without success.
I tried specifying "Normal.Montaigne" instead of "Montaigne", but that did not work either.
I even tried specifying "NewMacros" to see if it would find an MS-generated name, but it didn't.

I therefore suspect that the problem is simply that the code is looking for "Montaigne" as a module in the active document rather than in the Normal template.

If the code can be fixed to look under "Normal" will it give any problems that a user will sometimes have "Normal.dot" or possibly "Normal.dotm" loaded?

   I certainly am sorry to give so many problems.  Should I simply give up and say that what I am trying to do is impossible?

   I have increased the point value of the question to 500, and it is the first time I have ever thought one of my questions was worth more than 125 points.

    j.r.a.
0
 
syeager305Commented:
       Set VBProj = NormalTemplate.VBProject
0
 
JohnRobinAllenAuthor Commented:
I will try that tomorrow morning and let EE know the results. I suspect that the solution will solve all the problems. Thanks for the help.
0
 
JohnRobinAllenAuthor Commented:
The solution now works perfectly. Since I am not certain EE will publish all the steps, I awarded the points to several of S Yeager's letters.
     My only further request is some suggestions as to how I can learn more about how the solution works. Are there any books or web sites S Yeager or anyone else could suggest. I feel as if I were asking a magician how he does his tricks, but I cannot help that.
     Many, many thanks.
     j.r.a.
0
 
syeager305Commented:
John Walkenbach has several "easy to use" books, the best websites are cpearsons and ozgrid.  They have just about everything you need, last I recall cpearson has some great explanation about VBA programming concepts.  Also, check out you tube, I imagine there is some good tutorials there as well.

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 14
  • 11
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now