1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'VBA Macros' started by Logit, Jul 15, 2018.

  1. Logit

    Logit Active Member

    Messages:
    259
    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 ?
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,571
    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 (vb):

    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
    Chirag R Raval likes this.
  3. Logit

    Logit Active Member

    Messages:
    259
    Sample data attached.

    There will be 1 million rows of similar formatted data.

    Attached Files:

  4. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,571
    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 (vb):

    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
    Chirag R Raval likes this.
  5. Logit

    Logit Active Member

    Messages:
    259
    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 ?
  6. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,571
    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
    Chirag R Raval likes this.
  7. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Any sample ? …
  8. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,571
    Marc look in Post #3 above
  9. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    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).
    Chirag R Raval likes this.
  10. Logit

    Logit Active Member

    Messages:
    259
  11. Marc L

    Marc L Excel Ninja

    Messages:
    4,253


    No matter, you already had a way since post #2 …​
  12. Logit

    Logit Active Member

    Messages:
    259
    Thank you for your answers.

    How do I get this (which works) :

    Code (vb):
    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 (vb):
    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.
  13. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    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/
  14. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Code (vb):
    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

    Code (vb):
    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 …
  15. Logit

    Logit Active Member

    Messages:
    259
    Here is my attempt at a Dictionary.

    Code (vb):

    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 !
  16. Logit

    Logit Active Member

    Messages:
    259
    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 (vb):
    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
  17. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    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 (vb):
        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 ! ★ ★
  18. Logit

    Logit Active Member

    Messages:
    259
    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" ?
  19. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    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).
  20. Logit

    Logit Active Member

    Messages:
    259
    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 ?
  21. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    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).
    Chirag R Raval likes this.
  22. Logit

    Logit Active Member

    Messages:
    259
    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 ?
  23. Marc L

    Marc L Excel Ninja

    Messages:
    4,253


    I yet tried close to a million with success …​
  24. Logit

    Logit Active Member

    Messages:
    259
    Wonderful. Thank you Marc.
  25. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    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 …
    Chirag R Raval likes this.

Share This Page