When Office 2007 came out Microsoft introduced their new security measure Trusted Documents and Locations. Until a document was Trusted, the macros/VBA code would be disabled to avoid malicious code from running accidentally.
Thus, for documents with Macros, VBA code, until they are trusted in some manner you will see notifications similar to:
In Access you’ll see:
In Excel, Word, … you’ll see:
Continue reading
Today, I thought I’d go back to the basic and create a simple article to go over text parsing techniques and illustrate how we can use a few built-in function to get what we want from a string.
If you are new to VBA and text parsing, you’re probably asking yourself: which functions? Well, in this article I will touch upon:
So let’s get started
Today, I thought I’d share a couple simple functions that can help extract certain elements from a string.
In this instance, all the functions are built upon the same technique, iterate through the string character by character to see if it is in the desired ascii range, if so keep it, otherwise move on to the next character.
Basically, each characters is represented by a numeric ascii value:
we can test each character by converting it to it’s numeric value by using the Asc() function. Similarly, we can get the string representation of any ascii value by using the Chr() function.
So let’s look how we can put this all to use.
So what does a developer do when he can’t sleep at 3am? He decides to write an article about self-healing object variables!
In plain English, these are variables that are intelligent enough that they initialize themselves once and retain their value for the entire session. If reset, they will reinitialize again automatically. They are like Global Variables on steroids. It is just one more step you can take as a developer to improve the overall performance and give your application a little boost, and this for any VBA application (not just Access)!
My first recollection of being exposed to the concept was back in, I believe 2003-2005ish, by MVP Dirk Goldgar who was answering one of my Access question in a discussion forum. He didn’t call it SHOV, but that is effectively what it was. Implementing it drastically helped with performance with a database I was struggling with, and from then on I was hooked! No clue who originally came up with the idea, and it may be taken from another programming language for all I know, but it’s been around for a long time.
When we talk about Object Variables, we are most often talking about the use of the CreateObject() function, or in the case of Access specifically the use of CurrentDb. So things like
Set oExcel = CreateObject("Excel.Application")
Or
Set db= CurrentDb
These actions are straining, especially when done repetitively. The process of creating ActiveX reference, by using commands like CreateObject(), can cause significant delays, so we want to minimize such activities as much as possible to give the best user experience possible.
The idea behind Self-Healing Variables is you do it once, but hold it in memory that way subsequent calls don’t need to perform the initialization again, as it is still already in memory from the 1st call, ready to go.
Using Self-Healing variables can simplify your code since you no longer need to initialize such variables in your code and can significantly improve overall performance. On slower machines the performance boost achieved by using self-healing variables can be quite noticeable.
I don’t know about you, but I can’t count how many time a user e-mails or calls me on the phone giving me an error number and no description. Sure you can start Googling and trying to dig up some information. In some cases, it is easy to find, in others it can be next to impossible.
This is why it can be advantageous to simply create a table with all the error codes and their descriptions so you can quickly look anything up.
Continue reading
By now, you have probably heard of Microsoft’s new plan is:
and you can learn all about when this is coming to a PC near you, and all the other succulent details, by reading:
In the sections below I will examine:
As for me, I am looking into what I can do to minimize the frustration of dealing with this new hurdle of adding an extra 3, 5, 10+ clicks for each individual file I want to be able to work from e-mails, websites, forums I support, …
If you haven’t already heard, beginning with Microsoft Office version 2203 (for the Current Channel), set to release in early April 2022, Office will disable all VBA code in files from the Internet. Here’s the breakdown by Office Update Channel
| Update channel | Version | Date |
|---|---|---|
| Current Channel (Preview) | Version 2203 | April 2022 |
| Current Channel | To be determined | June 2022 |
| Monthly Enterprise Channel | To be determined | July 2022 |
| Semi-Annual Enterprise Channel (Preview) | To be determined | September 2022 |
| Semi-Annual Enterprise Channel | To be determined | January 2023 |
This applies to all files! Access, Excel, Word, PowerPoint, … that you download via your web browser, receive by e-mail. It doesn’t matter the source, the method used to download them, they will have their macros/VBA code blocked. Not disabled, but outright blocked. There will be no button to unblock them! Now, you will need to take a series of extra manual steps to unblock these files.
Up until now, in my 2 prior articles, we seen how we can read a single property or get all an images properties:
Today, I’m going to start showing you the real power of GDI+, we are going to examine deleting an image’s property.
The entire process revolves around the GdipRemovePropertyItem function. That said, once you alter the image, then you need to save it and to do so we use the GdipSaveImageToFile function. The issue here, complicating our life unnecessarily is the fact that GdipSaveImageToFile won’t allow overwriting/saving the currently open image file. So we have to create a temp image with the changes, close the image and then copy it over. Enough talk, here’s the code.
Today, I thought I’d touch upon overwriting built-in VBA functions.
The concept is very simple, what if I wanted to use my own version of a function? Replace the built-in function?
Well, you can and it is the simplest thing to do! Simply create a function with the same name as the built-in procedure and yours will take precedence!
For example, say you wanted to replace the MsgBox function, you could simple do
Public Function MsgBox()
Debug.Print "MsgBox is dead!!!"
End Function
and now MsgBox no longer displays any dialog. Not something you’d actually do though.
Anyone that follows my blog, you know my thoughts on what was once known as UserVoice; a great concept, horribly implemented and not properly administered!
As I mentioned back in September in my post UserVoice For Access In Gone UserVoice was finally killed off by Microsoft. In my earlier post on the subject Bye Bye UserVoice! the question was “What will replace it, if anything?” Well, today, I have the answer!
Say hello to the Microsoft Feedback Portal, UserVoice’s successor.
The Excel team published a post on the subject yesterday: Announcing the new Microsoft Feedback portal – accepting suggestions now!. Sadly, no mention of it in the Access blog. 🙁
The Excel team describes the portal as:
… where you can submit suggestions about … Microsoft products, vote and comment on suggestions made by others, and see feedback you’ve submitted.
So the exact same idea as UserVoice at the end of the day.