Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    378
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Hidden variables in Excel (Excel 97)

    Hi

    In VB for Word, you can create "document variables", like this:
    <pre> ActiveDocument.Variables.Add Name:="Temp", Value:="12"</pre>

    These variables are not visible anywhere, but can be displayed in a document with fields, like {docvariable "Temp"} (which, in this case, displays "12").

    Is there an equivalent in Excel?

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden variables in Excel (Excel 97)

    You could use defined names for this purpose and set their Visible property to False.
    That way those names will not show in the list of defined names.
    Download my Name Manager to ease creating and managing names.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden variables in Excel (Excel 97)

    Another approach could be to use a CustomDocumentProperty instead to store values that you need to retrieve later.

    Regards,
    Jan

  5. #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: Hidden variables in Excel (Excel 97)

    In Word/VBA I store copious amounts of data in User Modules. I don't see why you can't do the same in Excel.

    In Excel, Tools, Macro, Visual basic editor. In VBE Insert, User Module. Type away to your heart's content.

    I preface each line of data with a single quote, and remove it before use, but that's no big deal, because my VBA utioities do it all for me.

    <pre>Public Function LoadModuleToArray(strAr() As String, strUDoc As String)
    Dim i As Integer
    For i = 1 To DOCUMENTS(MacroContainer).VBProject.VBComponents.C ount
    If UCase(DOCUMENTS(MacroContainer).VBProject.VBCompon ents(i).Name) = UCase(strUDoc) Then
    Dim lngCountLines As Long
    lngCountLines = DOCUMENTS(MacroContainer).VBProject.VBComponents(s trUDoc).CodeModule.CountOfLines
    If lngCountLines > 0 Then
    Dim j As Integer
    For j = 1 To lngCountLines
    Dim strLine As String
    strLine = DOCUMENTS(MacroContainer).VBProject.VBComponents(s trUDoc).CodeModule.Lines(j, 1)
    strAr(UBound(strAr)) = Right$(strLine, Len(strLine) - 1)
    ReDim Preserve strAr(UBound(strAr) + 1)
    Next j
    Else
    End If
    Else
    End If
    Next i
    If UBound(strAr) > 0 Then
    ReDim Preserve strAr(UBound(strAr) - 1)
    Else
    End If
    End Function
    </pre>

    and
    <pre>Public Function LoadArrayToModule(strAr() As String, strUDoc As String)
    If lngModuleExists(MacroContainer, strUDoc) Then
    Else
    Call lngAddModule(MacroContainer, strUDoc)
    End If
    Call lngClearModuleText(MacroContainer, strUDoc)
    Dim i As Integer
    For i = 1 To DOCUMENTS(MacroContainer).VBProject.VBComponents.C ount
    If UCase(DOCUMENTS(MacroContainer).VBProject.VBCompon ents(i).Name) = UCase(strUDoc) Then
    Dim j As Integer
    For j = 0 To UBound(strAr) - 1
    DOCUMENTS(MacroContainer).VBProject.VBComponents(s trUDoc).CodeModule.AddFromString ("'" & strAr(j))
    Next j
    Else
    End If
    Next i
    End Function
    </pre>


  6. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden variables in Excel (Excel 97)

    Hi Chris,

    So what do you do to prevent pileup of junk in the module due to the heavy "editing" of the code that your method uses?
    Of course this method will not work on protected projects, right?

    BTW: I assume you know there is no such animal as a "User module", only a normal "module". <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden variables in Excel (Excel 97)

    There's a <img src=/S/free.gif border=0 alt=free width=30 height=15> downloadable workbook with a family of VBA routines to help you create, amend, and delete Custom Document Properties here.

    Alan

    P.S. Bad name for a website IMO. At a half glance, I thought it read "buggysoftware" <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

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

    Re: Hidden variables in Excel (Excel 97)

    > no such animal as a "User module",

    Quite right! It was very early morning, and I was trying to ensure I didn't write 'User Form". Thanks for picking me up on that. (Must get more sleep. You wanna cat?)


    > will not work on protected projects, right?

    Quite right. I have two aces up my sleeve:
    1) Most commonly I'm using this on a user's document, where the user knows I'll be changing the document, but the user has no "VBA code to be protected", or so they think.
    2) I can test for a locked document or template, and suggest that the user unlock it before continuing.

    > the heavy "editing" of the code
    Not always "heavy", either. One example (Docgen) allows a typist to transcribe a table of data into the document, I then convert the table to data in a module. At a later date, the typist brings the data back into table form, makes a few changes, and re-stores the table in a second module. In this way several revisions of a legal document might be stored within the document's modules without a great deal of change. The number of modules increase, but there's little bloat as we know it.

    Now, is "bloat" reduced by removing and rebuilding modules? Payne Consulting seem to think so, in both their Word & Excel code cleaners. To update a specific module, I delete and re-make the user module each time I move the user's table of data back into the VBE, so I suspect it won't bloat that much at all.


    Another application, "Spell Checker", might be aggregating small changes over a period, but it is small data compared to the massive amounts of source code that get shuffled around during VBA development, and the volume of "remembered" spelling stuff is miniscule compared to the volume of text in the user document.

    I hope that that all makes sense.

  9. #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: Hidden variables in Excel (Excel 97)

    Ahem! (now that search is back);

    I discussed this here some time ago.

    The attached file is an updated module, and you don't need utils.DLL anymore.

  10. #9
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Hidden variables in Excel (Excel 97)

    I have never used it, but see also Excel Hidden Name Space, though these are apparently application-instance-specific, not document-specific as you were requesting.
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

  11. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden variables in Excel (Excel 97)

    <hr>Now, is "bloat" reduced by removing and rebuilding modules? Payne Consulting seem to think so, in both their Word & Excel code cleaners. To update a specific module, I delete and re-make the user module each time I move the user's table of data back into the VBE, so I suspect it won't bloat that much at all.<hr>
    Using that method I expect no bloat either. I just didn't find the deleting of the module in the code you gave (or maybe overlooked it).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #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: Hidden variables in Excel (Excel 97)

    > or maybe overlooked it

    You didn't overlook it - it isn't in the sample I gave.

    The two sample routines are to transfer data between a module and an array. At a higher level I'm managing the modules and arrays themselves, removing and adding modules in particular.

  13. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    378
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hidden variables in Excel (Excel 97)

    Wow.

    I'm not sure I understand it all, but here's a start at summarising your responses:
    <table border=1><td><center>
    Technique</center>

    </td><td><center>
    Comments</center>

    </td><td>Defined names</td><td><UL><LI>Uses the Names collection
    <LI>The value is stored in a cell
    <LI>Set visible=false to stop names being visible to users
    <LI>Free name manager available from here[/list]</td><td>Custom document properties</td><td><UL><LI>Uses the CustomDocumentProperties collection
    <LI>The value is visible in File/Properties/Custom
    <LI>The value can be displayed in a worksheet [edited later] by defining a VBA function to "get" a property, and using this in a formula in the spreadsheet. (Problem: The value in the cell doesn't automatically update when you change the value of the custom property.)<LI>Free document property manager available from here
    <LI>More help available from this post[/list]</td><td>User modules</td><td><UL><LI>Stores the data in a module in the VBA project
    <LI>The code looks a bit fearsome
    <LI>How do you display a value in a worksheet???
    <LI>Requires write access to the VBA project
    <LI>Bloats filesize?
    <LI>In the same session, available to other workbooks (even after this workbook closed)
    <LI>Help available from this post
    <LI>Favoured by most cats[/list]</td><td>Excel hidden name space</td><td><UL><LI>The hidden name space is a memory area belonging to the current Excel instance
    <LI>Names can be defined even though there is no macro sheet available
    <LI>In the same session, available to other workbooks (even after this workbook closed)
    <LI>Undocumented feature, with concomitant risks
    <LI>Help available here[/list]</td></table>

    Thank you all for your help
    Dale

  14. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hidden variables in Excel (Excel 97)

    Just a correction:

    If you use a defined name, the value is NOT stored in a cell. You can store anything in a name like this:

    Sub test()
    ThisWorkbook.Names.Add "Test", "Anystring", False
    MsgBox ThisWorkbook.Names("Test").Value
    End Sub

    But you'll have to extract the information from the resulting string (remove the equal sign and the quotes).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #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: Hidden variables in Excel (Excel 97)

    Thanks for taking the time to summarize. An effort worthy of reward. I wish that I wer as dilligent.


    ># The code looks a bit fearsome

    True, but at the drop of a cat^H^H^H hat I'd be pleased to supply an updated demo template, essentially a DIY library template that can be accessed from other VBA applications.


    ># In the same session, available to other workbooks (even after this workbook closed)

    Now I'm not sure that I understand this. I can envisage a Word template sitting in, say, my Startup folder, accessible to other Word projects. I can't see me grabbing raw data (essentially code) from the project unless it is unlocked. I can see me using something like a Class or public fucntion to deliver data from a user module.

    I should explore this further and report back. It would make sense in my Spelling-checker, so that I could nominate a SET of documents, each with its own Local Dictionary (stored as a Module in the document) available to be combined into a super-local dictionary. Perhaps all local dictionaries in a folder could be used as an auxiliary dictionary.


    Hmmmmm. Thanks for the impetus (trots off to spend a few hours messing about in VBA ........)

Posting Permissions

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