I got tripped up by this little “feature” recently and wanted to raise awareness of some of the issues surrounding the IsDate function in VB and VBA. The Simple Case As you’d expect, IsDate returns True when passed a Date data type and False for all other data types except Strings. For Strings, IsDate returns … Read more
The advantage of using classes instead of just subroutines is that classes create a level of abstraction that allow you to write cleaner code. Admittedly, if you’ve never used classes before in VBA, there is a learning curve, but I believe it’s certainly worth the time to figure it out. One key indication that you … Read more
I believe you want to find the current region of A1 and surrounding cells – not necessarily all cells on the sheet. If so – simply use… Range(“A1”).CurrentRegion
I’m sure you’ll get a ton of “don’t do this” answers, and I must say, there is good reason. This isn’t an ideal solution…. That being said, I’ve gone down this road (and similar ones) before, mostly because the job specified it as a hard requirement and I couldn’t talk around it. Here are a … Read more
You can use DateValue to convert your string to a date in this instance Dim c As Range For Each c In ActiveSheet.UsedRange.columns(“A”).Cells c.Value = DateValue(c.Value) Next c It can convert yyyy-mm-dd format string directly into a native Excel date value.
The reason can be inferred from the difference between NUL : No character. It is used for filling in time or filling space on the surface (such as surface of platter) of storage device where there are no data. We’ll use this character when we’ll be doing programming for data wipers (destructive and non-destructive both) … Read more
This bug is not present on 32-bit, but it seems to be present in 64-bit VBA-capable applications (I’ve tried Excel, Word, and AutoCAD). Since the question already covers what happens if the Object does not get terminated or if there is no Class_Terminate event, the following examples all use an Object that will surely go … Read more
Yes, absolutely. You want to use win32com module, which is part of pywin32 (get it here). I’ve found you can really simplify Python integration by writing a macro in VBA for Python to use, and then just have Python call the macro. It will look something like this: from win32com.client import Dispatch as comDispatch xl … Read more
By referring this(http://www.excelforum.com/excel-programming-vba-macros/867665-application-cutcopymode-false.html) link the answer is as below: Application.CutCopyMode=False is seen in macro recorder-generated code when you do a copy/cut cells and paste . The macro recorder does the copy/cut and paste in separate statements and uses the clipboard as an intermediate buffer. I think Application.CutCopyMode = False clears the clipboard. Without that line … Read more
Cornelius’ answer is partially correct. His code gets the current instance and then makes a new instance. GetObject only ever gets the first instance, no matter how many instances are available. The question I believe is how can you get a specific instance from among many instances. For a VBA project, make two modules, one … Read more