Hidden variables in Excel (Excel 97)

Topic

New Reply

Hi

In VB for Word, you can create “document variables”, like this:

        ActiveDocument.Variables.Add Name:="Temp", Value:="12"

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?

Reply | Quote

Replies

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.

Reply | Quote

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

Regards,
Jan

Reply | Quote

There’s a 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” .

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

There’s a 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” .

Reply | Quote

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

Regards,
Jan

Reply | Quote

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.

Reply | Quote

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.

Public Function LoadModuleToArray(strAr() As String, strUDoc As String)
    Dim i As Integer
    For i = 1 To DOCUMENTS(MacroContainer).VBProject.VBComponents.Count
        If UCase(DOCUMENTS(MacroContainer).VBProject.VBComponents(i).Name) = UCase(strUDoc) Then
            Dim lngCountLines As Long
            lngCountLines = DOCUMENTS(MacroContainer).VBProject.VBComponents(strUDoc).CodeModule.CountOfLines
            If lngCountLines > 0 Then
                Dim j As Integer
                For j = 1 To lngCountLines
                    Dim strLine As String
                    strLine = DOCUMENTS(MacroContainer).VBProject.VBComponents(strUDoc).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

and

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.Count
        If UCase(DOCUMENTS(MacroContainer).VBProject.VBComponents(i).Name) = UCase(strUDoc) Then
            Dim j As Integer
            For j = 0 To UBound(strAr) - 1
                DOCUMENTS(MacroContainer).VBProject.VBComponents(strUDoc).CodeModule.AddFromString ("'" & strAr(j))
            Next j
        Else
        End If
    Next i
End Function

Reply | Quote

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”.

Reply | Quote

> 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.

Reply | Quote

[indent]

---

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.

---

[/indent]
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).

Reply | Quote

> 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.

Reply | Quote

> 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.

Reply | Quote

[indent]

---

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.

---

[/indent]
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).

Reply | Quote

> 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.

Reply | Quote

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”.

Reply | Quote

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.

Public Function LoadModuleToArray(strAr() As String, strUDoc As String)
    Dim i As Integer
    For i = 1 To DOCUMENTS(MacroContainer).VBProject.VBComponents.Count
        If UCase(DOCUMENTS(MacroContainer).VBProject.VBComponents(i).Name) = UCase(strUDoc) Then
            Dim lngCountLines As Long
            lngCountLines = DOCUMENTS(MacroContainer).VBProject.VBComponents(strUDoc).CodeModule.CountOfLines
            If lngCountLines > 0 Then
                Dim j As Integer
                For j = 1 To lngCountLines
                    Dim strLine As String
                    strLine = DOCUMENTS(MacroContainer).VBProject.VBComponents(strUDoc).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

and

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.Count
        If UCase(DOCUMENTS(MacroContainer).VBProject.VBComponents(i).Name) = UCase(strUDoc) Then
            Dim j As Integer
            For j = 0 To UBound(strAr) - 1
                DOCUMENTS(MacroContainer).VBProject.VBComponents(strUDoc).CodeModule.AddFromString ("'" & strAr(j))
            Next j
        Else
        End If
    Next i
End Function

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Wow.

I’m not sure I understand it all, but here’s a start at summarising your responses:

Technique	Comments
Defined names	Uses the Names collection The value is stored in a cell Set visible=false to stop names being visible to users Free name manager available from here [/list]
Custom document properties	Uses the CustomDocumentProperties collection The value is visible in File/Properties/Custom 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.) Free document property manager available from here More help available from this post[/list]
User modules	Stores the data in a module in the VBA project The code looks a bit fearsome How do you display a value in a worksheet??? Requires write access to the VBA project Bloats filesize? In the same session, available to other workbooks (even after this workbook closed) Help available from this post Favoured by most cats [/list]
Excel hidden name space	The hidden name space is a memory area belonging to the current Excel instance Names can be defined even though there is no macro sheet available In the same session, available to other workbooks (even after this workbook closed) Undocumented feature, with concomitant risks Help available here [/list]

Thank you all for your help
Dale

Reply | Quote

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).

Reply | Quote

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).

Reply | Quote

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 ……..)

Reply | Quote

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 ……..)

Reply | Quote

Wow.

I’m not sure I understand it all, but here’s a start at summarising your responses:

Technique	Comments
Defined names	Uses the Names collection The value is stored in a cell Set visible=false to stop names being visible to users Free name manager available from here [/list]
Custom document properties	Uses the CustomDocumentProperties collection The value is visible in File/Properties/Custom 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.) Free document property manager available from here More help available from this post[/list]
User modules	Stores the data in a module in the VBA project The code looks a bit fearsome How do you display a value in a worksheet??? Requires write access to the VBA project Bloats filesize? In the same session, available to other workbooks (even after this workbook closed) Help available from this post Favoured by most cats [/list]
Excel hidden name space	The hidden name space is a memory area belonging to the current Excel instance Names can be defined even though there is no macro sheet available In the same session, available to other workbooks (even after this workbook closed) Undocumented feature, with concomitant risks Help available here [/list]

Thank you all for your help
Dale

Reply | Quote