(check out the context!)

morning

pros and cons of VB & VBA is definitely off-topic as POB. Since it's primarily opinion based we can only discuss it here if anyone ever wanted to ;)

LINQ ? uh tough one but I've left a comment

@vba4all I think all that's stopping me is that there are no extension methods in VBA. But they are just syntax sugar for static method calls. The LINQ query syntax gets compiled to method calls... ok no IQueryable in VBA... but the real showstopper is deferred execution / lazy evaluation. And the notion of a query provider, so LINQ to Entities can spit out T-SQL. I'm not talking about implementing all of LINQ in VBA...

...but with a Delegate class, most of it can be implemented now. Enumerable.Where was impossible 2 days ago.

@RubberDuck YESSSS!!

I mean, you've addressed the sole point I would've had for a selfie :)

@Mat'sMug Yeah. I was just looking into that the other day actually.

It was a no brainer once it hit me.

@vba4all what comment? You can't do that to me. I'm way to curious to just not know. lol

^^ is that about not Setting the result?

Ahhhh maybe.

But an AssignUnknown method isn't hard to write.

@Mat'sMug creating delegates wouldn't be too hard. There would be only two issues. 1 not generating code and just let Application.Run do it's thing. 2. handling the paramerters

@Mat'sMug That was priceless by the way.

I like VBA, I just wish everyone who wrote it had some level of software background rather than being hackers with literally no idea what they are doing

@enderland It's where a language's "culture" really matters. VBA has a crap one.

@ptwales Yeah

I often compare it to PHP. VBA gets a bad rap because there's a low barrier to entry. A lot of beginners with no frakkin' clue use it. (Although PHP really is a crap language, but that's beside the point.)

There's a lot of people in this room right now that, in my mind, legitimize VB6 as a language.

@RubberDuck the trick is convincing others (I'm happy I found this room, I've never been able to get VBA questions answered on Stack Overflow at this point since 99% of the ones there are "I'm too lazy to use the macro recorder do this for me plz")

I think @mehow is active on SO in VBA. I quit about a year ago...

@RubberDuck does VBE work in VB6? ..or is there an equivalent?

Too many crud question. Noise to Signal ratio is too great. And earning any rep there, even with a fantastic answer, is neigh impossible. I know they're just imaginary internet points, but they motivate me.

hmm there has to be.. otherwise how would MZ-Tools work?

@RubberDuck I basically said that since using an Optional Parameter `predicate' as Delegate if there shouldn't be a check first using IsMissing(predicate) before checking if it's not nothing.

Yes! It works for VB6. AFAIK it was designed for VB6 and the VBA IDE is a slimmed down version of the VB6 IDE. So, if anything, it works better.

@Mat'sMug yeah I am still kind of active on SO... I just want to get the 20K rep and then I am pretty much off (unless I spot a really interesting question which will be probably once in a year or two lol )

I don't even bother anymore there, I started choosing to either up/downvote all questions I read there and realized I was downvoting the majority of VBA questions

I mean seriously

@enderland I totally understand... that does kind of explain this:

3,472 down...

I'm still pretty heavily upvote vs downvote, but I try to upvote all good answers in VBA since it's historically been a really low vote count topic

I have been deleting my old 0 votes answer on questions with low views ( you can delete 5 per day only though )

I think i've already deleted around 50+

@vba4all Ahhhhhh okay. Yeah. IsMissing() doesn't work on anything but variants.

@RubberDuck which makes sense, what is "ismissing" for an integer which has a default value of 0? :)

Right. And boolean has a default value of false.

I suppose this is a situation where java's "all objects are references" might actually be useful!

I talked about that a while back on my blog. christopherjmcclellan.wordpress.com/2014/02/14/…

that one wasn't a bad question ;)

@RubberDuck & @enderland yeah i totally agree.. answering yesterdays question with all those optional parameters as variants tricked me!!

i was also trying to pass C#s int? to vba to see how COM server marshals that into etc so yeah... it just all got twisted to me haha

@vba4all int? is actually a Nullable<int> ...generic types can't be exposed to COM :(

i just love it when something can't be done, still I try just to realize and prove to myself that it actually can't be done...

im just f*** up like this ;)

@vba4all I can relate to that :)

So can I.

haha that's true but sometimes it is worth all the effort in the world

hmm this is enlightening:

has anyone played with this :

@Mat'sMug well kinda, the reason I needed it was because I was an idiot :)

ah-ha!

Public Function Aggregate(ByVal accumulator As Delegate) As Variant

    Dim result As Variant

    Dim isFirst As Boolean

    Dim value As Variant
    For Each value In encapsulated
        If isFirst Then
            result = value
            isFirst = False
        Else
            result = accumulator.Execute(result, value)
        End If
    Next

    Aggregate = result

End Function

?LinqEnumerable.FromList(List.Create("the", "quick", "brown", "fox")).Aggregate(Delegate.Create("(work,value) => value & "" "" & work"))
fox brown quick the

so cool

I added the Aggregate function to the post

back to Nullable<int> ?btw. you can have a member on the COM interface that returns an int? type via COM

it's not visible neither in Object Browser (+ show hidden) in VBE nor intelli-sense

interesting

c.getNullable()

it doesn't give you an error that member does not exist

that my friends is very interesting...*at least for now*

unicorns

OK base to ask a question on SO

maybe some experts who know better than me can answer that...

microsoft people would give you a one-liner ie. because the getNullable() is marked with 2 attributes [Att1(1050), Att2(99)]

go figure what that means haha

@Mat'sMug, Here is an issue I have with the Enumerable that I can't work around. It has all of these auxiliary methods that I love but aren't really dependent on the Enumerable. Ideally they would be abstracted to work with anything that implements Get NewEnum

@ptwales I think there's no way around that :(

that's why I added FromCollection and FromArray to generate an Enumerable

Methods like Aggregate aren't hard to implement outside of Enumerable but once they are required to return a sequence we would like them to return the same type. That I can work around with duck typing as long as the sequence is of the same length.

But even then the implementation is dirty.

Where's the off-by-one bug here?

Public Function SeededAggregate(ByVal seed As Variant, ByVal accumulator As Delegate) As Variant

    Dim result As Variant

    Dim isFirst As Boolean
    isFirst = True

    Dim value As Variant
    For Each value In encapsulated
        If isFirst Then
            result = seed
            isFirst = False
        Else
            result = accumulator.Execute(result, value)
        End If
    Next

    SeededAggregate = result

End Function

?LinqEnumerable.FromList(List.Create(4,8,8,3,9,0,7,8,2)).SeededAggregate(0, Delegate.Create("(x,value) => iif(value mod 2 = 0, x + 1, x)"))
 5

should output 6

Also in unrelated news I'm creating a "true/false" table in SQL so I can have a "True/False" selection which allows nulls, since Access blows up with null bit fields

wtf is this madness

Is there a better way to do this? I like to hope there is but I don't think there is..

@enderland adodb? I've never had trouble with it. I use bit fields.

@RubberDuck they can't have null values if linked to SQL server

if your backend is Access it doesn't matter but the database engine blows up when you have null bit fields in SQL

@enderland Stop linking tables. Use adodb recordsets.

@RubberDuck well all the data is stored on a SQL server

Right. So don't link the tables with the JET engine. Bind your forms to ADODB recordsets instead.

Hmmmmm. I probably should have done that....

here for the brave

just kidding with that "brave" abbreviation ;)

@Mat'sMug you are really trying hard for tumbleweed badge, ain't you?

;)

lol

> I like that suggested tags are c# and linq - means I did something right!

@Mat'sMug May I humbly suggest creating a version of that that can be copy pasted into an IDE. Minimal examples for SO.

And how the frak do you have an off by one error in a for each loop?

no idea

oh fuck

this works:

Public Function SeededAggregate(ByVal seed As Variant, ByVal accumulator As Delegate) As Variant

    Dim result As Variant

    Dim isFirst As Boolean
    isFirst = True

    result = seed
    Dim value As Variant
    For Each value In encapsulated
        result = accumulator.Execute(result, value)
    Next

    SeededAggregate = result

End Function

deleted the post

Damn I can be dumb

So wait. How were you getting an off by one though. I'm not seeing it.

Ohhhhh never mind.

Found it.

Yeah, like that ;)

A lot of times I start asking on SO, and often I find my answer before I'm done writing the post. Or 5 minutes later.

ok someone is upvoting all my content on SE...

Optional parameters FTW!

Public Function SeededAggregate(ByVal seed As Variant, ByVal accumulator As Delegate, Optional ByVal resultSelector As Delegate) As Variant

    Dim result As Variant

    Dim isFirst As Boolean
    isFirst = True

    result = seed
    Dim value As Variant
    For Each value In encapsulated
        If resultSelector Is Nothing Then
            result = accumulator.Execute(result, value)
        Else
            result = resultSelector.Execute(accumulator.Execute(result, value))
        End If
    Next

would it be bad to change the order of the parameters so that I could shove them all under one function?

That depends Mug. There's such a thing as too much of a good thing. Would doing so create a confusing API or break the SRP?

well the signature wouldn't match that documented on MSDN anymore :(

I'd have (Delegate, Optional variant, Optional Delegate) instead of (Delegate) and (variant, Delegate, Optional Delegate)

SRP isn't at stake IMO - they all do the same thing :)

OTOH one thing I don't like about optional params is that client code could supply one but not the other - and it's bad when it breaks stuff

^^ @ptwales why I don't like optional parameters ;)

like in the Delegate class, client code could supply arg28 and skip the first 27

so I'll stick to Aggregate(Delegate) and SeededAggregate(Variant,Delegate,Optional Delegate)

and now I finally understand how Enumerable.Aggregate works!

@Mat'sMug I never said I liked Optional Parameters. I just dislike ParamArray because a function using it cannot be extended. Application.Run's 30 optional Parameters are worse than ParamArray

we're on the same page ;)

though I like how ParamArray works with the client code - like how I can inline-add list items :)

It's syntactically nice but you should always supply an alternative that accepts an array.

true

@RubberDuck About VBAEX, Should we consolidate List and Enumerable?

@ptwales Yes. I imagine so.

what do you mean, consolidate?

List would call into Enumerable methods?

Don't forget that his List class isn't type safe Mug. He has a subclass that is.

@Mat'sMug When you put it that way, it's a good question.

might not be crazy at all - my System.Enumerable is actually an almost-complete dupe of System.List. Stinks.

What is System.List?

it's my List class

^^ in the System project :)

github.com/ckuhn203/VBEX/issues/11

Added an issue to the repo.

List is essentially a mutable Enumerable / or Enumerable is essentially a read-only List

...but only from the public API's point of view - Enumerable still needs a way to add items, but only Friends can do it

makes sense to compose a List with an Enumerable

more than composing an Enumerable with a List IMO

@Mat'sMug Isn't that how .Net does it?

.net has Enumerable implementing IEnumerable, and IList extending IEnumerable - List implements IList

that's the part we all wish worked in VBA

Truth ^

So have a List in an Enumerable or Other way around?

System.**Linq**.Enumerable

Also I want to see that Tuple class....

oh, not much to see there - I used it in one place, and then refactored and didn't need it anymore; I guess it could be turned into a variable-size indexed thing

Option Explicit
Private Type TTuple
    Item1 As Variant
    Item2 As Variant
End Type

Private this As TTuple

Public Function Create(ByVal value1 As Variant, ByVal value2 As Variant) As Tuple

    Dim result As New Tuple

    If IsObject(value1) Then
        Set result.Item1 = value1
    Else
        result.Item1 = value1
    End If

    If IsObject(value2) Then
        Set result.Item2 = value2
    Else
        result.Item2 = value2
    End If

    Set Create = result

End Function

Public Property Get Item1() As Variant

^^ boring eh

@ptwales Well, it sounds like we would compose a List from an Enumerable. I can look at it after I re-write the Unit Testing code.

How would we implement a Mutable container from an immutable container?

Valid point....

Enumerable.Add is Friend - it's only immutable from the client code; if both are in the same project you can do it

but I hear what you're saying

I didn't like it... so much that I wrapped a Collection instead

but that was stupid

ctrl+v

But if List composes an Enumerable it isn't an Enumerable then it won't be able to call any Friend function of Enumerable

It's like all pseudo inheritance patterns in VBA is exactly backward.

no, a Friend is any code in the same project.

what the hell is that...

hence Friend == Public if you only have 1 project

@ptwales VBA my Friend!

That's the dumbest thing I have ever heard about VBA

I'm mortified.

lol

In addition to Public and Private, a procedure in an object module can be declared as Friend. A Friend procedure may be called only from within the project that contains the class, regardless of the Instancing property of the class. Suppose Class1 is defined within Project1, and contains the following code:

    Public Sub PubSub()
        Debug.Print "PubSub"
    End Sub

    Friend Sub FriendSub()
        Debug.Print "FriendSub"
    End Sub

    Sub NoSub()
        Debug.Print "NoSub"
    End Sub

cpearson.com/excel/scope.aspx

same in VB6

I actually really like it now that I understand it. It allows us to do this

public int SomeProperty {get; private set;}

Same difference more or less.

that.. or inter-project immutability

like Public Property Get with Friend Property Let

side note: Enumerable.Join is ...complicated.

how to implement this without looping a gazillion times over the inner collection...

I think I just found a usage for that Tuple class :)

wow that one is going to be some serious review material, all by itself!

Public Function Join(ByVal inner As LinqEnumerable, _
                     ByVal outerKeySelector As Delegate, _
                     ByVal innerKeySelector As Delegate, _
                     ByVal resultSelector As Delegate) As LinqEnumerable

    Dim result As New LinqEnumerable

    Dim outerValue As Variant
    Dim outerKey As Variant

    Dim innerValue As Variant
    Dim innerKey As Variant

    For Each outerValue In encapsulated
        outerKey = outerKeySelector.Execute(outerValue)

        For Each innerValue In inner

^^ I smell bad performance.. but can't think of a better way

What method is this based on?

System.Linq.Enumerable.Join

For joining collections of objects

Not something I do often in C# though; with EF that part is abstracted away with navigation properties

Now GroupBy

@Mat'sMug Is that the same as an intersection?

Public Function Intersect(collection2 As Enumerable) As Enumerable 'Collection
    On Error GoTo ErrHandler

    If collection2 Is Nothing Then
        Err.Raise EnumerableInvalidArgumentError, TypeName(Me), InvalidArgumentErrorMessage
    End If

    Dim results As Enumerable
    Set results = New Enumerable
    Dim item As Variant
    Dim innerItem As Variant

    For Each item In mCollection
        For Each innerItem In collection2
            If item = innerItem And Not IsEmpty(item) Then
                If Not results.Contains(innerItem) Then 'curse the lack of shortcircuiting

@RubberDuck yes, but with objects

This suffers from the same kind of performance issue.

That implementation is O(2n^2) I believe.

oh, but I could call ToCollection and remove all matched items as I go...

    Dim innerList As System.List
    Set innerList = inner.ToList

    For Each outerValue In encapsulated
        outerKey = outerKeySelector.Execute(outerValue)

        For Each innerValue In innerList
            innerKey = innerKeySelector.Execute(innerValue)
            If outerKey = innerKey Then
                result.Add resultSelector.Execute(outer, inner)
                innerList.Remove innerValue
            End If
        Next
    Next

I think that just chopped off an exponent :)

now ToList is O(n); Remove is also O(n)

..I'm no big-O expert... does that earn me anything really?

Yeah. It does. It went from O(n^2) to O(log n)??

did it?

I think so.

You're removing an item to check from the inner list on each match, so the inner loop is a log curve for sure.

68 questions.. 32 more to [badge:socratic]

@RubberDuck should Sum return a Variant?

Public Function Sum(Optional ByVal selector As Delegate) As Double

    Dim result As Double

    If selector Is Nothing Then
        result = Aggregate(Delegate.Create("(total,value) => total+value"))
    Else
        Dim value As Variant
        For Each value In encapsulated
            result = result + selector.Execute(value)
        Next
    End If

    Sum = result

End Function

I'm starting to consider tagging with vb.net at this point...

/me shamelessly upvotes without reading, sure, have some [vba] love

@enderland lol.. you know Big-O?

Yeah, I just didn't read that :)

hmm.. I could expose a Friend Sub Remove on LinqEnumerable, and do away with the ToList call...

@Mat'sMug Why would it return a variant? Do you really want to muck around trying to figure out what type to return? And worse, trying to figure out what type you're going to get back as a client of the code?

well I'm returning a Double.. even if the collection might just be Byte items.

I'd just return the type it returns!

hmm

oh hi @Mark!

Hello @Mat'sMug

My work disallows SO chat so I was seeing if I could join any others :P

cheers

lol they block SO chat, but they leave CR alone! hahaha nice! Check out The 2nd Monitor :)

@Mark Hola!

A file that uses '\0' as a delimiter.

Yeah - great logic, huh?

Nice

the magical world of EDI non-compliance

derp.xls is really a fixed width text file.

herp.csv is really an excel 2010 workbook

da-fuq?

lol

The idiocy is simple in practice.

not in theory?

wait, just ... wtf-is-this-shit??

@Mat'sMug You mean this shit?

I want to answer, but it's going to go way over OP's head.

Database program XXX cannot export a query as an Excel file so the people exporting them just select "export as any file ." then type in herp.xls. The program doesn't recognize that format so it prints whatever it decides is the default format.

too many times "macro" in that text

@ptwales that's just.. ... ...wow.

Sometimes you are tempted to edit the question to "I need a macro to do XXX please make one for me!" But that never gets approved

lol

Then they complain that there are errors with leading zeros.

I have a macro that reads like this

Sub OpenUnknown(ByVal file_path as String)
    If isExcel(file_path) Then

crap

and isExcel returns true if file_path ends with ".xls", ".xlsx", ".xlsm" or ".csv" I guess?

Sub OpenUnknown(ByVal file_path as String)
    If isExcel(file_path) Then
        OpenExcel file_path
    Else
         delim = determineDelimiter(file_path)
         If delim = "" Then
             OpenFixedWidth file_path, DefaultFixedWidths()
         Else
             OpenDelimitedFile file_path, delimiter
         End If

    End If
End Sub

@Mat'sMug lol no! it opens the file as an excel file then reads UnknownWorkbook.fileformat. Then closes the file.

ah

Can't trust them file extensions. That's just a filename.

how to deterine eh

haha

On Error GoTo Nope

shouldn't Application.ScreenUpdating = True be under the CleanExit label?

> First thing to do, is to find out exactly what instruction is blowing up. When the VBA debug dialog shows up, click the [Debug] button, the illegal instruction should be highlighted in bright yellow.

hmm.. a little harsh maybe?

A little.... maybe...

@Mat'sMug yes

@RubberDuck bah, I'm not expecting anything out of that answer

@Mat'sMug I think his problem is he's using Word stuff in excel. wdReplaceAll

and he mentions Access code.. wtf

I know. That's why I didn't answer. I was going to tell him to pass the control to a sub, and then use VBIDE to insert snippets, but..... nah.

I think we've just done way too many reviews here. Case & point.....

that looks suitable for CR..

@RubberDuck

Public Function Sum(Optional ByVal selector As Delegate) As Variant

    Dim result As Variant

    If selector Is Nothing Then
        result = Aggregate(Delegate.Create("(total,value) => total+value"))
    Else
        Dim value As Variant
        For Each value In encapsulated
            result = result + selector.Execute(value)
        Next
    End If

    Sum = result

End Function

?TypeName(LinqEnumerable.FromList(List.Create(CByte(12), CByte(25), CByte(200))).Sum)
Byte

@Mat'sMug I don't think it works as is. He said he hadn't tested it yet.

Very nice by the way.

I need to test this:

Public Function Join(ByVal inner As LinqEnumerable, _
                     ByVal outerKeySelector As Delegate, _
                     ByVal innerKeySelector As Delegate, _
                     ByVal resultSelector As Delegate) As LinqEnumerable

    Dim result As New LinqEnumerable

    Dim innerValue As Variant
    Dim innerKey As Variant
    Dim innerKeys As New Dictionary
    For Each innerValue In inner
        Set innerKey = innerKeySelector.Execute(innerValue)
        innerKeys.Add innerKey, innerValue

Oh shit

TS^

stars are per-room!

(or you're out here too?)

            result.Add resultSelector.Execute(outerValue, innerKeys(outerKey))

I'm out everywhere.

lol

Convert ^

yay!

Why VBA has a bad reputation

Almost done with the Delegates refactoring.

There will be Delegates.Create(ExistingId) and then Delegates.Lambda(lambda)

Also .Execute(ParamArray) and .Apply(Array). I am not checking whether the delegate is a Sub or a Function though

And if it doesn't exist it will error on application

Nice!

In my version it has to be a function

Have you tried to call Execute -> Invoke and see the error message VBA gives?

/s/call/rename

You have a version that can reference existing functions?

What do you mean by Execute -> Invoke?

Nope

"Invoke" can't be used as a method name, ..and the error is pretty interesting ;)

I was just saying that if you try to reference an existing Sub it will work.

Marked as Restricted?

IDK, hadn't heard of

It says something about the class being derived, and the method being already existing in the base type.

I think I need an AnonymousType class for Join to be usable... the resultSelector would go something like "(employee,emplDept) => new(employee(""JobTitle""),emplDept(""DepartmentID""))" - and then I'd have to ..generate a type on the fly... which I know won't work... ugh.

So I have a Join method that's well implemented, but I don't know how I can test it.

doesn't look too good for GroupBy

@RubberDuck help me out here

:)

oh, how about I pimp up that System.Tuple class

the type I need to return has to be in the System project.

Invoke must be a COM method kinda like how you can't implement ICollection.

indeed

Help you out with what? Join?

I'm trying to write code that uses Join; everything goes fine up to the resultSelector delegate

in C# that could be an anonymous type

or a "solid" class

so I'll create a class instance.

The type would have to be a predefined class. That's for sure.

but then I need a class that can have any number of properties

and they can't be dynamically generated

What I'm saying is you could only use it on an existing data model.

Even that's dicey, because you can't New something in a different project.

I'm thinking of wrapping a dictionary, and expose a parameterized getter that takes a property name and returns the associated value

Might work.

Yeah. I think that definitely could work.

well I'm going to "interpret" the "new()" part of the delegate definition, and "translate" it to "ThatClass.Create(whatever)" - and put that in the generated code

how would I call such a class? AnonymousType is a big fat lie!

or.. why do I need to wrap the dictionary at all? perhaps I could return a dictionary

Mug. Step back and think about what you're trying to do. One thing at a time. Do you want linq-to-object or linq-to-sql?

Either way, returning a dictionary makes little sense.

why?

oh

it's a LinqEnumerable containing Dictionary items that I'd be returning

linq-to-sql is out of the question for now - I'm not generating T-SQL with these calls!

I'm trying to project elements of a LinqEnumerable into a new shape - this new "shape" needs a type..

Okay. So why a dictionary? Why do you need an anonymous type? You already have two LinqEnumerables, you just want to return an Enumerable where some property matches.

Beat me to it.

Typing on the phone is slow.

the resultSelector delegate is picking properties from both joined items

So... Hmm... You can't just return the type of object that the original list contains?

that would eliminate the need for a resultSelector function

Good thing or bad thing??

in C# you go employees.Join(employeeDepartments, employee => employee.Id, emplDept => emplDept.EmployeeId, (empl, emplDept) => new { Name = empl.Name, Department = emplDept.Name })

and that gives you a bunch of objects with a Name and a Department property

Yeah man. Linq looks like Greek to me sometimes to be honest.

bbl kids' bath time