VBA Rubberducking

Mat's Mug

03:29

@Duga no daily update?

oh, it's only in The 2nd Monitor now..

Duga

06:09

[retailcoder/Rubberduck] retailcoder pushed commit e3f3a2da to LexicalTokens

> added ModuleNode and clarified strategy for VBTreeListener

RubberDuck

13:45

Hey guys. I need some votes on this synonym please.

stackoverflow.com/tags/vba/synonyms

Per this meta

3

A: What to do about [Macros]?

We can't just burn the tag entirely because it does have a valid use and relevance for the c and c++ languages. So, we're going to need to clean up macros by hand. Which sucks. Pretty bad. But I'm willing to put some effort into cleaning this up. Hopefully some others in the community will lend a...

Chrismas007

14:17

@RubberDuck Done

RubberDuck

Thanks @Chrismas007. If we can't stop people from using [macros] it won't matter how many questions we retag. It'll happen faster than we can clean it up.

Chrismas007

@RubberDuck Just put in a suggested wiki tag page for vba-macros BUT macros-vba already has a suggested edit...

RubberDuck

vba-macros will go away. I made a mistake. The second one will be more visible. It's also my suggeseted edit in there.

Chrismas007

Meh good to have both if they are both just going to be synonymized (sp?)

RubberDuck

That's how I spell it.

Just tired of the mess.

Chrismas007

14:43

@RubberDuck 772 Qs with vba and vb.net LOL

RubberDuck

@Chrismas007 Seriously? Dafuq?

2

Mat's Mug

some people apparently don't know which language they're using.

RubberDuck

14:59

@Mat'sMug Yeah... lots of people and I just can't grasp how someone couldn't understand what language it is that they're working with. I mean, how??? HOW?!

Mat's Mug

I wonder how many are tagged with both c# and java...

RubberDuck

Hey, SO started using that yellow color for quote blocks!

1

Q: copying active sheet into another workbook: Copy Method of Range class failed

I want my code to copy the entire worksheet(SOURCE) and paste it into some other worksheet(TARGET) under other workbook(WHERE_I_WANNA_PASTE_IT) and save it. I am getting this error: Run=-time error '1004': Copy Method of Range class failed on this line: CurrentSheet.Cells.Copy Destinat...

Mat's Mug

oh nice!

RubberDuck

Betchya that just made a ton of old questions look like crap.

Mat's Mug

nah, my bet is that it just made a ton of crap-looking old questions look decent ;)

2

vba4all

15:32

this: stackoverflow.com/a/27964938/2140173

Public Sub Assign(a As Double, b As Double) vs mc.Assign(Cells(1, 1).Value) isn't that an error for not enough parameters passed?

how does that get 3 upvotes if it doesn't work :P ohh maybe im just too picky

RubberDuck

16:03

@vba4all That. Too picky. =;)-

@vba4all: Thank you for reply - I have edited my answer. — duDE 9 mins ago

Phrancis

@RubberDuck Nice, that looks better actually IMO

RubberDuck

Yeah it does.

vba4all

16:47

@RubberDuck haha quality control in between changing jobs ;P

Chrismas007

16:58

1

Q: Enter value as text to cell using VBA even if value starts with =?

I get an error on the following expression: wksWrite.Cells(lWriteRow, "F") = Trim(wksRead.Cells(lReadRow, 2)) because Trim(wksRead.Cells(lReadRow, 2)) evaluates to: =SKYDDSFOLIE FÖR SR-72 5424 I guess Excel evaluates that as a formula and doesn't allow VBA to enter it. But I want to enter t...

excel vba excel-vba

Interesting as to why CStr() method doesn't work

RubberDuck

@Chrismas007 Because it's a string anyway. CStr() is working.

Chrismas007

so you can't assign a string with an = at the front to a Range.Value?

Nevermind... looks like duplicate of stackoverflow.com/questions/6458941/…

RubberDuck

4363 questions... made a scratch... sigh

Phrancis

@Mat'sMug around?

Phrancis

17:26

Just wanted to see if you could help proof another form letter, if/when you have 5 minutes just ping me

Mat's Mug

gimme an hour or so

Phrancis

Ok thanks bro

Chrismas007

18:05

1

A: Checking algorithm with two conditions in ranges

And boom goes the dynamite. This will take an input like this: And give you this: Sub TestIt() Dim LastRow As Long, CurRow As Long, InLast As Long, FindRng As Range LastRow = Range("C" & Rows.Count).End(xlUp).Row For CurRow = 1 To LastRow If Not Range("B" & CurRow).Value = "" Then ...

@RubberDuck Want to clean that up? :P

RubberDuck

19:07

@Chrismas007 I have cleaned up at least 40 in the last two days.

stackoverflow.com/questions/tagged/macros+excel

Unfortunately, there were 20 new ones posted in that time.

Phrancis

Time to write Access SQL yay.

Mat's Mug

@Phrancis what's up?

(sorry, a little late)

Phrancis

19:22

@RubberDuck Any clue what I'm doing wrong? (Access)

RubberDuck

@Phrancis Yeah, the brackets there are telling the query to expect a parameter.

You'll probably have to query a query to do what you're trying to do.

Phrancis

So like copy & paste my subquery?

RubberDuck

Yeah. The subqueries need to be proper queries in their own right. Then another query queries those.

God I hate Access.

2

star-bait ^

Phrancis

You mean, actual saved queries, or just a plain old sub-SELECT?

Mat's Mug

@RubberDuck indeed

Phrancis

19:33

>_<

RubberDuck

@Phrancis Yes. It's Access....

Nice try though.

Phrancis

:(

Does the subquery need a group by as well maybe?

(in the main group by)

Screw it, I'll just do a damn pivot in Excel

2

Mat's Mug

lol

Phrancis

19:53

Wow, the data is FUBAR.

Mat's Mug

20:05

@StackExchange dude, @CaptainObvious beat you big time here

in The 2nd Monitor, 2 mins ago, by Captain Obvious

0

Q: Efficiently create and sort a Collection

The goal of my code is to sort data into two categories. It must use a local copy of the initial data from Collar (Top View).csv. My code creates a Collection of items called Collars using the initial data file, then moves each Collar into its respective category based upon its E dimension. I wou...

vba

Stack Exchange

2

Q: Efficiently create and sort a Collection

The goal of my code is to sort data into two categories. It must use a local copy of the initial data from Collar (Top View).csv. My code creates a Collection of items called Collars using the initial data file, then moves each Collar into its respective category based upon its E dimension. I wou...

vba

Chrismas007

Why would a flag be accepted as Helpful but the Q not closed / put-on-hold

Mat's Mug

@StackExchange about time...

@Chrismas007 good question

Chrismas007

43

A: Why does flag marking as helpful/declined not always correlate with moderator action?

Case 1: your flag was helpful, but nothing happened. Causes include: An edit or other action that occurred between your flag and the moderator reviewing it meant that moderator action wasn't required. However the moderator felt you were right at the time of flagging, and so marked your flag hel...

Phrancis

20:48

@Mat'sMug You would love this shit. They managed to load, and subsequently mark DELETED, the same exact record 9 TIMES.

And when I say a "record" in Contract_Master, it hits about 25-30 other tables each I think.

And they did similar things 27 times since September and only a few were resolved.

Mat's Mug

wow. just....... wow.

Phrancis

fml

2

(mind you, there were about 6000 more entries I used Access and Excel to filter and resolve, these are only the ones I had to check by hand)

RubberDuck

> Here is data that I would be an example. Each column is in a spreadsheet and the first column starts as 'B'

Ask and ye shall receive @ducky

Mat's Mug

there's something I'm not grasping with precision/scale.

this works:

select cast(case when f16 = '' then '0' else '0' + ltrim(f16) end as decimal(10,3)) from staging.dbo.yyf871 group by f16 order by f16

but this blows up:

select cast(case when f16 = '' then '0' else '0' + ltrim(f16) end as decimal(10,5)) from staging.dbo.yyf871 group by f16 order by f16

RubberDuck

Okay, so what's the data actaully look like?

Mat's Mug

21:02

and yet at row 307 I have 10000.000

there's empty strings, numbers starting with a dot, and then things that look like legit numbers

RubberDuck

Decimal(10,3) means 10 possible digits with a maximum of 3 decimal places, right?

Mat's Mug

I thought the scale was for either side of the comma

RubberDuck

@Mat'sMug What do you mean?

Mat's Mug

not sure

RubberDuck

128

A: How do I interpret precision and scale of a number in a database?

Numeric precision refers to the maximum number of digits that are present in the number. ie 1234567.89 has a precision of 9 Numeric scale refers to the maximum number of decimal places ie 123456.789 has a scale of 3 Thus the maximum allowed value for decimal(5,2) is 999.99

Mat's Mug

21:05

I believe the 10000 is blowing up with 10,5

RubberDuck

Shouldn't. That fits in a Decimal(10,5)

Mat's Mug

oh crap. there's a 900501

wtf... it's a "unit cost" column. nonsense.

unless that fabric is F'n platinum-coated gold fiber embroided with raw diamonds....

confirmed, data entry error

and damn, it's in a year I'm not even bothering to import.

moves derived column past the OrderDateCalendarId lookup transform

or is that shoving a DQ issue under the carpet?

Phrancis

@Mat'sMug Can't you just fix the record? Just sounds like one (or two) too many zeroes

Mat's Mug

it's from 2000, I'm only importing history for 10 years

besides IDK where the comma belongs...

Phrancis

21:20

Oh, then I guess it doesn't matter too much

Mat's Mug

I'll redirect failed conversions to the DQ database, and reject them.

Phrancis

Sounds like a plan

f16 is all string data I'm assuming? :)

Mat's Mug

everything is strings!

Phrancis

Full orchestra hehehe.

@Mat'sMug Have you had to deal much with one-to-many record relationships?

(e.g., one invoice with multiple products, etc.)

Mat's Mug

21:36

absolutely

Phrancis

I was curious if I could pick your brain a bit, hopefully to not have to spend so much time on this crap in the future

Mat's Mug

spent the day on that one (the ETL for it actually)

create table dwf.PurchaseOrders (
	 _Id int not null identity(1,1)
	,_DateInserted datetime not null
	,_DateUpdated datetime null
	,_DateDeleted datetime null
	,OrderDateCalendarId int not null
	,DeliveryDateCalendarId int not null
	,ETADateCalendarId int null
	,LastReceiptDateCalendarId int null
	,InvoiceDateCalendarId int null
	,DelayReasonId int null
	,Number nvarchar(10) not null
	,SupplierId int not null
	,MaterialId int not null
	,SupplierStyleCode nvarchar(15) not null
	,QuantityOrdered decimal(10,5) not null

@Phrancis what's up?

Phrancis

Picture this:

CREATE TABLE Contract
(
	Id INT,
	DateCreated TIMESTAMP
	-- more stuff
);
CREATE TABLE ContractProduct
(
	ContractId INT, -- FK
	Sequence INT,
	ProductId INT,
	SerialNumber TEXT
	-- more stuff
);

Mat's Mug

shouldn't there just be a ProductId on Contract?

or it's a many-to-many junction table?

Phrancis

That's the catch

Mat's Mug

21:43

What's the catch?

> 22

Phrancis

What I'm dealing with is, Contract.Id is a unique PK, but a contract can have multiple products, hence the Sequence

Mat's Mug

how about ContractHeader and ContractDetail, with a ProductId on ContractDetail?

Phrancis

So, ContractId 1 + Sequence 1, ContractId 1 + Sequence 2, etc.

@Mat'sMug Same idea, just different words for it

Mat's Mug

right

ok yeah I get it

no PK on ContractProduct?

Phrancis

It's a combined key, or whatever the word is. My example kind of sucked

ContractId + Sequence = Unique key

Mat's Mug

21:46

ok

Phrancis

Now, what I was struggling with, is that I was dealing with multiple distinct ContractId, each with multiple product sequences with serial numbers, but serial numbers are duplicated across the Contracts

So, at that point an unintentional many-to-many relationship, where the only differentiator is a flag in the Contract table, giving a status code, like 'ADD', 'CAN', 'DEL'

And my challenge was to find these distinct Contract.Id where one or more 'DEL' and 'CAN' exist, but an 'ADD' may not, or there may be multiple 'ADD'

Mat's Mug

can you mess with table structure?

Phrancis

No. Only thing I can do is import data in Excel and Access

Mat's Mug

aw

Phrancis

It would break so many things if the table structure changed, especially SNAP ;-)

Mat's Mug

21:50

lol

@Phrancis sorry I'm kinda lost there :(

Phrancis

Of course I can pull more data from SSRS and make my own table/stored query structure using Access... but, it's Access :\

Mat's Mug

yeah but your life would be easier if Header and Detail (sic) both had an ~~identity~~ autonumber PK

Phrancis

To make it even more fun, there's no guarantee whatsoever that the same serial number on two duplicate contracts will be on the same sequence

This helped a bit, had to do the rest in Excel... better write some of this stuff down for next time

select
  Serial_Number
, count(Serial_Number) as [Count_Active]
from HVAC_Duplicate_Entries
where
  Status = "ADD"
group by
  Serial_Number

HVAC_Duplicate_Entries is a SSRS report that joins Contract and Product on COUNT(Serial_Number) > 1 and a few other things

RubberDuck

I have to go. I didn't quite get through all of the code. — RubberDuck 14 secs ago

Mat's Mug

great, I'll deal with the leftovers :)

Phrancis

21:56

I wonder if VBA might be a better option than trying to do SQL... Any ideas?

Keep it in Excel and let the code do the work

Mat's Mug

if SQL fails, I'd work with Excel and VLOOKUP's before trying anything with VBA

Phrancis

VLOOKUP D:

Phrancis

22:15

I think this might be able to be done fairly simply using a couple of loops, actually... I'm going to jot some things down and try it

Phrancis

22:39

@Mat'sMug @RubberDuck Does this ^^ seem like it could be done in VBA fairly simply?

(open to suggestions to do it better, too)

Mat's Mug

pseudocode! haven't seen that since CEGEP! ...actually, since the last pseudocode CR post I've VTC'd ;)

Phrancis

Seems to help to lay it out in plain English before you start trying to hammer some code

Mat's Mug

I can only think of going back home right now...

Phrancis

Do that, I'm about 10 minutes from clocking, myself

RubberDuck

23:19

Would a paste special "transpose" help you work with the data?

Or maybe a pivot table? Not quite sure what you're up to, but you could certainly translate that psuedo code into VBA. Might be slow if you have a ton of cells.

Watch the off-by-one-errors though. =;)-

Transcript for