Welcome to the portion of my website where I share and store information or tools about Microsoft Access and Excel. Hope you find some of it interesting or helpful!
If you're totally new to using Microsoft Access, I recommend reading this article to help get you started.
If you have any questions or suggestions for the articles on the website, feel free to post in the forums. I can't promise my response will be quick, but I will try my best to respond to all forums posts.
Sincerely,
Azli
As an Amazon Associate I earn from qualifying purchases.
Here's an easy way to calculate the number of occurrences of a string within another string.
| A | B | C | D | |
| 1 | String | Search String | Count | Formula |
| 2 | AaAaAaA | a | 3 | =(LEN(A2)-LEN(SUBSTITUTE(A2,B2,"")))/LEN(B2) |
| 3 | AaAaAaA | A | 4 | =(LEN(A3)-LEN(SUBSTITUTE(A3,B3,"")))/LEN(B3) |
VBA UDF (User Defined Function) to determine if a sheet or range exists in currently active workbook, e.g.
If IsWorksheet("SheetName") Then
Msgbox "Sheet exists"
Else
Msgbox "Sheet does NOT exist."
End If
Besides the common practice of controlling the status bar text or displaying a progress meter in Microsoft Access, the SysCmd command may be used for other purposes.
Sometime it's useful to use the file name, file path, or sheet name. Particularly if you're using formulas like INDIRECT() or HYPERLINK(). The problem occurs if the path changes of the sheet name is changed, you need some way for those to be dynamically updated.
So this is how I've used CELL() and HYPERLINK().
