« first day (2132 days earlier)      last day (59 days later) » 

12:01 AM
[Minesweeper] Games Played: 135, Bombs Used: 74, Moves Performed: 15538, New Users: 28
12:38 AM
@MathieuGuindon did you hear any more news about your job?
1:38 AM
@mansellan oh, yeah... basically my boss called me up the same afternoon to say he'd got me on the short list of people that would WFH with compensation - I've been remoting to work since... and well I'm pretty much doing two jobs now that my colleague is gone
I know I'm working through this week - next week IDK
2:05 AM
#TIL it's painful resolving merge conflicts when working on multiple features concurrently.
Still not as painful as having to continually deal with these issues because they always get dropped cause #WeGotNoTimeForThatNao.
7 hours later…
9:19 AM
Just wondering if this should be considered a false positive.... given this partial code:
Implements IControlValidator
Implements ICreateChangeNameValidator

Private Type TChangeNameValidator
    SourceControl As Access.Control
    Caption As String
End Type
Private This As TChangeNameValidator

Public Function Create(SourceControl As Access.Control, Optional Caption As String = vbNullString) As IControlValidator
    Dim NewValidator As ICreateChangeNameValidator
    Set NewValidator = New ChangeNameValidator

    NewValidator.SetData SourceControl, Caption
    Set Create = NewValidator
The Create method yields an inspection result:
To the variable 'Create' of declared type 'xxx.IControlValidator' a value is set assigned with the incompatible declared type 'xxx.ICreateChangeNameValidator'.
9:59 AM
I have a question, I want dynamically get the row number of last filled cell in column
some cells in column are empty, sheet is a copy of a pattern sheet
I want display number of last row in column in some particular cell
@this This is explicitly by design.
If you daclared the variable on the RHS as the the concrete type, you would not have gotten the result.
The inspection warns about conflicts in declared type. We really cannot know the underlying type in general.
Moreover, relying on an implicit down cast is a code smell, IMHO.
thank you for a link
10:14 AM
@M.Doerner The purpose of ICreate... was to hide the SetData so that when one creates a new object with syntax ChangeNameValidator.Create(), the Create() would be the only one that shows on the intellisense
for now I can get the last row number
but I can't make it dynamically (everytime I add something to column, it counts automatically)
this doesn't work, there is no refresh in K1
1 hour later…
11:28 AM
Getting back to the code I was working on Friday with only the vaguest idea of what I was doing and where I was. Figured it out using TortoiseGit to walk through the files I'd checked in to see what I'd done. #SCFTW #ProudOfMyself #OuchPattingSelfOnBackRelatedInjury
@Pochmurnik is the row number 32767 by any chance?
no, it works
I used solution from link you gave me
You mentioned I need on change event to refresh k1 cell
Do yourself a favor anyway, and change Function AccessionRangeEnd() As Integer to Function AccessionRangeEnd() As Long. The only time to use an Integer in VBA is when you must pass a 16-bit field to an external DLL. VBA uses Long internally and it will implicitly convert from Int to Long anyway.
Something else looks off, gimme a minute...
The accepted answer on this SO question is my go to for getting row count.
The only difference I see between your code and what he's got there is that you have .End(xlUp).row while the answer shows .End(xlUp).Row. The fact that the VBIDE did not capitalize row for you leads me to believe that somewhere you've got a variable or function named row and that somehow, somewhere, someway that is interfering.
Can't possibly imagine how, but that's the only thing I see.
11:48 AM
@FreeMan keep in mind that you can destroy the capitalization if you do something like Dim row As whatever
Did you try putting a call to AccessionRangeEnd() in an _onChange event for the sheet in question?
@this yup, very well aware of that. That's why I said the only difference I see, and that I can't imagine how that may have impacted the code he posted.
@FreeMan yes, I used "Find last row in a column", it adds this function to cell k1, but when I add some row to column "F" it doesn't update automatically
Was about to suggest Dim Row as Long to fix the capitalization, then delete the declaration. (I do that all the time for value -> .value and item -> .item (the methods should both be capitalized)
I have row variable in other procedure
@Pochmurnik Does it update when you manually recalculate the workbook?
11:50 AM
ok, I won't use this name from now
@Pochmurnik I'd suggest lastRow or currentRow or dataEntryRow or someDescriptiveNounRow so you know what the heck "row" means. Also, it prevents VBA from "helpfully" changing capitalization throughout your entire project, including built in method names.
VBA likes to be so "helpful"...
@FreeMan no
I feel like the answer to this is going to be really obvious, even though neither of us is seeing it at the moment.
it updates when I again input AccessionRangeEnd() to K1 and press Enter
I've got a pretty long process running in my project right now, but when it's done, I'm going to create a simple test and see if I can figure it out.
11:53 AM
(for now I dont have any on change event - I mentioned it)
Much less important (to me) that I figure out your problem, than that I continue working on my code changes, but much more inspiring!
@this I understand that. In this case, to avoid the result, you can do two things: either you simply use an ignore annotation or you define two variables, one with the concrete type and one with the setter type. Then, you set the setter type variable to the concrete variable. Finally, you set the result to the concrete variable.
@M.Doerner Yeah. Just to be clear, in this case, we can know that the cast is safe because it's a VBA class and we have both Implements available to us. Would you agree that if we modified the inspection to consider the case where an interface that was assigned a concrete can be assigned to another interface to be OK ?
I do not think we should make special exceptions.
Wait, why do you even need the create interface?
To hide the SetData function from the predeclared concrete
so when one types in the ConcreteClass. the only thing that comes up in the intellisense is the Create.
12:01 PM
Do you intend to cast to that later to change the data content?
No. This is purely to create; there will be no casting back and that would be considered wrong thing to do.
Technically, I cannot prevent that but at least the code will look obviously wrong
Why not just make SetData private?
Oh, I see.
Good morning fellow ducklings. I recently started using Office 365. Yuck lol. When I add a UserForm in Access, it adds "(Predeclared)" to the end of the name. I was wondering what that means..? See attached image.
then Create function cannot use it
since it needs to set the This's data for the created instance.
I really hate that there are no parameterized constructors in VBA.
For that reason, I do not use factory methods.
12:05 PM
Me, too. Hence I'm experimenting with the idea of using a ICreate... interface to help make the API more cleaner
So you use a factory class instead or what?
@spinjector PredeclaredId means that there is a default instance of the class you can access using the class name. For user forms, this attribute defaults to true.
I believe all UserForms are Predeclared by default in all versions of Office, are they not?
Yes, I believe that is the case.
Ok my OOP skills are a little rusty. A bit more info? Is that like they're always there waiting as if there's an imaginary Dim myFrm as new someForm running..?
You know how you can just do UserForm1.Show?
12:09 PM
That's predeclared instance at work.
Ahhh ok gotcha. Thx. :thumbsup:
if it wasn't, then you'd have to do Dim f As UserForm1: Set f = New UserForm1 : f.Show
BTW, Mat has some opinions about default predeclared instance. ;-)
Ok cool so my first guess was in the right neighborhood.
@spinjector read UserForm1.Show
Good heavens, I know Mug just did some updating to the blog, but finding a post is a major PITA!!
12:12 PM
^ that is one of his "some opinions"
There needs to be a search or at least a simple listing of all posts.
it does explain the concept of the PredeclaredID, though, does it not?
site:rubberduckvba.wordpress.com foo, done
Ohhh neato. That looks like a good pahe to read in general. I didn't know that was there. Thanks.
@this yeah, I did that, but still a PITA
The More You Know.™
12:12 PM
Ok ciao peeps. thx.
@FreeMan I find that a good greek yogurt and fresh cucumbers is the key to making it pop.
good Lord, I trimmed down the data I'm retrieving, and I made it more efficient by returning several values from a sProc instead of getting each value individually, but this is still taking forever to run this report from home!
(could be network via VPN is the problem)
@this Yogurt I'm fine with. Cucumbers - they're excellent compost heap fodder.
Of course I chave to respond with this. :-D
Oops sorry that came out way too big.
12:15 PM
usually we get an animated GIF, so I'm happy for the still image.
haha ok
later gators
yeah, there's a jerk who really likes gif. So annoying.
@this BTW, that only works if you know what you're looking for. If you just want to browse, no so much.
Probably should be slapped twice for being so gratuitous.
I wonder who @this could be referring to.
12:16 PM
@FreeMan Yes, I agree
Just a simple "List of all articles". Maybe a "Sort by Date" and "Sort by title".
Of course, one could go crazy, man, crazy and add some tags...
this is fun... Somehow, I've got my Excel window set to "always on top". It's hiding part of my browser window, my Windows Explorer window. Heck, it's even on top of it's own VBIDE window.
How the heck did I do that?
whew! Close & reopen Excel and it's back to normal.
Is that a setting somewhere?
I don't have a concrete evidence but I've already had that suspicion that since Windows 10 or so, they subtly messed up how windowing works, so we get those quirks like that.
Ah! Similar to how they managed to screw up Excel in general so it silently crashes on exit and restarts itself about 37.2% of the time.
Feb 21 at 15:04, by FreeMan
Feb 12 at 14:02, by FreeMan
Jan 31 at 19:21, by FreeMan
yesterday, by FreeMan
23 hours ago, by FreeMan
2 hours ago, by FreeMan
2 mins ago, by this
12:32 PM
Gotta keep their customer's happiness rating low.
@Pochmurnik Try inserting this in the "Akcesje" sheet code:
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
See if that makes your cell count update
Just to check I added Debug.Print "sheet changed" after this line with calculate and it printed it in Immediate
but no refresh/recalculation in K1
12:55 PM
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
Ugly as all get-out, but functional
Can not be the right way of doing things!!!
Note, that will fire on every single cell change. You'd probably want to at least make it conditional to ensure it's only calculating when the column is 6 (column f)
@this negative NPS scores are the way to go!!
are you testing it? and it works?
so I don't know what is wrong with my sheet, here it doesn't
A) unqualified Range call is not a good idea. But, I don't do enough any UDFs so I'm not sure exactly how to go about resolving that to ensure it's only impacting sheet "akcesje"
Are you sure that's in the "akcesje" worksheet's code?
12:59 PM
B) It needs to filter to only calculate if it's in column F. It's not the most intensive calculation in the world, but it's cleaner that way
C) It just seems wrong to have to manually trigger it that way, but I don't do UDFs so I'm not sure...
Maybe @MathieuGuindon can chime in? :) :)
I added this line to Worksheet_Change Range("K2").Value = "works"
it changed k2
but I also got error in line with calculate
Runtime 28 out of stack space
What's the code?
Stack space issues generally imply some form of recursion. Not always obvious with calculations and events.
I added this to K1 cell
and this pastebin.com/C3x2xWKL to sheet "Akcesje"
I think the out of stack space is because you changed the value in K2, so that triggered another change event, which changed the value of cell K2, which triggered a change event, which...
ah, ok
1:06 PM
If you're going to do something like that, I believe you have to disable events, make the change, then reenable events. (correct me if I'm wrong, Mug)
anyway, this shows k2 changes
I'm sorry, @Pochmurnik, you're beyond my knowledge at this point.
I will test it on new Excel file
@M.Doerner I have to wait for a task to finish because otherwise the program finishes executing and exits before it returns. I invoke them via Task.Run(() => )
@MathieuGuindon Oof, sorry you're only just now getting to WFH and having to work two jobs :(
1:23 PM
@FreeMan sounds like it, yes
1:39 PM
@Pochmurnik might be a good starting point. I started a brand new file and my last posted Worksheet_Change function did work consistently as I added and removed random text from column "F".
Ok, I have new .xlsm, in sheet "Akcesje" I add Worksheet_Change, in Module1 I add AccessionRangeEnd definition, then in "Akcesje" I fill F1 to 3 with values 1,2,3, then I write "=AccessionRangeEnd()" in K1, I press Enter and it displays 3 in K1
I write something in F4 and press Enter and nothing changes in K1
2:03 PM
^ try adding that to the UDF
the function has no dependents, Excel doesn't know it needs to recalculate it
Thank you, it works
@MathieuGuindon If this is in the UDF, does he need the _Change event handler?
I mean, I removed change event and it still works
2:12 PM
@MathieuGuindon I presume that is a flag setting that needs to go into each UDF (with no dependents) that needs to recalculate in order to force it to be recalculated, not a 1x per workbook setting?
Volatile UDF recalcs every time (bad for perf)
Ideally a UDF takes all its dependencies in =)
But is it specific to the UDFs where it's declared, or does it force all UDFs to recalc?
I think it's per-function
but I don't use UDF much
so rewriting the function like this:
unlike formulas, you can put a breakpoint or Debug.Print and see what happens.
(assuming that UDF => VBA functions, not formulas...)
2:16 PM
Function AccessionRangeEnd(ByVal inSheet as String, ByVal inColumn as String) As Long
    Dim x As Long
    With Sheets(inSheet)
        x = .Range(inColumn & .Rows.Count).End(xlUp).row
    End With
    AccessionRangeEnd = x
End Function
would make it more flexible and remove the need for Application.Volatile?
guessing a UDF can't pass in a Worksheet-typed variable...
FWIW, when I do write UDF for Access, I find it better to pass parameters in like manner than having a function that spits out magically delicious answer.
totally not comparing apples to oranges, right?
mmmm... Magically Delicious!
@Pochmurnik - give that function def a try ^^^
Clint Eastwood + Lucky Charms: Do you feel lucky, punk? It's magically delicious
Then you'll have to modify your cell k1 to =AccessionRangeEnd("Akcesje","F")
I need add Application.Volatile to make it work
2:29 PM
Even with the modified function that passes in the parameters?
@MathieuGuindon Is that what you meant by "takes all its dependencies in"?
Passing the parameter as a Range and not a String would fix this.
@FreeMan yes, even with that modified function with parameters
@Pochmurnik try @BigBen's suggestion. I hadn't thought of that. #DontUseEDFs
@BigBen and the range in that case is?
2:45 PM
Yeah that's what I'd try
well make sure you qualify the worksheet too
I'm not sure I know how to modify this, I think Application.Volatile is enough for now
If you can avoid a volatile function, do it =)
3:00 PM
3 hours later…
6:18 PM
Duck check: Is it worthwhile to add a '@HostIdentifier("Excel") annotation? It feels like I've requested several Excel specific features. Could this be a way to appropriately toggle them depending on which host is used?
Full disclosure that I haven't thought through any of the implications this may cause.
Just bringing it up to the hive-mind.
You will be assimilated. Resistance is futile.
7:01 PM
@puzzlepiece87 Now I am confused about the setup of your application. A GUI application should not just shut down when a command returns.
I thought you also showed some progress bar to the user, which did not work because you waited on the UI thread.
7:21 PM
@IvenBach Couldn't you detect the host dynamically and have no need for annotation? What if the code uses multiple Application contexts? Only one is the "host". You can open Word from Excel and do word. (I guess I would need more information about your Excel specific features to understand what you are doing here)
8:16 PM
What's the proper terminology - perhaps library - for the VBA in VBA.CallByName?
Q: CallByName not working for all objects/functions

Simon StreicherIn VBA, most Excel functions are either accessible through Application.WorksheetFunction or VBA Take sinh and sin for example: Worksheet Function Debug.Print Application.WorksheetFunction.sinh(5) 74,2032105777888 VBA Function Debug.Print VBA.sin(5) -0,958924274663138 Question: Why does ...

It's obviously not an Object which is why the Type Mismatch, but want the wording in my answer to be correct
@HackSlash @IvenBach we actually already; our inspections can be decorated with 2 different attributes to indicate whether it is a host specific inspection and whether it applies only when it's the host rather than merely referenced (like Word automation in Excel). No need for annotations in VBA code.
Duly noted. Again you've already thought of it when I finally reach that level of understanding.
:54018675 ?Evaluate("sin(5)")
As you correctly note, CallByName cannot work on non-objects, and modules are non-object. Therefore, a different approach is needed.
The confusing thing is that VBA does let us treat a library or a module like an object but only in matter of resolving the reference (e.g. disambiguating between VBA.Interaction.Sin() from MyThing.Interaction.Sin() or MyThing.MyModule.Sin() (using only MyThing.Sin() in the code), but we cannot set variables to either a library or a module because #reasons.
Right... that was well put.
CallByName needs an Object which you should read as IDispatch. If the object doesn't implement IDispatch, then you can't call IDispatch::Invoke(), where you can basically call a method using a dispid.
8:31 PM
digging deep into the bowels of VBA, which I really haven't done to this point
But being a module doesn't mean it has IDispatch. It's just a bunch of functions loaded to some specific memory addresses.
Thanks, that makes sense. The exact reason for why I want this is a bit complicated, but it is part of a some testing scripts to test the numerical similarity between some excel functions and functions from other languages. Do you know perhaps of any way to accomplish an indirect call to VBA functions outside WorkbookFunction, like "sin" and "cos" and "month"? Application.Run also doesn't seem to work. — Simon Streicher 4 mins ago
So I guess what I'm saying is that VBA.Interaction.Sin() gives one a sense of calling a function on an object (aka IDispatch) but in fact, VBA compiler resolves the VBA.Interaction.Sin to something like "execute this function at this memory address 0xDEADBEEF", so there's no object involved at all.
Right... so I don't think there's any way to do what OP wants, but just wanted to check
8:38 PM
yeah but doesn't that just call the worksheetfunction sin?
the fact that he says Application.Run sounds suspicious, though.
No, it gave the same result as VBA.Sin()?
it should, I would think, but I assumed that Evaluate is actually calling the worksheet function (and not the VBA member VBA.Sin)
i.e. you can't do Evaluate("vbGet")
By worksheet function, I meant as called by =SIN(5) in a cell, not WorksheetFunction.Sin, which doesn't exist.
isn't it WorksheetFunctions.SinH()?
which is why Evaluate("Sin(5)") is referencing the formula SIN if I'm not mistaken
From the evaluate docs: [The Name parameter is a] formula or the name of the object
8:57 PM
I think I'm missing something then.
doesn't that demonstrate that Evaluate is using VBA.Math.Sin() for "Sin()"?
But =Sin(5) in a cell returns the same thing.
It would make sense for Application.Evaluate to evaluate something in a Excel context, not in a VBA context.
Another example: ? Evaluate("IF(1=1,1,0)")
is just like typing =IF(1=1,1,0) into a cell.
I guess my confusion is because I took OP to imply that the WorksheetFunction.Sinh() is supposed to be the one that's used in the =SIN().
yeah the inclusion of WorksheetFunction.Sinh was really confusing
... and I just found out that the SINH function exists, didn't know that
so it's actually a red herring. Whee!
2 hours later…
10:51 PM
@puzzlepiece87 Is Landing a window?
I think that the main problem in your application is that you run everything inside Main.
In a WPF application, you would typically get your main window, then an instance of a class derived from Application and call application.Run(window).
That call lets the application run a dispatcher; it will only return when the application shuts down.
Then, you trigger whatever actions you want to take in response to events on the window, usually caused by user interaction.
When you just call an async method from there (without awaiting), the code in it will start synchronously. However, at the first await, it will save the state and terminate.
Then, when the awaited task has completed, it will schedule a continuation to the specified synchronization context, which is the same the method started on by default.
So, after the await, the execution resumes on the UI thread once the dispatcher of the application gets to the continuation.
What the dispatcher essentially does is that it waits for new window messages in an infinite loop.
Note that the application object has three distinct shutdown modes. The default is to shut down when no more windows are open. The alternatives are shutting down when the main window closes or only on explicit shutdown.

« first day (2132 days earlier)      last day (59 days later) »