Solved: Modify Array in Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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?
 
Joined
Jul 25, 2004
Messages
5,456
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. :)
 

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
 
Joined
Jul 25, 2004
Messages
5,456
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?
 

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.
 
Joined
Jul 25, 2004
Messages
5,456
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.
 

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?
 
Joined
Jul 25, 2004
Messages
5,456
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.
 

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.
 
Joined
Jul 25, 2004
Messages
5,456
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.
 
Joined
Jul 25, 2004
Messages
5,456
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.
 

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?
 
Joined
Jul 25, 2004
Messages
5,456
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top