Alternative to using CreateObject("system.collections.arraylist") in VB due to not being able to use .Net 3.5

  • Original post, Create button to run formula on every sheet in workbook.

    This is the code from the module to randomly pull tickets from a list of tickets on each sheet in the workbook.

    I have a need to change the code as I cannot use CreateObject("system.collections.arraylist")

    I'm working on a company computer and .Net Framework 3.5 isn't installed, which I found is a requirement for the arraylist. I can't install .NET myself, and I'm not sure if the company would push it corporate wide since others want to use this spreadsheet as well. So I need to find an alternative.

    I read about using VB arrays or collections as an alternative, but got lost trying to convert the code to use a VB array or collection over the arraylist.

  • The Dictionary might work for you. It also has the Add and Exists (similar to Contains) methods so would require minimal code change there.

    However, neither the Dictionary nor the Collection has a ToArray method so you would have to rewrite that line to explicitly extract all the items to the desired range.


    Making the world a better place one fret at a time | | |·| |·| |·| |·| | |:| | |·| |·|

  • The Dictionary.Items property returns an array though. ;) (as does Keys)

    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

  • Forgive me as I am still learning much of these topics. I modified my code to the following. It appears to work in that it does not give me any runtime errors. But it puts excel in a not responding stated since I probably put it in some loop it does not exit from. I was not sure if this is correct as the dictionary was looking for a key, value pair. When I had the line as .Add x(ii,1) it gave an error about not enough parameters. So I changed that line to below.

    .Add x(ii, 1), x(ii, 1)

    Here is the whole code as it stands now. Commented out what was there originally to keep it for historical purposes, for now. Basically the code should select 4 random cells from each sheet in the workbook and then output those random cells into the range H2:K2. I've not ventured into this territory in excel to work with arrays and the like.

  • You have the line commented out to copy the items to the worksheet (line 22). I fixed that and your code ran OK for me.

    However, the problem could be in your data. This code will randomly choose cells in column B until it has found four unique values. If it contains many cells but many duplicate values, it could take some time to find four unique values. If it does not contain four unique values, your code will be in an infinite loop. Remember also that RANDBETWEEN can return the same number more than once. Can you show a sample of the data for the sheet where it gets stuck? You could add some Debug.Print statements at key points to trace what is going on.

    I rewrote your code to eliminate that GoTo, an undesirable practice:


    Making the world a better place one fret at a time | | |·| |·| |·| |·| | |:| | |·| |·|

  • Thanks. I forgot I commented out the one line that would print the values. Substituted your code as well and it worked great. Thanks.

    I found out what was happening on my end. There was one sheet that only had two rows of data. I thought I removed it, but I guess I did not. Once I deleted that sheet, the code ran without issue.

Participate now!

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