Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Standard Code Module On The Fly (Xl2000/2002)

    As opposed to using a template, I have a one sheet workbook that when opened creates a custom menu which in turn allows for the creation of a fairly large and complex workbook used for billing reconciliation. All code is to be kept to a minimum in this billing workbook.

    There is some code which, depending upon the date in a specific cell, returns the hours in the month. I would like to, via VBA, create a standard code module and insert the necessary procedure in it. I have been looking into this and there are several ways to accomplish this task. Is there a fairly straight forward way of accomplishing this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Standard Code Module On The Fly (Xl2000/2002)

    To create code in code, you'll have to do the following:

    1. In the Trusted Sources tab of the Macro Security dialog, you must allow access to the Visual Basic Project (this will hold for all your users too!) Otherwise, you won't be able to write/modify code in code.

    2. In the Visual Basic Editor, set a reference to Microsoft Visual Basic for Applications Extensibility 5.3. The 5.3 is the version on my machine (XP), it may be different for 2000. This library exposes the objects, methods and properties of the Visual Basic Editor.

    Here is a trivial example:

    ' Declarations
    Dim vbp As VBProject
    Dim vbc As VBComponent
    Dim mdl As CodeModule

    ' Set a reference to the current project
    Set vbp = ActiveWorkbook.VBProject

    ' Create a new VB component, a standard module
    Set vbc = vbp.VBComponents.Add(vbext_ct_StdModule)

    ' Set a reference to the code module in the VB component
    Set mdl = vbc.CodeModule

    ' Now, create some wonderfully original code in the module
    ' Note the use of double double quotes to insert a string in a string
    ' If you're going to write code in code, you'll see a lot of that
    mdl.AddFromString _
    "Sub Test()" & vbCrLf & _
    vbTab & "MsgBox ""Hello World""" & vbCrLf & _
    "End Sub"

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Standard Code Module On The Fly (Xl2000/2002)

    Thanks Hans! Looks just like what the doctor ordered.

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Standard Code Module On The Fly (Xl2000/2002)

    I am able to now get my code into the new billing workbook. Works great, except for one small problem. The code module created on the fly, that Hans so graciously helped me with, needs to be fired from a worksheet event:
    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target = Range("B9")
    Call OtherInputsDate
    End Sub
    </pre>

    I have spent all day, trying various methods of getting to the specific sheets code module, to add this event to it. I have not had very much luck with this effort. The answer will be so simple as to be embarrassing... alright, enough crying in my beer, back to work.

    <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Standard Code Module On The Fly (Xl2000/2002)

    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    never mind .....

    vbext_ct_Document should do the trick.

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Standard Code Module On The Fly (Xl2000/2002)

    Be aware though that the 'trusted sources' setting in the Security tab is by default disabled and it's not something you can programmatically enable. Therefore, you'll find the vast majority of your users have it disabled.

    Also, there is no such beast in Excel 2000 so you should test for the Excel version or handle the error some other way.

    Deb <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Standard Code Module On The Fly (Xl2000/2002)

    Deb,

    Thanks for the information. I have abandoned the direction that I was heading.

    Our Corporate AntiVirus app didn't like the code that I was writing. I kept getting a Bloodhound.ExcelMacro virus message and my work quarantined. I changed the sensitivity of the Heuristic scanning settings to minimum and the warnings stopped. All was well with the world. The next day when I fired up my computer to continue working, the file size was zero and in the Virus History log was the name of my file showing deleted. Nice.

    Of course I didn't back up my work, unlike what I preach to my users. This incident served to reinforce the need to back up my work to someplace other that the box I am working on. I also re thought was I was trying to accomplish and using an IF function combined with a few AND's and a VLOOKUP was able to accomplish what I needed to do.

    My forehead hurts from banging it on the desk. On to the next problem...

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Standard Code Module On The Fly (Xl2000/2002)

    WOW! <img src=/S/drop.gif border=0 alt=drop width=23 height=23> Your company has serious anti-macro policy in place, jeesssh! I wonder if the outcome would of been different if you had a digital certificate assigned to the file. So they delete your files if you use security = High, is that it? For Excel 2002, high security is the default setting. I don't know about the Bloodhound thing.

    Deb <img src=/S/flee.gif border=0 alt=flee width=25 height=25>

  9. #9
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Standard Code Module On The Fly (Xl2000/2002)

    Unless one has total control over the environment in which code is to be run, code should not be created on the fly.

    Auntie virus software often will, and should, detect ANY attempt to modify code on the fly.

  10. #10
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Standard Code Module On The Fly (Xl2000/2002)

    Howard,

    The program that I am working on will be used by only 3 people (so far). The invoice, when completed, is sent to another company. The reason why I am trying to keep as much code out as I can from the actual invoice is to keep the file size as small as possible (approx. 30 MB without any code) and to keep the source code confidential without having to play around with security issues.

    This was my first effort at coding on the fly. My butting heads with NAV essentially forced me to re think what is was that I wanted to accomplish. I found a much simpler and cleaner solution than what I had in place originally.

    Brent

  11. #11
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Standard Code Module On The Fly (Xl2000/2002)

    The easiest way to keep the code secure is to put almost all the code in a class, then compile the class into a VB 6 ActiveX DLL.
    The only code left in Excel workbook would be:

    1. Code to instantiate the class.
    2. If needed, wrapper functions to allow functions in the DLL to be used as worksheeet functions.

    The compiled code will also execute much faster than non-compiled code.

    The compiled DLL is separate from the workbook, so changes could be made to the code without changing the code in the workbook.

    Since you wish to use the code in Excel 2000 and 2002, you should use early binding and compile the DLL using the Excel 2000 object library. If you do not have Excel 2000 available, then you would, ugh!, need to use latebinding wqhen you compile the DLL using the Excel 2002 object library.

    I did an analogous project for a Client last year, the code really hums when compiled into a DLL.

Posting Permissions

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