Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Proposal for Code Warehouse

    A routine and function collection warehouse within or adjunct to the Lounge would be very useful, to VBA newbies like me, and it would probably be of use to skilled coders who need to get a quick subroutine to build into a larger project.

    Some of the folks who make their living consulting in and writing Office suite code may be uncomfortable with this proposal, but my intent is code which functions as extensions of the Office suite capabilities, rather than full-blown client-specific developments.

    I'm personally just trying to scratch the surface of VBA, and with many VBA experts here many of the dialogs between them result in exchanges where the last few posts are something like: "If you change that 8th line to <brilliant code> it will work better"; I don't have the skill (which also means I don't have the time) to assemble the final product from all these exchanges.

    There may be a number of ways to implement this idea, but I 'm thinking that "Bankers", analogous to Moderators, would be selected for each Office product (Word, Excel, etc.), for all the final code products and would set them up and maintain them in a side VBA code archive indexed by Office product and then by purpose with a description, like download sites such as ZDNet, but in text format, not as downloadable files.

    Reaction?
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Proposal for Code Warehouse

    I've been working with the folks in the lounge for about 3 months now, working on a specific project which required TONS of code. The project, to make Excel look/feel/act like Access, has taught me a lot about VBA and it's particulars. I too am scratching the surface, but I feel that the correspondence between these more knowledgable people is making me learn/research more on my own, making me a more viable VBA resource in my daily work.

    Because you're dealing with literally Millions of code combinations throughout VBA to achieve various functions, it would be difficult to catalouge them all in a means which would be easy to understand. In this, essentially the lounge's infancy, it's rare that the same VBA question is asked twice, from what i've witnessed.

    There are sites out there which are well-off on cataloguing various forms of code:
    Dev Ashish's Access Web contains specific VBA code which may apply to Access.. (i've sucessfully incorporated some of this code to Excel...)
    <A target="_blank" HREF=http://www.mvps.orgvbnet>Randy Birch's VBNet</A>
    <A target="_blank" HREF=http://www.mvps.orgvbvision>Bryan Stafford's vbVision</A>

    Some of these are tailored to the full-fledged Visual Basic Programmer, but you get the idea. The lounge is already a warehouse for VBA and other VBA code samples, as they apply to each part of Microsoft Office. There is also a general VB/VBA Lounge... But hey, I do like the idea! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    <IMG SRC=http://www.wopr.com/w3tfiles/43-24424-shot.gif> <img src=/S/lightbulb.gif border=0 alt=lightbulb width=15 height=15><IMG SRC=http://www.wopr.com/w3tfiles/43-24424-shot.gif>
    <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

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Proposal for Code Warehouse

    Hi John,
    In principle I think that is an excellent idea. (no I'm not volunteering to be a 'Banker')
    "Woody's Code Emporium" has a certain ring to it (still not volunteering)
    It does however sound like an awful lot of work for something that I suspect the majority of Loungers wouldn't use terribly often. (no way could you take that as volunteering)
    There has been a similar sort of movement in the VB/VBA forum where Chris Greaves (among others) has been posting various utility functions and procedures for others to criticise, improve or just plain steal but unless there's a way of automating the majority of the work, I can't see anyone having the time to collect, catalogue, index and cross-reference all of the code that gets posted in this place. I have been meaning to build a database to store the post notifications from the Lounge so I can search it offline and if I ever get around to it and find a reliable way of stripping out code, I'll be happy to share! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    In the end though, it's up to <img src=/S/queen.gif border=0 alt=queen width=19 height=20> <img src=/S/witch.gif border=0 alt=witch width=15 height=15>Eileen...
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proposal for Code Warehouse

    Brooke Tindall is testing my newest scam - the Procedure Packer. I'll explain "scam" in a moment.


    The packer is pointed to a LAN (or if you're a wuss, just the computer, or a single hard drive) and scours the entire region looking for templates, and grabs the procedure code in an encoded form. In essence it builds a single HUMUNGOUS string that is an encoded representation of the entire WORD/VBA code sitting anywhere on the LAN. This process is passive (runs late at night) and hence is good, because no public code goes unnoticed.

    The application comes with a Finder; you key in a string of characters (any string at all, such as "MsgBox", "APIProfileString", "=" or "1", I don't care) and the encoded string is examined for occurences which are then listed as <line within proc><proc><module><template><folder><path><drive> <computer>.

    Click on one instance, and the procedure is built in another box and can be appended to the clipboard.




    Now, at long last, I can find that piece of code I wrote two years ago.




    Scam:

    If I can peersuade (i.e. talk my peers into liking the idea) Word/VBA loungers to make use of this tool, then the next time I pose a request for help, they'll rush to help me, because they, like me, will be able to find that little piece of code they wrote two years ago. I'm just looking after my own interests as usual.



    Now, such a tool might be able to scour the entire Lounge database for code ....... and then we wouldn't need volunteers.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Proposal for Code Warehouse

    Chris,
    As a matter of interest (assuming it's not proprietary information) how does it determine what is code? My guess would be that it takes everything out of modules as it would be hard to do it by searching for keywords? In which case the Lounge might prove a little tricky! I can't help but feel that there would be a large amount of manual intervention required and although I would be happy to help (in spite of my reply to John <img src=/S/grin.gif border=0 alt=grin width=15 height=15>), there's no way I could guarantee having that kind of time free (and I don't think I know anyone who could) unless I retire really early...
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proposal for Code Warehouse

    >how does it determine what is code?

    <pre>(snip)
    ' Examine each line of this module.
    For lngInd = 1 To objModule.CodeModule.CountOfLines
    Application.StatusBar = strStatusBar & " at " & _
    str(lngInd) & " of " & str(objModule.CodeModule.CountOfLines)
    Dim strProc As String ' Identify each procedure as we go
    Dim strOldProc As String ' Identify each CHANGE OF procedure as we go
    ' What line do we see?
    strProc = objModule.CodeModule.ProcOfLine(lngInd, vbext_pk_Proc)
    If ("" <> strProc) And strOldProc <> strProc Then
    ' We are about to start a new procedure.
    (snip)
    </pre>




    I also contemplate a crude-display. Insetad of neatly formatting the results (as is done at present) I could pop the results up as 50 characters of the raw string, one line per found, with the found string aligned dead centre of the screen. Here's how it would look if I searched for the equals sign:
    <pre> .Name = "*.dot" ' Specifi
    If .Show = -1 Then
    strName = .Name ' Grab the
    ments.Open FileName:=Options.DefaultFil
    owVisualBasicEditor = True ' Open VBE
    Window.WindowState = 2 ' same as vbext
    uments.Add Template:="d:greavesOrange
    </pre>

    Once this raw-display is thrown up, the user could refine the search and ask for the (current) neat display with, say "VisualBasicEditor = True " as the search string.



    The idea behind the passive-accumulation is that almost NO manual intervention takes place by the host. I know that your original post remarked on the difficulty of assembling the "final version" of code. Here's a mechanism that locates all code that contains a search string and allows one to selectively append it to the ClipBoard. It's one step beyond that to take all those clippets (!) and drop them (by program) into a pigeon-hole.

    I want to get the thing working across a computer, at which time I'll u/l it for trial on Woody's Lounge. At that point it's up for grabs as an idea.

    I usually find that the hardest part of any new thing like this is developing the format of the storage (I have chosen nested strings with tags) and the slave functions to do the low-level searching and assembly. Once that is in place, adapting it all to a new environment is not so hard.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Proposal for Code Warehouse

    you still have the problem with the Lounge though, that there are no modules to search for code. That means you have to determine which parts of the text (if any) are code. I guess a search on 'sub' or 'function' would be reasonably accurate but you'll still have instances where those words don't actually exist as part of a procedure or where the code is incomplete and doesn't actually have a Sub...End Sub structure. Without a solution for that, it still sounds like manual intervention to me.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proposal for Code Warehouse

    > you still have the problem with the Lounge though, that there are no modules to search for code. That means you have to determine which parts of the text (if any)


    I agree. Without a well-defined structure I can't obtain ALL the code that's fit to print.

    Library1: I could certainly trawl the database and grab all those chunks that appear to be complete procedures (Function followed by EndFunction with no itervening Sub etc)

    Library2: being all the text from the database. There there IS a structure. Encoded, that second library with the flat display and the ability to click on an item in the LH box and obtain for clipboard ten lines either side ought to go a long way towards it.


    Or we could aim for obtaining 95% of the code (all those chunks that can be loaded to Library1) and let the user search for any other pieces that appear to be related. Maybe those remnants could be passed to the Librarian-person. Their manual work would consist of creating a post that includes any found improvements with the original function. They post the upgraded function as a new post and it gets swept up in the next Library 1.

    I'm assuming some sort of automated date-stamp here, so that a use could search for 'ApiProfileString' and clipboard the latest date-stamped complete function as being the one most likely to work. It doesn't work? They SEARCH the lounge or post a follow-up question. Ultimately the USER drives the production of a better version of the function.

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

    Re: Proposal for Code Warehouse

    Sounds like a lot of work to me.. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    <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

  10. #10
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proposal for Code Warehouse

    Drk,

    I get the firm impression Chris likes creating work. Anyway, sound's like we've got our volunteer!

    Brooke

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proposal for Code Warehouse

    I *hate* work. That's how I got into computing.

    I LOVE finding a way for the computer to relieve me of the boring and repetitive stuff.

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proposal for Code Warehouse

    (Copy of an email response to Brooke Tindall, whose help has been MIGHTY in getting me this far with the Procedure Clipper)


    > Public Function strPackTemplate(strLocPath As String, strFile As String, lngAr() As Long) As String


    OK. I have to do BIG THOUGHTS on this.



    To date I have a device which will scour all DOT across a computer. It proves that the technique is useful (for I believe you and I have each gotten useful data from it).


    BAS, XLS, MDB
    =============

    One of the big things about VBA (Charlotte's caveats notwithstanding) is that VBA procedures written for Word97 are useful in Excel, in Access and so on.


    This suggests that it was somewhat parochial of me to think just in terms of Word97.


    So what I need is a real Procedure Clipper, one that will wander a computer system and obtain every skerrick (it's in my 1989 Oxford Concise, but NOT in my Candian Oxford nor my two American dictionaries!) of VBA code, from DOT, XLS, MDB and BAS files.

    I have written a Word97 beast that will trawl DOT, and it can easily be made to inspect BAS (you only have to change the extent in the little GUI that pops up from ReBuild to make it inspect BAS files; it just won't do you an awful lot of good in the current version!). A found BAS would have a drive|folder|template identifier (the template being the DOS file name) but an empty string for the module name. I'd have to do a very simple lexical and syntactical analysis of the code to locate the start and end of the procedure. Not a big deal, given that if I fail to identify the procedure, the code will still pop up as found - see the KWIC search for any example. Nothing escapes me.




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

    This takes me back to JohnBF's original post (30999) and Rory's response (31373 / re: 31364) and DrkRealm's comment (31542 / re: 31530 ), for which reason I am taking this back to the Lounge forum.

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




    It would seem NOT to make sense to write another two vehicles, one written in XLS and one written in MDB, to do the same thing, to share the same engine deep down, but be different at the top.

    Consequently I am now pondering extra code in my Word97 beast that will locate XLS files and place a call to Excell to return the code modules from there one by one. Likewise a call to Access.

    In parallel with this thought I'm pondering the possibility of making the Word97 beast be very good at analysing BAS files, and having three small drivers (Word, Excel, Access) that scour for THEIR file types and pass the BAS form of modules to the Word97/BAS analyser. That Word97/BAS analyser would therefore EITHER "scour the LAN for BAS" OR "receive BAS and emit encoded strings".

    That makes me think of having the Word97 beast accept a BAS file and paste it into a temporary Word97 template module solely for the purposes of building an encoded string - it would use my exisiting code at the module level - and then do the header stuff for the path, file and TRUE module name.



    Of all of these, I think I like the idea of having the current beast call Excel/Access and ask them to deliver the modules of a specific file.

    In any way, I'll have to spend a bit of time examining that.


    I don't want to write code that looks at BAS text files if I can easily grab them as modules. Also I don't want to (spend time) write code that figures out the parent of an exported (from Excel/Access) BAS file if I don't have too.



    A procedure Clipper that could trawl all 4 file types (Excel, Access, Word, BAS) ought to be a jolly useful thing, no?


    And hence to the Lounge forum : a beast that can trawl text files and report findings in a manner that permits pasting to clipboard, reduction/elimination of identical procedures etc starts to get useful.

  13. #13
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proposal for Code Warehouse

    Two things:

    don't forget .FRM's

    also: you can import an excel .BAS and an excel .FRM into a word document. whilst the code probably won't run too well, that isn't what you're after. I suspect the same would hold true for access.

    Oh yes, three things. How is powerpoint code stored???

    Brooke

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proposal for Code Warehouse

    >Oh yes, three things. How is powerpoint code stored???


    heh heh! It *looks* like this .....

    <pre>Public Sub SaveAsHTML()

    ' 1) Save the PPT presentation in RTF format

    Dim strSavedName As String
    strSavedName = strSavePPTinHTML

    ' 2) Load that saved path and name to the Think.INI
    Dim strIniFile As String
    strIniFile = strStartupPath & strcPathSeparator & strcApplication_
    & strcExtentSeparator & strcINIExtent
    Call strPP(strcApplication, strcRTFFile, strSavedName, strIniFile)

    ' 3) Invoke the Thinker macro RTF_To_HTML
    ' We need an object which looks like Microsoft Word
    Dim objApp As New Word.Application
    ' Let's make it visible to the user, lights and wheels flashing and spinning.
    objApp.Visible = True
    ' Now we need to have a document on the desktop so that we can
    ' run our application macro.
    objApp.Documents.Add
    ' Then we can run our application macro.
    objApp.Application.Run "RTF_To_HTML"
    ' And when we are done, we unload the Word application.
    objApp.Quit

    End Sub
    </pre>


    OK. I can visualize an add-in for PowerPoint, Access, Excel that would act a bit like the VBA DIR function - On the first call it initializes an iteration through all code modules of a named file, and on subsequent calls it delivers the next code module. The Word97 code could then encode the module delivered by the add-in and marry that to the drive/folder/file decsription.

    Presumably the same technique could be used for any other VBA application. What else qualifies? Outlook?

    It would be nicer, though, to have Word be smart enough to obtain the code modules itself by an appropriate call. gee. I might need help on this one (Hint!)

    That leaves just the BAS files, for which I do a crude analysis.


    Then if I'm going to do crude analysis for BAS files I may as well do it for any TXT files that just happen to hold VBA code, right?


    Ohmigawd.

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proposal for Code Warehouse

    This is addictive.


    I can now see my way clear to getting all your Excel VBA from my Word.

    <pre>Sub GetExcelApplication()
    ' We need an object which looks like Microsoft Excel
    Dim objApp As New Excel.Application
    ' Let's make it visible to the user, lights and wheels flashing and spinning.
    objApp.Visible = True
    ' Now we need to have a document on the desktop so that we
    ' can run our application macro.
    objApp.Workbooks.Add
    ' Then we can run our application macro.
    objApp.Workbooks.Open ("d:greavesclientswatsonDemisterDimensioning.xl s")
    MsgBox objApp.ActiveWorkbook.VBProject.VBComponents.Count
    Dim i As Integer
    For i = 1 To objApp.ActiveWorkbook.VBProject.VBComponents.Count
    Dim cm As CodeModule
    Set cm = objApp.ActiveWorkbook.VBProject.VBComponents(i).Co deModule
    MsgBox cm.CountOfLines
    Next i
    ' And when we are done, we unload the Excel application.
    objApp.Quit

    End Sub
    </pre>


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
  •