Tl dr - Collections seem very similar to arrays.An individual content control. If so, is there any benefit to a collection over an array (or vice versa) for things like this? By extension, could you also make an array of ranges, sheets, and books, too? Which shows me that you can make an array out of files. 'Do some stuff with the open workbook here 'Loop through selected files, put file name in A1, paste as values 'If user clicks cancel, exit sub rather than throw an error
'Build the array of FileNames to pull data from, note filetype specifiedįileNames = Application.GetOpenFilename(filefilter:="Excel Files (*.xlsx), *.xlsx", MultiSelect:=True) 'FileNames is array of file names, file is for loop The gist of it being: Sub Loop_Thru_Select_Files()
#Vbag collection code
Someone a little while back posted some code that I've added to my file of clever things and have used a few times since. Hope this helps out a bit! I know it's incomplete and if you have any questions/suggestions let me know! Set fso = CreateObject("Scripting.FileSystemObject")įor Each oSubfolder In oFolder.SubFolders This has a pretty cool find/findnext feature in it as well.ĭig through folder/subfolder/files: link to author Dim fso, oFolder, oSubfolder, oFile, queue As Collection Loop While Not Type_Match Is Nothing And Type_Match.Address First_Address With Sheets("Your Sheet").Range("Your Range") Looping Through a Collection For Each Item in aCollectionįind something and perform an action: Sub Find_Add_DoSomething() In the same way remove from: aCollection.Remove aKey this includes ranges, sheets, books, and files. It's important to note that you can add any variable type to a collection. Keys are not necessary and for most applications will not be used: aCollection.Add aItem To add an item with a key: aCollection.Add aItem, aKey The neat thing about collections is you can dim & set at the same time: Dim aCollection as New CollectionĪdding to a collection is quite simple and it involves Items and Keys. Setting a collection is just as simple: Set aCollection = New Collection This thread is written on the hopes that an average VBA'er will be able to understand and apply collections to their code.ĭeclare a collection simply: Dim aCollection As Collection When I discovered collections and the power they had, it changed my approach to VBA drastically. Recent ClippyPoint Milestones !Ĭongratulations and thank you to these contributors DateĪ community since MaDownload the official /r/Excel Add-in to convert Excel cells into a table that can be posted using reddit's markdown.Ĭollections are not only easy to use, but can be utilized in a wide variety of applications.
Include a screenshot, use the tableit website, or use the ExcelToReddit converter (courtesy of u/tirlibibi17) to present your data. You can select code in your VBA window, press Tab, then copy and paste into your post or comment. To apply code formatting Use 4 spaces to start each line This will award the user a ClippyPoint and change the post's flair to solved. OPs can (and should) reply to any solutions with: Solution Verified Only text posts are accepted you can have images in Text posts.Use the appropriate flair for non-questions.Post titles must be specific to your problem.
0 kommentar(er)
