| Function | What Does it do? | Example | Result |
| Len(str) | Returns the length of the string | Len(“Hello”) | 5 |
| Ucase(str) | Changes the string to upper case | Ucase(“Hello”) | HELLO |
| Lcase(str) | Changes the string to lower case | Lcase(“Hello”) | hello |
| Left(str, num) | Shorten the string from the left | Left(“Hello”,3) | Hel |
| Right(str, num) | Shorten the string from the right | Right(“Hello”,3) | llo |
| Mid(str, start, length) | Extract the middle of a string | Mid(“Hello”,2,2) | el |
| Instr(start, look in this string, find this string) | Find if a string is in another string | Instr(0,”Hello there”,”Hello”) | 1 |
| Trim(str) | Removes preceding and trailing spaces | Trim(” hello there “) | hello there |
| LTrim(str) | Removes spaces from the beginning of a string | Ltrim(” hello “) | hello |
| Rtrim(str) | Removes spaces from the end of a string | Rtrim(” hello “) | hello |
| String(num, char) | Repeats the char num times | String(2, “a”) | aa |
| Space(num) | Makes a certain num of spaces | Space(2) | |
| Cint(str) | Change a string to a integer | Cint(111.1) | 111 |
| Clng(str) | Change a string to a long (use if you get an overflow error) | Clng(111.1) | 111 |
| Cdbl(str) | Change a string to a double | Cdbl(111.1) | 111.1 |
| Split(str,delimiter) | Split a string into an array | Split(“Hello,There,Friend”, “,”) | (0) = Hello (1) = There (2) = Friend |
| Join(array, delimiter) | Join an array into a string | Join(array(“Hello”, “There”, “Friend”),”,”) | Hello,There,Friend |
Category Archives: Access
[Cheatsheet] VBA String Functions
Posted by admin on October 9, 2011
No comments
Simple Regular Expression Tutorial for Excel VBA
Posted by admin on October 8, 2011
No comments
I’ll go through a simple tutorial for using regular expressions for VBA. This is a great quick start guide for people who have used regular expressions before in other languages. Just as a review, regular expressions are used to match patterns in strings. This is a powerful tool that can help you data cleanse and data mine. The directions are the same for Microsoft Access as well.
First thing first, let’s enable the reference in Excel so that we can use this functionality.
Go to Tools > References
Then find Microsoft VBScript Regular Expressions 5.5
Click Ok, and now you’ll be able to create a RegExp object to use in your VBA.
Here’s a basic function that finds 5 letter words in a string.
Here’s a copy of the Excel file that I am using in the example: RegEx Excel Tutorial XLS File
Function findFiveLetter(sentance)
Dim regEx As New VBScript_RegExp_55.RegExp
Dim matches, s
regEx.Pattern = "\W\w{5}\W"
regEx.IgnoreCase = True 'True to ignore case
regEx.Global = True 'True matches all occurances, False matches the first occurance
s = ""
If regEx.Test(sentance) Then
Set matches = regEx.Execute(sentance)
For Each Match In matches
s = s & " Position: " & Match.FirstIndex
s = s & " Word: " & Match.Value & " "
s = s & Chr(10)
Next
findFiveLetter = s
Else
findFiveLetter = ""
End If
End FunctionNow let’s break it down.
First you need to declare your regEx obejct with:
Dim regEx As New VBScript_RegExp_55.RegExpNext you give your object some parameters.
The most important is .Pattern, which is the pattern of characters that you want your function to find. Then there are a couple of settings like Global, if you want it to search for all occurrences (TRUE) or just the first occurrence (FALSE), and also IgnoreCase.
Here’s a simple guide to get you started on writing regex patterns.
regEx.Pattern = "\b\w{5}\b"
regEx.IgnoreCase = True 'True to ignore case
regEx.Global = True 'True matches all occurances, False matches the first occuranceNow to test if the pattern has been found in a string you can use
regEx.Test(sentance)To get an array of matches from your regex you simply have the regex execute.
Set matches = regEx.Execute(sentance)To get the results from this array object, you can reference matches(n) or use a For Each loop. There are two important pieces of information returned in the array, the index of the match and the actual match.
I used a For Each:
For Each Match In matches
s = s & " Position: " & Match.FirstIndex 'Position of the match
s = s & " Word: " & Match.Value 'The actual match
s = s & Chr(10) 'Prints a new line
NextHere’s the example output:
| Line | Matches |
| What began as a small group of protesters expressing their grievances | Position: 5 Word: began Position: 16 Word: small Position: 22 Word: group Position: 53 Word: their |
| about economic inequities last month from a park in New York City | Position: 0 Word: about Position: 31 Word: month |
| has evolved into an online conversation that is spreading across the harry | Position: 69 Word: harry |
| country on social media platforms. | Position: 18 Word: media |
Here’s a copy of the Excel file that I used in the example: RegEx Excel Tutorial XLS File
The regex function is called findFiveLetter(“put a string in here”)
Basic Regular Expression Patterns for Beginners
Posted by admin on October 8, 2011
1 comment
This is super simplified, but it’s enough to get started with. I remember being confused by the gigantic tables with all the special clauses, so when you’re beginning keep it simple.
Remember if you want to match a literal character you can just type it out, unless there’s a special character. Special characters have meanings in regex patterns, so you need to type a \ in front of them if you want to specifically match them in the pattern.
Common special characters are: + . \ – [] {} $ ? |
Example: If I wanted to match taste, taster, tasters, I would type a pattern like taste\w{0,2}
If I wanted to match taste!, I would write a pattern like taste\!
| Code | What does it Match? |
| \d | Numbers |
| \w | Word characters like letters of any case |
| \s | Spaces |
| \b | Boundary of a word character |
| \D | Not a number |
| \W | Not a word character |
| \S | Not a space |
| \n | Line break |
| \r | Line break |
| \t | Tab |
| ? | Previous code is optional |
| * | Repeat previous code 0 to infinity |
| + | Repeat previous code once or more |
| {1} | Repeat previous code once (can use any number) |
| {1,2} | Repeat previous code 1 to 2 times (can use any numbers) |
Quick Tips: VBA, Make a file dialog open
Posted by admin on September 15, 2011
No comments
Here’s the code to make a file dialog to open up. Once the user has selected a file, it will be saved in the fileName variable, and you can use that variable to open up the file contents.
In order for this code to work, you must activate the Microsoft Office 14.0 Object Library, or else your VBA will result in an error. This VBA should work in Microsoft Access and Microsoft Excel.
Dim fileName As String Application.FileDialog(msoFileDialogOpen).Show fileName = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
Get Visual Basic 2010 Express to query an Oracle database
Posted by admin on September 1, 2011
No comments
So one of the limitations of Visual Basic 2010 Express is that you can only add data sources to link or query Microsoft databases such as an Access database or MsSQL. So if you need to query an Oracle or mySQL database, for example, it seems that you’re out of luck unless you upgrade to the paid version of the software. Thankfully if you have Microsoft Access on your computer there’s a workaround.
All you need to do is set up a linked database in Microsoft Access (look up ODBC drivers if you’re having trouble) and you can query that database as a middleman from your Visual Basic program.
Access Tips: Watch out for case senstitive situations
Posted by admin on August 26, 2011
No comments
I just spent like 2 hours trying to get a query with like 3 levels of sub-queries that I built in a sandbox with copied tables in Microsoft Access to work under a live ODBC linked connection with an Oracle database.
Guess what broke my query?
3 references to a user input form that were missing once capitalized letter.
Access Database on Shared Drive Opening as Read-Only
Posted by admin on August 15, 2011
No comments
I had some issues with one user who was not able to open a Microsoft Access database on a shared drive. The user would try to open the database, and it would only allow the user to use it as read only, which was not very useful. Every other user was able to use the database with out any trouble. There were no issues with permissions on any other databases on the shared drive or with any linked tables.
We were able to fix the issue by going into the permission for the database (Right Click -> Properties -> Security) and adding the user directly as an administrator to the database. I have no idea why the user suddenly had issues accessing the database, as they had been using it on a daily basis for about a month with no issues. Hopefully, this tip might save you some headache.
