Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Modify VBA in VBA (VBA Office 97 SR2)

    I'm looking for a method of version handling, essentially, I would like to replace code or modify code from VBA. It wouldn't be code from a file that is open or running, and the code doesn't have to manipulate itself, I'd just like to make a change without having to re-send the file.

    Any ideas?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Modify VBA in VBA (VBA Office 97 SR2)

    What you want is a benign virus??
    Charlotte

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify VBA in VBA (VBA Office 97 SR2)

    Drk,

    Charlotte
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Modify VBA in VBA (VBA Office 97 SR2)

    <img src=/w3timages/blackline.gif width=33% height=2>
    > They did not receive it...
    <img src=/w3timages/blackline.gif width=33% height=2>

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <IMG SRC=http://www.mtl.com/sam/images/computer.gif>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Modify VBA in VBA (VBA Office 97 SR2)

    Drk,

    If as Geoff pointed out, you would be needing to modify code in Normal.dot, then I'd expect the virus checker's alarms to go off.

    But it is possible to write code to modify code - depends on what you want to do.

    It sounds like you want different code to run, depending on which version of Word the code is going to run in; in this case can't you do an If test for version and run appropriate code accordingly? (or is it the case that the code you want to have run for say Word 2000, won't compile in Word 97?).

    Gary

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify VBA in VBA (VBA Office 97 SR2)

    Drk,

    You haven't mentioned which Office application. I've mentioned Word in this thread, but perhaps I can generalise a thought.

    I'm guessing that you've got something like a spreadsheet or a document into which the users enter data. You have an application behid the scenes which does nice things to their data. But you want to be able to update that application from time to time without imapcting their data.

    If that's where you're going, I'd suggest that you look at having a code spreadsheet or document separate from where the data is kept. Perhaps you may have a skeleton routine in the data document linking to the code document- or perhaps you can use add-ins. So if you enhance the code (or even correct it- though I know you won't want to do that), you can distribute the code without impacting on the data. It's more work to set up- but it will make life much easier down the track.

    I mentioned Word earlier in the thread. It was painful having to update normal.dot- and it was painful coming up with an alternate solution. But our add-in solution is now much more stable, and much more maintainable, than updating the code across modules.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify VBA in VBA (VBA Office 97 SR2)

    Thanks Geoff, the application is Excel. I've got a userform at the user's front end which accepts many different types of data, the method i've chosen is to house historical data locally, but after each call entry, the data is saved to a common network location.

    All the parts are there, but I do need to tweak the code to make things easier on our reps. The problem is that because all data must have an individual key assigned to it, (for easy import to Access) I have to ensure that I change code without effecting thier base structures. (Each representative has been granted a chunk of one million individual keys...) We're only talking a hand-full of representatives, but if I can automate this process and update the code remotely, that would be ideal. It's not going to be addition of objects, or anything like that, but simply a process of replacing code for a primary userform with something else.

    I hope this clarifies a bit.. I'm intruiged on your thoughts on making it an add-in, do you have any good resources for add-in development?

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify VBA in VBA (VBA Office 97 SR2)

    Drk,

    <A target="_blank" HREF=http://support.microsoft.com/default.aspx?scid=kb;EN-US;q211563>here</A> is something about creating Exxcel add-ins.

    Alternatively, you can just run routines in another sheet- as long as you have that sheet open. From the on-line help example for Application.Run:
    Run Method Example
    This example shows how to call the function macro My_Func_Sum, which is defined on the macro sheet Mycustom.xlm (the macro sheet must be open). The function takes two numeric arguments (1 and 5, in this example).
    mySum = Application.Run("MYCUSTOM.XLM!My_Func_Sum", 1, 5)
    MsgBox "Macro result: " & mySum
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  9. #9
    Lounger
    Join Date
    Nov 2001
    Location
    Fort Dodge, Iowa, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify VBA in VBA (VBA Office 97 SR2) [Addins]

    <P ID="edit" class=small>Edited by gwhitfield on 16-Jan-02 06:15.</P>Hyperlinks added

    Here's a book I got about a year ago with TONS on add-ins. Add-ins (for you just joining us) are a great way to make maintenance easier and to propogate changes to code _quickly_. The book is "Microsoft

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify VBA in VBA (VBA Office 97 SR2)

    Alas, I am back to this again... Let me rephrase the question..

    It doesn't seem that an Add-In is going to work in this instance, the primary reason is time. That said, is there a way to replace the code of a userform with the contents of a text file? Virus checkers aren't of concern here, (It's clear that I don't run the backend..) and automation of this process would save me gobs of time...

    Ideally, the user would open the spreadsheet, they'll be prompted, (Would you like to begin this process?) after clicking Yes, some process would check the code in the .txt file, and if there is a change, replace it.

    This may be way too much to ask of VBA, but your thoughts on this would be appreciated!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Modify VBA in VBA (VBA Office 97 SR2)

    There should not be a problem replacing code with updated versions, but the approach to use will depend on the location of the code, i.e is it a General Module, Class Module, Workbook/WorkSheet object or Userform. Could you be specific.

    Andrew C

  12. #12
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Modify VBA in VBA (VBA Office 97 SR2)

    A caveat here (which Andrew may be hinting at): updating code in a class module (including a userform), once that class has been instantiated, can be very problematic (at least in my limited experience it made the app crash).
    Updating the code in a regular module shouldn't be problematic.
    The solution would be to have procedure calls in the class module that call procedures in a regular module. The code in the regular module should be updateable.

    Gary

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify VBA in VBA (VBA Office 97 SR2)

    The code is in a userform. I've got another useform calling it though, serving as a splash screen and data compilation process before the main form is ever opened. (The main form contains the code I want to modify via VBA...)

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Modify VBA in VBA (VBA Office 97 SR2)

    What Gary said is very important - make sure that the userfoem has not been instantiated whilst changes are being made. Youu could distriibute a workbbook together with a text file containing the complete code for the userform. The workbook would contain the code to update the existingg workbook. Should the workbook that requires updating contain event procedures, you need to disable them when you open/activate the workbook. the following code activates "WBToUpdate.xls", selects the userform codemodule and removes all existing code and them updates the codemodule from UpDate.txt. You may want to include code to open the workbook to be updated.
    <pre>Sub UpDateUserForm()
    Dim i As Long
    Application.EnableEvents = False
    Workbooks("WBToUpdate.xls").Activate
    With ActiveWorkbook.VBProject.VBComponents _
    ("UserForm1").CodeModule
    If .CountOfLines > 0 Then
    For i = .CountOfLines To 1 Step -1
    .DeleteLines i
    Next
    End If
    .AddFromFile ("UpDate.txt")
    End With
    Application.EnableEvents = True
    End Sub</pre>


    It is most important that UpDate.txt is a simple text file with nothing other that the code. DO NOT recode your userform and export it and then try to update from that. Caution is advised and I am sure you know to experiment with backup data. As soon as your code is update have the code save the updated file.

    Hope it gets you started

    Andrew

  15. #15
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify VBA in VBA (VBA Office 97 SR2)

    This code was so Perfect!!!

    Now it's causing Excel to crash (Illegal Operation)... I know the forms have not been loaded at the point fo failure, any Ideas what may be causing this?

    Here's the error:
    EXCEL caused an invalid page fault in
    module VBA332.DLL at 015f:65191330.
    Registers:
    EAX=004f2030 CS=015f EIP=65191330 EFLGS=00210246
    EBX=004fb388 SS=0167 ESP=0062dadc EBP=0062db2c
    ECX=00004b48 DS=0167 ESI=0000ffff FS=4d47
    EDX=004ef00c ES=0167 EDI=004fe770 GS=0000
    Bytes at CS:EIP:
    8b 04 b0 85 c0 75 44 8b 5f 0c 8b 4c 24 14 85 c9
    Stack dump:
    004baa60 004baa3c 004fb388 651460f4 0062db00 004baa3c 00005068 004fba44 019f4b54 00000005 004fb168 004fb8cc 00004b48 00000000 00000000 00000000

    It happens Every time as long as the code above in enabled.. any ideas?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

Page 1 of 2 12 LastLast

Posting Permissions

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