• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hard Code Table Data In The Macro ??

Logit

Active Member
Is there a way to place the table data inside the macro code itself, rather than in a worksheet ? Then to be able to search that data ?

Any suggestions ?
 

Hui

Excel Ninja
Staff member
you can store data in macros as Constants or arrays

eg:

Public Const MyInteger As Integer = 42
Private Const DaysInWeek As String = "Seven"

or as arrays
Code:
Const AValues = "1,10,5"
Public A(2) As Integer

Public Sub MyMacro()
Dim vntTemp As Variant
Dim intIndex As Integer
vntTemp = Split(AValues, ",")

For intIndex = 0 To 2
   A(intIndex) = vntTemp(intIndex)
Next

Debug.Print A(0) 'will return 1
Debug.Print A(1) 'will return 10
Debug.Print A(2) 'will return 5
End Sub
How about posting a sample of what sort of data you want
 

Hui

Excel Ninja
Staff member
Can I please ask why you would want to store this type of data in a macro ?

Firstly, You may say they are fixed, But looking at the data, I doubt that.
You are likely to want to add/remove people

I would generally store this data in a Table and if the security is important, lock or hide the worksheet
You can load 100,000 rows of this data into an array in VBA in 1 line, and it is blindingly fast, so there is no performance to be gained by storing the data natively in VBA

Code:
Dim myArr As Variant
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
myArr = Range("A2:L" & lr).Value
The code above will load your data into an Array in VBA and it takes about 0.15 seconds to load 100,000 rows of similar data, 1.6 seconds to load 1,048,575 rows
 

Logit

Active Member
The data will change maybe every six months ... probably more like yearly. Having to recreate the data inside the macro would not be a problem with an annual or semi-annual schedule.

Using tables on a sheet has always been an option but I've been trying to find a way to completely obscure (as much as Excel will allow) the data so it can't be reproduced. Having said that, I realize there are ways of circumventing those protections but the average user of this project will not go that far.

The data I want to hide ... I am paying a fee for. I'd really like to make it difficult for someone to reproduce it.

As an alternative (just throwing this out there), if not in the macro code itself, is there a means of coupling with Access ? Using an Access sheet in the same manner as you would Excel for storing tables ... and being able to secure the Access sheet more securely than just hiding/password the sheet ? I'm not familiar with everything Access is capable of.

I'd prefer to keep this project all within Excel ... but can entertain using Access for this portion.

Thank you for your response.

Thoughts ?
 

Hui

Excel Ninja
Staff member
In VBA you can use the Worksheet.veryhidden property to hide the worksheet so it can only be unhidden from within VBA

activesheet.visible = xlsheetveryhidden
or
Worksheets("This worksheets name").visible = xlsheetveryhidden

The data is still available to VBA, just a user can't see it

You can password protect VBA as well to strengthen the protection.

Ultimately where ever it is stored it is a computer file and so can be accessed by somebody with the appropriate skills/tools

At least in a Worksheet it can be a lot more easily managed
 

Chihiro

Excel Ninja
Personally, I'd store it in database and query it as needed for use...

If that's not feasible, like Hui has stated.
Storing data in hidden table/sheet, then using variant array to iterate over data set is very fast (as long as you don't do nested loops), typically single iteration over 1mil record will take about 1 sec or less (depending on your machine spec).
 

Logit

Active Member
Thank you for your answers.

How do I get this (which works) :

Code:
Sub getarr()

Dim nameList() As Variant
Dim i As Integer
ReDim Preserve nameList(2)

nameList(0) = "AB1CDE Jones John K  John    K  Jones  Sr. 123 Main Street Anywhere    Mo. 54685"
nameList(1) = "W5HA Jones, Mary P  Mary    P  Jones      1632 4th Ave    MyTown  Ca. 66859"
nameList(2) = "K3EDR  Able, Tim I Tim I  Able    Jr  #2 16th Street North    Miami  Fl. 50421"

i = InputBox("Enter #", "Query List")
    MsgBox (nameList(i))

End Sub

To this (which doesn't work) :

Code:
Sub getarr()

Dim nameList() As Variant
Dim w As String
ReDim Preserve nameList(2)

nameList("AB1CDE") = "AB1CDE Jones John K  John    K  Jones  Sr. 123 Main Street Anywhere    Mo. 54685"
nameList("W5HA") = "W5HA Jones, Mary P  Mary    P  Jones      1632 4th Ave    MyTown  Ca. 66859"
nameList("K3EDR") = "K3EDR  Able, Tim I Tim I  Able    Jr  #2 16th Street North    Miami  Fl. 50421"

w = InputBox("Enter ID", "Query List")
    MsgBox (nameList(w))


End Sub
Instead of query on a number, query on the ID string of the person ? I've been researching websites on ARRAYS but haven't found an answer yet.
 

Chihiro

Excel Ninja
Array must be accessed via index of row/column elements.
So, if you want, you'd create 2d array, first column holding key, 2nd holding the value. Then, loop array row index, check for key column for condition, return 2nd column element of same row index.

If you want to use Key/Value pair. You can either use collection or dictionary object. Which is much easier and probably faster than looping on array.

Have a quick read of...
http://analystcave.com/excel-vba-dictionary-arrays-and-other-data-structures/
 

Marc L

Excel Ninja
How do I get this (which works)
Code:
Sub getarr_revamped()
    Dim nameList$(), S$
    nameList = Split("AB1CDE Jones John K  John    K  Jones  Sr. 123 Main Street Anywhere    Mo. 54685¤" & _
                    "W5HA Jones, Mary P  Mary    P  Jones      1632 4th Ave    MyTown  Ca. 66859¤" & _
                    "K3EDR  Able, Tim I Tim I  Able    Jr  #2 16th Street North    Miami  Fl. 50421", "¤")
    S = InputBox(vbLf & "Enter # :", "  Query List")
    If S > "" Then MsgBox nameList(S)
End Sub

To this (which doesn't work)
Code:
Sub getarr2()
         Dim V, Dic As New Collection, S$
    For Each V In Split("AB1CDE Jones John K  John    K  Jones  Sr. 123 Main Street Anywhere    Mo. 54685¤" & _
                        "W5HA Jones, Mary P  Mary    P  Jones      1632 4th Ave    MyTown  Ca. 66859¤" & _
                        "K3EDR  Able, Tim I Tim I  Able    Jr  #2 16th Street North    Miami  Fl. 50421", "¤")
        Dic.Add V, Split(V)(0)
    Next
        S = InputBox(vbLf & "Enter ID :", "  Query List")
        If S > "" Then MsgBox Dic(S)
        Set Dic = Nothing
End Sub
Do you like it ? So thanks to click on bottom right Like !

★ ★
Both procedures crash in case of a bad entry
(number or ID not matching the array or collection)
without any trapping error statement …

★ ★
Easier than a collection on the same principle is a Dictionary
(to see in VBA help, in threads of forums or in any tutorial)
but it works only under Windows.
I created some dictionary for MAC/PC just using a Collection object
adding the same methods as a Dictionary object and even more …
 

Logit

Active Member
Here is my attempt at a Dictionary.

Code:
Sub TryDictionary()

Dim dict As Object 'Declare the Dictionary object
Set dict = CreateObject("Scripting.Dictionary") 'Create the Dictionary
Dim key, val

key = "W5HA": val = "W5HA Jones, Mary P  Mary    P  Jones      1632 4th Ave    MyTown  Ca. 66859"
key = "K3EDR": val = "K3EDR  Able, Tim I Tim I  Able    Jr  #2 16th Street North    Miami  Fl. 50421"
key = "AB1CDE": val = "AB1CDE Jones John K  John    K  Jones  Sr. 123 Main Street Anywhere    Mo. 54685"

key = InputBox("Enter Term", "Enter")
MsgBox val


'Dispose of VBA Dictionary
Set dict = Nothing

End Sub
The ' Set dict = Nothing ' does not clear anything. When I perform a search it continues displaying the last item in the dictionary :

"AB1CDE Jones John K John K Jones Sr. 123 Main Street Anywhere Mo. 54685¤"

The resource sites, if I understood them correctly, state the ' = Nothing ' would reset the Dictionary allowing a new query ?

Help me understand where I went wrong. Thank you !
 

Logit

Active Member
Ok ... this seems to be working. If I add another 100,000 to this list, is it going to crash or will it continue to work as desired ? What changes, if any, do I need to make ?

Code:
Sub TryDictionary()

Dim dict As Object 'Declare the Dictionary object
Set dict = CreateObject("Scripting.Dictionary") 'Create the Dictionary
Dim key, val

dict.Add "W5HA", "W5HA Jones, Mary P  Mary    P  Jones      1632 4th Ave    MyTown  Ca. 66859"
dict.Add "K3EDR", "K3EDR  Able, Tim I Tim I  Able    Jr  #2 16th Street North    Miami  Fl. 50421"
dict.Add "AB1CDE", "AB1CDE Jones John K  John    K  Jones  Sr. 123 Main Street Anywhere    Mo. 54685"

key = InputBox("Enter Term", "Enter")
val = dict.Item(key)

MsgBox val

'Dispose of VBA Dictionary
Set dict = Nothing
End Sub
 

Marc L

Excel Ninja
It depends on the available Excel memory …

First activate the Microsoft Scripting Runtime reference
- via the VBE Tools menu -
then paste next code to the module ThisWorkbook :​
Code:
    Dim Dic As New Dictionary

Sub Demo1()
    Dim S$
     If Dic.Count = 0 Then
        Dic("W5HA") = "W5HA Jones, Mary P  Mary    P  Jones      1632 4th Ave    MyTown  Ca. 66859"
        Dic("K3EDR") = "K3EDR  Able, Tim I Tim I  Able    Jr  #2 16th Street North    Miami  Fl. 50421"
        Dic("AB1CDE") = "AB1CDE Jones John K  John    K  Jones  Sr. 123 Main Street Anywhere    Mo. 54685"
     End If
        S = InputBox(vbLf & vbLf & "Enter Term :", "  Enter")
        If Dic.Exists(S) Then MsgBox Dic(S)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Dic.RemoveAll
    Set Dic = Nothing
End Sub
★ ★ Do you like it ? So thanks to click on bottom right Like ! ★ ★
 

Logit

Active Member
Marc L :

Thank you for your assistance. Your code works well.

What issue/s might I encounter using my last macro ? You mentioned "Excel Memory".

Just trying to understand for future reference.

Thanks.

Also, do I simply keep adding more lines to the Keys (Dic) ? And approximately, how many more lines can I add before things get "hairy" ?
 

Chihiro

Excel Ninja
Hard coding values in code, you are more likely to hit module size limitations.
In general when "compiled" code for a module exceed 64k, it's been known to misbehave (mostly crashing Excel).
This is undocumented by MS as far as I know, but reported by several MVPs (among which is Chip Pearson).
 

Logit

Active Member
Searching a few websites, they seem to indicate copying the module code and pasting into a text file (Notepad) then seeing how large the text file is ... is a way of determining the module's size.

Must be a more accurate means ?
 

Marc L

Excel Ninja
I regularly used collection or dictionary around 100 000 items.
For huge data (400 000+) I prefer using a collection running faster.
It's not only the number of items but the number of characters stored …

I never hardcode so many items, better is to use a hidden worksheet
even encrypted or an external file as a database (even encrypted too).
 

Logit

Active Member
The following line has approx. 100 characters (including spaces).

Dic("AB1CDE") = "AB1CDE Jones, John K, 123 Main Street, Anywhere, Mo. 54685"

Probably all of the lines will average the same, plus or minus maybe two or three characters.

So from your experience how many lines would you guess ?
 

Marc L

Excel Ninja
As a collection is 6 times faster (2 times at least) than a dictionary
for loading a million of items on my end …

At 100 000 a dictionary is a bit faster but at 200 000 a collection wins !

Edit : it depends on how the code is written, see next posts …
 
Top