VBA Rubberducking

12:03 AM

REFRESH!

[Minesweeper] 62 Games Played. 44 Bombs Used. 8833 Moves Performed. 10 New Users

[rubberduck-vba/Rubberduck] 1 issues opened. 4 issue comments

[Zomis/Games-nodejs] 1 commits

[Zomis/Games] 1 commits

7 hours later…

6:54 AM

[rubberduck-vba/Rubberduck] MDoerner created comment on issue #5840: Error when access to edit any module in Excel VBA ide

> Do you have any other VBE addins installed? This issue looks similar to old issues caused by other addins that load a custom version of the VBA interop library that is not compatible with Rubberduck.

9 hours later…

3:41 PM

@mrr010 Just talk to the chat like you are speaking to a Rubber Ducky. By the end of your question you will have found the answer on your own. It's magic. Try it.

and if not, someone will come along, read your thinking out loud, and suggest a solution

2 hours later…

5:44 PM

Gleesh. Today I am getting "Out of memory" errors while running unit tests from RD. This is not even that complex of a solution.

5:56 PM

Grrr... I've got an Excel file with ID, value1, value2, value3, ..., value15 in rows and need to turn it into a ID, Value row for each of those 15 value columns. Additionally, there are really 5 or 6 ID columns that need to be repeated on each row.

What's the best way to do that?

Copy/Transpose won't work, because I'll get each of the ID columns listed on separate rows and I can't have that.

Is there a "simple" copy operation that would do it (for 1000s of rows) or would it take some code to do it most easily?

So you want a key\value pair table?

I think the answer is "no" there is no magic to do what you want

Basically yes, key/value pairs with a multi-column key.

You can query excel sheets with ADO but you still need to split out each column with a for loop. For each column output ID, value

Joy. Guess I'll write a bit o'code. Shouldn't be hard to bang that out.

Yeah, I've done ADO against an Excel table before.

It's good if you have an absurd dataset or one that spans across different files

I just did one to query timesheets across the whole org. It takes 60 seconds using ADO. They've been using Excel timesheets for over 20 years

6:10 PM

@FreeMan do you mean unpivoting?

Use Power Query.

Yeah, I guess unpivot is the term. I'll look into Power Query, thanks @BigBen

A: Convert matrix to 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize')

support.microsoft.com/en-us/office/…

Another way to unpivot data without using VBA is with PowerQuery, a free add-in for Excel 2010 and higher, available here: http://www.microsoft.com/en-us/download/details.aspx?id=39379 Install and activate the Power Query add-in. Then follow these steps: Add a column label to your data source a...

Depending on your version of Excel it's already built in and no downloading required...

Cool. I know that there is a ton of stuff that Power Query can do but every time I've tried to use it I find it too slow and clunky. Can't get anything done

thanks

Unpivoting is a bread-and-butter PQ function ... should be straightfoward here.

6:18 PM

Arg, why would I get error 40036 (Application- defined or object defined error) when attmpting to get a sheet by name in code? Using the locals window I can see the sheet and it's name. It's correct. Plus this code worked yesterday

@HackSlash are you referencing .worksheets("sheetname").rows() or sheetname.rows()? if the latter, you have to change the sheet name in the sheet's properties - it's not the name displayed on the worksheet tab

@BigBen hrm... I've got First, Last, Value1, Value2 and I need to turn that into 2 rows, First, Last, Value1 and First, Last, Value2. Is that doable? The simple examples there don't show this...

Yep

Just unpivot Value1, and Value2.

@FreeMan, I have an object handle on the book with a Set testDataBook = Workbooks.Open

I'm expecting the statement in the Immediate window to return "OPTIONS"

weird... Any hidden, funky, wrong-code-page type of characters in there?

No, It was working yesterday before I refactored this section of code.

The name of the sheet hasn't changed

Q: VBA- xlsm file has error 40036 with similar sheet names

6:30 PM

i have an issues with an macro that i'm writing. I have my xlsm file that i'm writing my macro in, it's creating a template that gets data from multiple spreadsheets that other people send me. However one file that i use for information is an xlsm file and is giving me problems. I believe i have ...

The error code is ... weird. Seems like something else is going on.

6:49 PM

I had read that thread before but there are no problems with my code... Until I looked at the workbook I was opening. THAT one had compile errors. Not my book where the code is running from. I fixed the compile errors on the target workbook and now it's working. Le Sigh. I never knew that compile errors in a different workbook could put a full bork on my code that is looking at the borked workbook.

Another day. Another new edge case. It seems endless.

7:27 PM

[rubberduck-vba/Rubberduck] erajtob starred us

8:09 PM

[rubberduck-vba/Rubberduck] jim-oflaherty-jr-qalocate-com added label enhancement to issue #5841: Identify things only available in 64bit, like LongLong...

[rubberduck-vba/Rubberduck] jim-oflaherty-jr-qalocate-com added label feature-inspections to issue #5841: Identify things only available in 64bit, like LongLong...

[rubberduck-vba/Rubberduck] jim-oflaherty-jr-qalocate-com added label up-for-grabs to issue #5841: Identify things only available in 64bit, like LongLong...

[rubberduck-vba/Rubberduck] jim-oflaherty-jr-qalocate-com opened issue #5841: Identify things only available in 64bit, like LongLong...

[rubberduck-vba/Rubberduck] jim-oflaherty-jr-qalocate-com edited issue #5841: Identify things only available in 64bit, like LongLong...

4 hours later…