Tag: Spreadsheets

Categories
Software

How To Fix “Invalid Property Assignment (Error 450)”

If you are testing the return value of a function in the immediate window in VBA and get the following error:

Wrong number of arguments or invalid property assignment (Error 450)

What you are doing is something like this:

Function GetCollection() As Collection
    Dim coll As New Collection
    coll.Add "Hello"
    coll.Add "World"
    Set GetCollection = coll
End Function

Then in the immediate window typing:

? GetCollection()

The code appears to work fine, but the error is a mystery. The reason for the error is that the immediate window call expects a collection, but isn’t receiving one.

Therefore, to properly test your functions returning a collection data type create a test subroutine that calls the function and receives the collection returned. Such as:

Sub Test()
    Dim a As Collection
    Set a = GetCollection()
    Debug.Print a.Item(1)
End Sub

Then in the immediate window enter the name of the subroutine just created:

Test

You should then see the following output:

Test
Hello

Conclusion

When testing functions that return a Collection data type instead of using the immediate window defer instead to calling them from a test subroutine. Then output the desired response to visually check your code works, or instead of printing to the immediate window, use Debug.Assert like this:

' Debug.Print a.Item(1)
Debug.Assert a.Item(1) = "Hello"

If there’s a problem with your function when the subroutine is ran in the immediate window it will break at the failed assertion test.

Either way, learning this has helped brush up my rusty Excel VBA skills as I jump back into Excel VBA 2016.

Categories
Cloud Apps

How To Join Multiple Columns Into 1 Sorted Unique Column

Further to my previous post on combining 2 columns into one column I’ve had to do some further work on this by now combining a number of unknown columns into one unique column and then sorting the result.

I’ve been able to achieve this and will demonstrate one way in how I’ve been able to get it to work. Let’s use a working example to show how it works.

First, we’ll acquaint ourselves with the old method of using this formula, which in this case would be the following for the following three columns (column A will be our results column):

=unique(transpose(split(join(";",A:A)&join(";",B:B)&join(";",C:C),";")))
Join multiple columns
Here is the data set we will be working with

Unfortunately the problem with this method is that is requires us to enter in the join(delimiter,range) function for each new column, and I want the function to be able to concatenate all cells in a range of columns.

Our first remedy at finding a solution avails itself when using the ArrayFormula function. As this function will loop through each of the cells in your array (being a range of cells) and will output each cell according to the way you define the function inside.

As an example, if we were to apply the following formula in cell A1 we would get the following:

=arrayformula(concatenate(if(len(B:D)>0,B:D&";","")))
ArrayFormula Concatenate Results
Use ArrayFormula to concatenate results into a string

As you can see what happens in the above function is that the ArrayFormula goes through my range B:D (being everything in columns B to D) and once it finds a cell that contains something (a string length greater than 0) it then gets that result and appends a semi-colon to it – this is denoted with the true statement in the if statement by B:D&";".

Now we simply merge the two formulas together, in essence replacing the join section of our previous formula with the ArrayFormula above, producing:

=unique(transpose(split(arrayformula(concatenate(if(len(B:D)>0,B:D&";",""))),";")))
Merge Multiple Columns Into 1
Merge multiple columns into 1 column

Lastly, if we wanted to sort the result of this output we would finally wrap the resulting formula in the sort function, where we would have everything sorted from A to Z:

=sort(unique(transpose(split(arrayformula(concatenate(if(len(B:D)>0,B:D&";",""))),";"))))
Join Multiple Columns Sorted
Join multiple columns together and sort results

Of course if I didn’t know how many columns I would be expecting in this sheet I would amend the last column name to a much large letter, eg.

=sort(unique(transpose(split(arrayformula(concatenate(if(len(B:Z)>0,B:Z&";",""))),";"))))
Extend Column Width
Extend more columns into the join as necessary

However, do be aware processing time will increase with the more data columns you add.

Categories
Cloud Apps

How To Easily Join 2 Columns Into 1

UPDATE (4 Nov 13): I’ve created another post that allows you to perform the same task demonstrated below, but works better with multiple columns (rather than just two as detailed below).

There are times within a Google Spreadsheet (or I guess any spreadsheet for that matter) where you need to merge two columns together to form one column. It comes in really handy when you are looking to use a series of cells for data validation and both bits of data span over two separate columns.

Just how can we get them into one column?

I had a case recently where I needed to append the contents of one column underneath the contents of another column, with the size of both column’s height unknown (this eliminated the easier approach of simply placing the contents underneath manually).

I was able to find a solution, and I’ll illustrate how it worked by using an example. Let’s assume the following columns of data:

2 Columns of Data
Appending two columns of data, our example

The first thing we will do is move to the next adjacent column, column C, and in cell C1 enter the following formula:

=join(";",A:A)&join(";",B:B)

What this formula does is mesh all the data in the first column into a single string where each cell’s content is appended together with a semi-colon. Now if your data contains semi-colons you will want to use another symbol that is not used in your data set.

Our output in cell C1 would look a little something like this:

Lions;Tigers;Elephants;Lemurs;ABC;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;Red;Yellow;Green;Blue;White;Black;ABC;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

Now we’re going to amend our code in C1 so that we can split each item back into their own cell, our code in cell C1 will now look like so:

=split(join(";",A:A)&join(";",B:B),";")

This would nor produce something a little like this:

Appending Columns of Data
Appending two columns together into one

Then as we need the data to be placed into a column we would transpose our data, by amending our cell in C1 to this:

=transpose(split(join(";",A:A)&join(";",B:B),";"))

Giving us:

Joining 2 Columns Together
Appending one column onto another

Now if we want to remove any pieces of data that are the same within both columns we would just further wrap the UNIQUE function around the TRANSPOSE function. In our working example this would remove the second ABC in our new column. Here’s the formula firstly:

=unique(transpose(split(join(";",A:A)&join(";",B:B),";")))

Resulting in:

Unique Merged 2 Columns
Merging two columns together and returning only unique values

This has certainly helped me when operating with ImportRange function calls and then working with the imported data in the active sheet. Hopefully it helps you too!