Return an array from a function within a sub

I'm somehow new to excel VBA and I'm stuck with something that should be super easy and I hope you can help me.

I have a Sub (called test() in this case) and I want there to get an array from my function ReturnArray().

But all I get is "Run-time error '13': Type mismatch" when I try to run the sub.

Thanks in Advance.

Function ReturnArray() As Variant
    Dim Accounts As Variant
    Accounts = Array(1, 2, 3, 4, 5, 6, 7, 8, 9,)

    ReturnArray = Accounts
End Function

Sub test()
    Dim acc() As Variant
    acc = ReturnArray
    Debug.Print acc
End Sub

Answers


Dim acc() As Variant declares acc as an array of variants but you are returning a single variant from your ReturnArray function. That single variant happens to contain an array. But just one array.

Dim acc As Variant

Now with that said, you are now going to get a runtime error on Debug.Print acc because you cannot print out a whole array on one line. You need to loop over all the elements and print them one at a time. You can turn all the array elements into one string by joining them all together using the Join function: Debug.Print Join(acc, ",")


Need Your Help

Publishing site using Web Matrix (install SQL Server Management Objects (SMO))

asp.net sql-server-2008 umbraco webmatrix

I have a problem with publishing a blog (Umbraco CMS) using web matrix. It gives me this message

Check pixel color and trigger a bash script

osx bash applescript compare pixel

How can we check the color of a determined pixel(x,y) and, if it is equal to a certain value, run a bash script.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.