Get worksheet as collection excel vba

Question

I'm building a basic Form Macro in excel. The user is passing the workbook object name i.e. the full file path and the worksheet name via my form. The macro needs to then open the activate the workbook, locate and activate the worksheet and then copy all the data on this sheet into a collection. The collection will be passed via ByRef or ByVal to another sub. I'm not efficient with Microsoft Excel VBA re its libraries, I work in a vb.net environment, the below code is what I would run in vb.net to get my worksheets data as a collection. You'll note in the code below there's three functions being used, GetWorksheet(), GetClipboardText() & ParseDelimSeparatedVariables(). Hoping someone may have a suggestion on how to recreate this is Excel VBA.

Assembly References: System.Data.dll, System.Xml.dll, System.Drawing.dll, System.Windows.Forms.dll

Namespace Imports: System, System.Drawing, System.Collections.Generic, System.IO, Microsoft.VisualBasic, System.Windows.Forms, System.Data, System.Diagnostics, System.Text, System.Threading, System.Runtime.InteropServices

Dim ws as Object = GetWorksheet(handle, workbookname, worksheetname, False)

' Do we have a sheet?
sheetexists = ws IsNot Nothing
' No sheet? No entry.
If Not sheetexists Then Return

ws.Activate()
ws.UsedRange.Select()
ws.UsedRange.Copy()

Dim data As String = GetClipboardText()
                
worksheetCollection = ParseDelimSeparatedVariables(data, vbTab, Nothing, True)

Below is the VBA code I've now got in place, currently getting a runtime error 9 subscript out of range error.

Sub getWSCol()

Dim wb As String
Dim wsName As String
Dim Arr() As Variant
Dim v As Variant
Dim colMailMerge As New Collection
        
wb = txtbxworkbook
wsName = txtbxworksheet
            
Workbooks(wb).Worksheets(wsName).Activate
        
Arr = Worksheets(wsName).UsedRange.Value
       
For Each v In Arr
    col.Add v
Next v
    
End Sub

Image below of stepping through code

Dave Scott Dave Scott · Accepted Answer · 2021-05-06T10:59:57

VBA lacks a lot of functions that make your life easy, so need to roll them all yourself.

Wouldn't a collection just result in a single dimension list of all the cell text? An array is probably better use case.

Dim Arr() as Variant
Arr = ws.UsedRange.value

I think if want a collection you'd have to loop through an array, can do it easily by then just

Dim Col as new Collection
Dim v as Variant
for each v in Arr
    col.add v
next v

Getting worksheet, need to just reference by name in the Application.Workbooks(name).Worksheets(name) objects. It'll error if not there so need to what it on a function and error resume next past the errors, then can test if nothing outside of function.

For the sheet...

Dim Wk as workbook, Ws as worksheet
On error resume next
set wk = application.worksbooks(Wkbkname)
set ws = wk.worksheets(wsname)
on error goto 0
if ws is nothing then msgbox "Doesn't exist"

Arr = ws.UsedRange.Value

Get worksheet as collection excel vba

1 Answers