VBA: Working with Arrays

    I am trying to educate myself on creating static multi-dimensional arrays via VBA.

    Please bare with me as my knowledge of this topic is very limited.


    Question:

    Dim [variable name]([first] To [last] ) As [Type]


    With an excel userform, I want to create the [variable name] from a textbox within the userform. (text in textbox1 would be the name of the array)

    Also, using combo boxes, I want to make the [first] To [last] values. (ComboBox1 would be [first], combobox2 would be [last])


    I would appreciate any information on the topic.

    I have done some research however, I do not have a good understanding yet on arrays.


    Thanks!

  • Go to Best Answer

    You cannot name an array using the value of a textbox. Why do you think you need to?


    In order to resize it using variables, you should declare it as dynamic first:


    Code
    dim someArray()


    then resize:


    Code
    Redim someArray(clng(combobox1.value) to clng(combobox2.value))

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    • Best Answer

    If you feel like learning about VBA Arrays, Paul Kelly has written a very good explanation:


    Excel VBA Array - The Complete Guide - Excel Macro Mastery
    This post provides everything you need to know about the Excel VBA Array. Includes a quickVBA Array reference guide and 50+ VBA Array examples.
    excelmacromastery.com


    Hope this will help :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Like 1

  • Kyp

    Selected a post as the best answer.

    At some point, you'll also want to look at Chip's pages where he put a wealth of functions for working with arrays - e.g. here: http://www.cpearson.com/Excel/VBAArrays.htm

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Like 2

    While searching the web I cam across this vba and it does what I want however...

    Is there a way to get the 2D array into a single column?

    Currently, its like this:

    0-0 0-1 0-2

    1-0 1-1 1-2

    2-0 2-1 2-2


    I would like it be this way if possible.

    0-0

    0-1

    0-2

    1-0

    1-1

    1-2

    2-0

    2-1

    2-2

    Your current version is actually missing out some of your array when it writes to the sheet. For a single column you could use:


    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login