+ Reply to Thread
Results 1 to 14 of 14

Removing a KNOWN VBA password via VBA

  1. #1
    Forum Contributor
    Join Date
    07-16-2012
    Location
    Ontario Canada
    MS-Off Ver
    Excel 2010
    Posts
    124

    Removing a KNOWN VBA password via VBA

    Hi everyone,

    I am trying to think of the best way to release "updates" to a vba file.
    This is because at work, we create files but there are so many employees that its very difficult to manage all the versions floating around.
    Right now, I am using the Microsoft Visual Basic For Applications Extensibility 5.3 reference to edit the modules.
    This works unless I have a password in the VBA project.

    I know all the passwords. But I cant find how to temporarily remove the password, edit the script, then add the password again.
    Could anyone please point me in the right direction?
    Also, I would like to stay away from Send Keys as I feel like its very unreliable.
    If you have another method, I am also open to that too.

    thanks in advanced!
    Last edited by jason_lee_91; 03-09-2013 at 09:04 AM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Removing a KNOWN VBA password via VBA

    I don't think that the object model supports protecting or unprotecting the project.

    Why not instead make the workbook an add-in and store as read-only on a network share? Then you can update as needed.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Removing a KNOWN VBA password via VBA

    Hello jason_lee_91,

    See these links:

    http://www.excelforum.com/excel-prog...ject-code.html

    Especially shg's comment:

    BTW, are you aware that anyone can open your workbook in OpenOffice, unhide your very Hidden Sheets, and view your protected VBA project?
    http://www.ozgrid.com/forum/showthread.php?t=13006

    From that I think you will understand why it is not really worth the effort.

    Also see the "Please Consider" note at the bottom of this post.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Removing a KNOWN VBA password via VBA

    If you have already elminated SendKeys as an option, then you're out of luck. VBA does not expose the VBProject passwords in its object model. The only workaround is to use SendKeys to simulate you manually unlocking the project.

    If you Google you'll find many SendKeys techniques offered (the link above by Winon is good), and widely criticized as SendKeys is notoriously unreliable. I would not put this technique into a product I was giving OTHERS to use, and might only use it myself in the rarest of instances.

    I'm also going to watch this thread closely. If anyone breaks the forum rule regading offering solutions that overcome Excel's builtin securities, I'll shut this discussion down. Ok?
    Last edited by JBeaucaire; 03-09-2013 at 10:36 AM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  5. #5
    Forum Contributor
    Join Date
    07-16-2012
    Location
    Ontario Canada
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Removing a KNOWN VBA password via VBA

    Thanks everyone for your replies.

    Shg,
    By the workbook, do you mean the file that I will be updating? There are simply too many.
    And if someone saves it on their desktop, then I cant always access it.
    I would like for someone to be able to run a macro to perform updates to the VBA code that I have written.
    There could very well be a feature that I am not using properly though.

    JBeaucaire,
    Yes I completely understand.
    And I think your answer pretty much answers my question.
    I would never feel comfortable using Send Keys to edit modules especially for someone else.
    If its for me, I dont really need the updater so.

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Removing a KNOWN VBA password via VBA

    By the workbook, do you mean the file that I will be updating?
    Yes, an Excel file is called a workbook.

    What does the workbook do, how is it used?

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Removing a KNOWN VBA password via VBA

    Ok, then your next step would be to redesign your macros so users do not need to "edit code". Design your macro so there are plenty of variables that your users are given the ability to send values into, thus the macro can then execute based on the parameters they give at the time of execution. Users changing variables is much simpler than users editing your VBA.

  8. #8
    Forum Contributor
    Join Date
    07-16-2012
    Location
    Ontario Canada
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Removing a KNOWN VBA password via VBA

    Well I have a file that updated a workbook and I also have the workbook that gets updated.
    I wasnt sure which one you were referring to.

    Most of the files use ADO to pull from a SQL server and perform operations on the page to produce a report.

    The files are being edited not because the variables need changing, but rather the uses and needs for the files evolve and the code ends up having to change. The majority of the files have user forms and such but for example, if they want a brand new column of information, the VBA has to be edited.

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Removing a KNOWN VBA password via VBA

    Once again, why not put the code in an add-in, and distribute that?

  10. #10
    Forum Contributor
    Join Date
    07-16-2012
    Location
    Ontario Canada
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Removing a KNOWN VBA password via VBA

    Shg,
    am not quite sure of how i might do that.
    We have literally 1000s of files that are already existing. Many of them have 10-20 modules
    I probably release 2-3 each week depending on requests/demand.
    So would I put each of the files in a different add-in then add each of those add ins? Because I dont think I want 1000s of add ins.
    Or would I put every file into one add in?
    I think that might end up maknig the add in wayyy too large.


    Also, it doesnt address the issue of updating pre-existing files

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Removing a KNOWN VBA password via VBA

    I would put all of the code in a single add-in and put it read-only on a network share. The add-in would process data in external files -- maybe starting by sucking up the data from the thousands of existing files and saving them as macro-free workbooks.

    You've got a configuration nightmare.

  12. #12
    Forum Contributor
    Join Date
    07-16-2012
    Location
    Ontario Canada
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Removing a KNOWN VBA password via VBA

    Well none of the spreadsheets actually store data.
    They all draw data from a server and the end result can (almost) always be replicated.
    Except for the current working files ofcourse.
    The files before they upload it to the database.

    So you would put all the code in all the files in a read only add in?
    We have 1000s of file and its quite common for them to have upwards of 20 modules.
    I feel like an add in with litterally 10000s of modules and forms would be very very difficult to manage
    Last edited by jason_lee_91; 03-09-2013 at 02:45 PM.

  13. #13
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Removing a KNOWN VBA password via VBA

    So you would put all the code in all the files in a read only add in?
    Yes.

    We have 1000s of file and its quite common for them to have upwards of 20 modules.
    I feel like an add in with literally 10000s of modules and forms ...
    Huh? Workbook A has 20 modules, and they are all different than the 20 in Workbook B, and all different than the 20 in Workbook C, and ...???

    ... would be very very difficult to manage
    How well are you managing with your current approach?
    Last edited by shg; 03-10-2013 at 01:06 AM.

  14. #14
    Forum Contributor
    Join Date
    07-16-2012
    Location
    Ontario Canada
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Removing a KNOWN VBA password via VBA

    Shg,

    Hey sorry about that, I have been sooo busy recently.

    I cant speak for the other devs at the company but for myself, yes, the modules are all different.
    If 2 workbooks share common modules, then they are already in the same workbook.
    So putting the modules in one add-in now would mean putting together workbooks that I at first deemed too different to be in one book.

    I very much value your opinion and I gave it an honest attempt.
    But i think maybe in this particular situation, the one add-in approach is maybe not feasible?
    What do you think?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1