Home:ALL Converter>VBA Excel - Assigning multiple values to an array using a single variable

VBA Excel - Assigning multiple values to an array using a single variable

Ask Time:2018-06-08T22:08:19         Author:Geminiflipflop

Json Formatter

For my issue i have checked the archive and there is a similar thread but my issue is slightly different and i cant seem to be able to tweak my code to make it work. The similar one is here: Excel 2010 VBA: How to store an array of worksheets as a variable?

I am trying to assign a series of tabs to an array using a variable. The variable uses a value in a cell on another sheet.

Dim VendorTabsArray As Variant

VendorTabs= Sheets("OtherSheet").Range("A1").Value
VendorTabsArray = Array(VendorTabs)


The actual value in the cell A1 i wish to use as variable to put into the tab array is "EBS - Add User", "EBS - Modify User", "EBS - Delete User", "EBS - Re-Enable User", "EBS - Password Reset", "EBS - Access Profiles Roles", "EBS - Reporting"

This give me a Subscript out of range error.

When i add a watch to VendorTabArray, it does not seem to recognize the quotes and commas in the variable and just adds the whole list of tabs into the first array position instead of splitting them out.

Does any know how i can select these tabs using an array from a variable whereby the variable is a cell reference that contains all required tabs?

Any help is very much appreciated as it has me baffled.

Author:Geminiflipflop,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/50762485/vba-excel-assigning-multiple-values-to-an-array-using-a-single-variable
Vityata :

Start with something small that works:\n\nPublic Sub TestMe()\n\n Dim someArray As Variant\n someArray = Array(\"EBS - Add User\", \"EBS - Modify User\") \n ActiveWorkbook.Sheets(someArray).Select\n\nEnd Sub\n\n\nThen try to read from the worksheet and localize the problem:\n\n\n\nPublic Sub TestMe()\n\n Dim someString As String\n someString = Range(\"A1\")\n\n Dim someArray As Variant\n someArray = Split(someString, \",\")\n\n ActiveWorkbook.Sheets(someArray).Select\n\nEnd Sub\n\n\nThe problem is visible in the values of someArray:\n\n\n\nThen you need some \"cleaning\". Thus, you create a second array, which removes the useless \" and trims a bit the values:\n\nPublic Sub TestMe()\n\n Dim someString As String\n someString = Range(\"A1\")\n\n Dim someArray As Variant\n someArray = Split(someString, \",\")\n\n Dim someNewArray As Variant\n ReDim someNewArray(UBound(someArray))\n Dim cnt As Long\n\n For cnt = LBound(someNewArray) To UBound(someNewArray)\n someNewArray(cnt) = Trim(Replace(someArray(cnt), \"\"\"\", \"\"))\n Next cnt\n\n ActiveWorkbook.Sheets(someNewArray).Select\n\nEnd Sub\n\n\nNow it works:\n\n",
Matt Gaydon :

What you want is a split function. It will read a string and split the string at certain characters and store each split as an entry of an array base (0). See below:\n\nDim VendorTabs, VendorTabsArray As Variant\n\nVendorTabs = Sheets(\"Other Sheet\").Range(\"A1\").Value\nVendorTabsArray = Split(VendorTabs, \",\")\nFor t = LBound(VendorTabsArray) To UBound(VendorTabsArray)\n VendorTabsArray(t) = Trim(VendorTabsArray(t))\n MsgBox VendorTabsArray(t)\n ActiveWorkbook.Sheets(VendorTabsArray(t)).Select\nNext\n\n\nThis sets a variable equal to the cell that contains every tab, then the split function automatically creates an array base (0) with the entries and stores it at the specified variable you give it. Then we use the For-loop to run through the lower bound of the array (0) to the upper bound (however many tabs you have minus 1), we trim the array values (to get rid of the extra space before and after commas) and then the message box simply prints what the array value is. This exists so you can check to see if the array is working properly.\n\nYou can replace the message box with any functionality you want the array to do. I don't believe you specified what you wanted to do once the array was created, but whatever you want to do, that's where you'd put the code. \n\nUPDATE: I see where you'd like it to select the worksheets of the tab array. I've added that to the code.",