@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
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'.
@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
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?
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.
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)
@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.
@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 ?
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.
@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.
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..?
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!
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.
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.
Private Sub Worksheet_Change(ByVal Target As Range)
Range("K1").Calculate
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)
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?
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...
@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 :(
@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
@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?
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.
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.
@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)
In 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 ...
@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.
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.
CallByNameneeds 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.
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 Streicher4 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.
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.
In 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 ...
VBA Rubberducking
VBA Rubberducking