+ Reply to Thread
Results 1 to 6 of 6

Advanced VBA-Use IDE to insert & run code in a new workbook after ThisWorkbook closed?

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Cool Advanced VBA-Use IDE to insert & run code in a new workbook after ThisWorkbook closed?

    The thread title says it all.

    I want to run code after ThisWorkbook is closed. Can ThisWorkbook use VBA to create a (temporary) new workbook, insert code module into the new WB and then use Application.OnTime to fire this new module code shortly after the ThisWorkbook closed?
    Attached Files Attached Files
    Last edited by mc84excel; 06-02-2013 at 10:40 PM. Reason: correct terminology
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

    Re: Advanced VBA-Use IDE to insert & run code in a new workbook after ThisWorkbook closed?

    I don't really know what the IDE has to do with running code - it's just the environment you write code in. But in direct answer to your question - why not try it and see?

    My 2pennyworth, sounds like a pretty horrible solution :
    • Firstly, what's wrong with manually removing the add-in? - this seems awfully complicated to me for such a simple task
    • Requiring users to give you the level of access you need is a no-no, and may not even be possible in some places
    • The installation code is flakey, send keys is *never* a good solution
    • Relying on things like onTime for proper functionality is a bit iffy, there are too many variables which would result in bits of code lying around that would need manually cleaning up - for example what if the user closes the workbook before the procedure completes?

    If you're concerned about inexperienced users not being able to add and remove add-ins then just provide them with a step by step guide with pictures, preferably one for each version of office. It's a lot easier and will give you fewer headaches - this what I do and the level of knowledge I deal with on a day to day basis couldn't get any lower; but people can follow simple instructions (especially with pictures).

    There's a temptation when you begin writing applications/automating tasks to try and make software do everything for the user despite the amount of upfront effort, this rarely works, adds complexity and means you end up writing loads of awkward code to accomplish trivial tasks. Software should be kept as simple as humanly possible to complete the task in hand and no more - it's easier to understand, easier to debug, and easier to adapt and build upon. At some point you have to ultimately trust the user, you can't do everything for them - that's why you have training courses for software

  3. #3
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to insert & run code in a new workbook after ThisWorkbook closed?

    Hello Kyle

    Firstly +1 for taking the time to provide such detailed constructive criticism (As you may have noticed from my signature - I usually don't like to see forum users publicly criticize the OPs need for the thread solution - but I make an exception here as you are obviously trying to help).


    The points you raise are valid however most of them won't apply in the usage I have in mind. I'll explain:
    1. Manually removing an add = What you are seeing is only a small extract of a much larger project. Part of it contains an auto-update/install for the next version of the add-in. So I need to manually unload & uninstall the old version. And I prefer to automate the removal rather than having to manually do it.
    2. Users permission = In this case, the end user will be myself and 2-3 close friends. So that won't be a problem here.
    3. Send Keys = Yes I know. I found the Add-In Cleaner code on the net and I can't see anyway around the use of Send Keys for this macro. (If you know otherwise, I would be grateful )
    4. OnTime = I wasn't aware that onTime was so iffy. Could you suggest another alternative? (In regards to the end user closing the workbook = the 3-4 users who are using this project have enough VBA nous to not do that )


    P.S. my use of "IDE" - I haven't done a course on VBA so I don't always use terminology in the correct sense. I use "IDE" to refer to VBAs use of the IDE reference library to add/edit VBA code from VBA. (I haven't learnt how to write IDE yet so that explains why I can't try it to solve this thread).
    Last edited by mc84excel; 05-30-2013 at 08:20 PM.

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

    Re: Advanced VBA-Use IDE to insert & run code in a new workbook after ThisWorkbook closed?

    IDE stands for Integrated Development Environment (it isn't a language or methodology) and refers to the tools that are provided to help you code, intellisense, compilation errors, object browsers, debugging etc, other IDEs exist for other languages (and many cross purpose ones); Microsoft has Visual Studio, eclipse is often used for Java etc... They are distinguished since you don't technically need an IDE to write code, for example if you were so inclined you could write C# code in notepad and compile it - in fact many developers (notably web, but certainly not exclusively) tend not to use IDEs preferring customizable text editors like Text wrangler or Notepad++ (or if you're really hardcode vi/vim). For example, I write a lot of JavaScript and I use Notepad++, then I use Chrome to debug, but I reckon you could almost claim that Chrome isn't far off an IDE

    This is possibly why your questions come across as slightly odd, your use of "IDE" is just wrong

    I'm going to duck out here and refrain from telling you why I disagree with your points, but I hope you achieve what you are looking for
    Last edited by Kyle123; 05-31-2013 at 07:01 AM.

  5. #5
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to insert & run code in a new workbook after ThisWorkbook closed?

    Hello Kyle,

    Thanks for the correction of IDE. How then should I refer to the use of VBA to create/edit VBA?

  6. #6
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Advanced VBA-Use IDE to insert & run code in a new workbook after ThisWorkbook closed?

    Using another WB to run code after closing was a bad idea. I'm now looking at using scripts instead.

    This webpage looked promising http://stackoverflow.com/questions/1...e-updated-xlam

    however it doesn't work. The code stops after unloading ThisWorkbook from add-ins.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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