1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Modify Array in Excel

Discussion in 'Business Applications' started by Carlos_Mendoza, Nov 12, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. Carlos_Mendoza

    Carlos_Mendoza Thread Starter

    Joined:
    Nov 12, 2011
    Messages:
    124
    I am totally new to Excel advanced features, but have recently started using Excel forms. What I would like to do is to create an array in VBA and then be able to add an item to the array, or to remove an item from the array without having to enter into the VBA coding each time a change has to be made. How would one of you Excel advanced users suggest performing this?
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there, welcome to the board!

    Can you give us a little more specific information about your data and what you're trying to achieve? What code are you using? What are you trying to change? When do you want to make changes? Please try to be more specific, we don't have enough information to help you right now. The more specific you are, the more detailed of an answer/solution you will get. :)
     
  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
  4. Carlos_Mendoza

    Carlos_Mendoza Thread Starter

    Joined:
    Nov 12, 2011
    Messages:
    124
    Zack, I open, and print numerous workbooks that others enter data into every Tuesday morning, and these workbooks change every two weeks. I have an array that opens the workbooks (working on code that will auto-print them for me):
    Code:
    Dim sDate As String
    sDate = Format(Date, "ddd")
    Dim varBook
    Dim varBooks
    If uNamae = "Excel User Name Goes Here" and sDate = "Thu" Then
    varbooks = Array ("Name of workbooks in Array")
    For Each varbook in Varbooks
    Set wb = Workbooks.Open(Filename:="P:\" & varbook
    'working on code that will auto print
    Next varBook
    End Sub
    
    I am wanting a way to change those workBooks other than actually enter the VBA, and manually change them. I don't know what options I have to perform this. I was looking for options as to how alter the workbooks in the array without having to change the VBA code each time.

    Rollin_Again, that post/solution looks like it could work but I don't understand the coding you used in your sample, so I wouldn't be able to effectively modify it to cater to my specific needs :( Thank you for the time to post a possible solution though. Actually, Rollin_Again if I used the code here to "Add on the fly" or "Remove from array" would that code work? Nothing wrong with your code, this just has multiple comments in it, so it is easy for me to follow:

    http://patorjk.com/programming/tutorials/vbarrays.htm#addingnewelements
     
  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    So you want to change your VBA code? Why not have a list somewhere that you could alter as need be? Or even have a full list of files you just pick from?
     
  6. Carlos_Mendoza

    Carlos_Mendoza Thread Starter

    Joined:
    Nov 12, 2011
    Messages:
    124
    Yes, I want to change the VBA code...apologies if I am not using the proper terminology. I have a list in my VBA code with the varBooks, .is that not what you are talking about? A full list somewhere could get pretty lenghty as there are over 1200 different choices that it "could" be, and maybe time consuming having to fan through the list to choose the only the ones that I am needing.
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    This is probably the best source on the internet for doing anything in the VBE with VBA...

    http://www.cpearson.com/Excel/vbe.aspx

    If you knew what you were looking for exactly (or could logically find it) and new what you wanted to replace it with, you can do it with this code. Give some specifics about where the code is located, examples of code to change from/to, and we could probably help with some code for you.
     
  8. Carlos_Mendoza

    Carlos_Mendoza Thread Starter

    Joined:
    Nov 12, 2011
    Messages:
    124
    What if the code I posted above:
    Code:
    Dim sDate As String
    sDate = Format(Date, "ddd")
    Dim varBook
    Dim varBooks
    If uNamae = "Excel User Name Goes Here" and sDate = "Thu" Then
    varbooks = Array ("Gamma", "Alpha", "Delta", "Omega")
    For Each varbook in Varbooks
    Set wb = Workbooks.Open(Filename:="P:\" & varbook
    'working on code that will auto print
    Next varBook
    End Sub
    
    And from the Array I wanted to Add: "Sigma", "Theta" to the Array, and Remove: "Omega" from the Array. How could I accomplish that?
     
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Before we get to that point, how do you know whether you want to Add, Replace, or Delete? How do you want to specify that? We can do input boxes for each if you'd like. Or even make a userform of it.
     
  10. Carlos_Mendoza

    Carlos_Mendoza Thread Starter

    Joined:
    Nov 12, 2011
    Messages:
    124
    I receive an email with all the workbooks with new data in them (from the people performing the data entry), and from there, I add/delete. A userForm would be ideal since that is how the data entry is being done, I feel like that would make it easier so when I can finally hand this little project off to someone else, it won't be a completely new process for them.
     
  11. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    If it's being done via userform, why can't you keep the list on a worksheet and just read that? Then you don't have to edit code, you just have to edit worksheet cells? That would be much easier.
     
  12. Carlos_Mendoza

    Carlos_Mendoza Thread Starter

    Joined:
    Nov 12, 2011
    Messages:
    124
    How would I reference the "Array" from a worksheet? Does my question make sense...
     
  13. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    You can populate an array from cells, that's not a problem. Editing code in the VBE via VBA should be done very, very carefully, as you have such a higher probability of failure than you do any other time. IMHO you should make robust code to overcome your issue(s) as opposed to editing the code itself.
     
  14. Carlos_Mendoza

    Carlos_Mendoza Thread Starter

    Joined:
    Nov 12, 2011
    Messages:
    124
    IMHO? What does that stand for? Adding the workbook names into a worksheet and having the Array reference there seems easiest way to have workbooks added/removed with no issues. :) How would I reference those cells?
    Code:
    varbook = Array(Sheet1!A1-Sheet1!A6
    
    I am taking a shot in the dark, but is the code above it would be done?
     
  15. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    IMHO = In My Humble Opinion :)

    Here is an excellent source (again, from Chip Pearson's website): http://www.cpearson.com/Excel/VBAArrays.htm

    Here is an example of how to add items from a worksheet to a VBA array:

    Code:
    Sub AddItemsToArray()
    
        Dim rCell As Range
        Dim varBook() As Variant
        Dim vItem As Variant
        Dim i As Long
        Dim s As String
        
        i = 1
        With ThisWorkbook.Sheets("Sheet1")
            varBook = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Value
        End With
        
        For Each vItem In varBook()
            s = s & vItem & vbNewLine
        Next vItem
        
        MsgBox Trim(s)
        
    End Sub
    This assumes you have a header in A1 and the names of the workbooks start in A2 and go down from there.

    HTH
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved Modify Array
  1. Pedro14
    Replies:
    12
    Views:
    431
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1026601

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice