Land Of Oorang
Or "Things Only I Will Think Are Important"
Blog
OpenArgs
|
|
comments (0)
|
Migrating... I will be moving this site into the blog section slowly but surely.
On to the show:
On occasion you need to open a form in Access, and have that form already know something special. (Say a filter.) The generally accepted way istp pass the form the information with an "Open Argument":
DoCmd.OpenForm "Example", OpenArgs:="Table1.[MyField]='1'"
But what if you want to pass more than one argument? Well the bad news is you can pass one, and only one string to the form. So you must cram all of your data into one string and parse the string as needed. If you have ever had to maintain someone else's work you know that if the original Author was in a hurry they probably just tacked this and that on as they went, and left you with a 5 or 6 custom string parsing procedures across 2 or 3 databases. This can be, in a word, painful.
One way you can spare your successors this agony, is to use a (somewhat)self-documenting design pattern. A structured way to approach this problem is select a universal argument delimiter, and a common way to access the arguments.
Example follows:
Option Explicit
Public Enum eForm1Args
eFilter = 0
eIsSpecial = 1
End Enum
Private m_strArgs() As String
Private m_lngArgsUprBnd As Long
Public Property Get Args(ByVal eForm1Args As eForm1Args) As String
If eForm1Args <= m_lngArgsUprBnd Then
Args = m_strArgs(eForm1Args)
End If
End Property
Public Sub SetArgs(ByVal value As String)
m_strArgs = Split(value, "|")
m_lngArgsUprBnd = UBound(m_strArgs)
End Sub
Private Sub Form_Open(Cancel As Integer)
m_strArgs = Split(Nz(Me.OpenArgs, vbNullString), "|")
'Example use of Args Property:
If LenB(Me.Args(eFilter)) Then Me.Filter = Me.Args(eFilter)
End Sub
Private Sub Command1_Click()
'Example use of Args Property:
If LCase$(Me.Args(eIsSpecial)) = "true" Then
'Do something special
End If
End Sub
Friday 5/29/2009 - NateOwned
|
|
comments (0)
|
If you hang out on MrExcel then you know why this is funny (click for a larger view):
You can visit Nate Oliver's blog here: http://msmvps.com/blogs/nateoliver/
Tuesday 2/9/2009 - Batch Backup Script
|
|
comments (0)
|
Ok, here is a little quickie you can throw into a bat file and use to back up your data. I kind of like this one because it actually only backs up items that have changed (using the archive bit)
@Echo Off
xcopy "%USERPROFILE%\My Documents\*" U:\ /e /h /y /a
attrib -A "%USERPROFILE%\My Documents\*" /S /D
Echo Backup Complete
Pause
What It Does
This example backs up all files and sub directories in My Documents of the current user to the drive "U:".If the archive flag is set.Then it removes the archive flag for all files and folders (since they have now been backed up). If you change any of the files or add them,windows will reset the archive flag and your backup script will grab all changes. Nice and simple:)
Tuesday 1/9/2009 - Boolean to Sign
|
|
comments (0)
|
On some rare occasion you may have a need to conditionally change the sign of a number. The most obvious way of doing this is
If x then y = -y
However there are a few occasions where you might want to do this without using a conditional, either in an Excel formula or in code. I wouldn't call this a best practice, merely a convenient trick to know about.
y = y * (x * 2 + 1)
Why does it work? Well in VB (and Excel) boolean true implicitly converts to integer -1 and boolean false converts to integer 0. So when True you have:y = y * (x * 2 + 1)
y = y * (-1 * 2 + 1)
y = y * (-2 + 1)
y = y * -1
Whereas when False you end up with:y = y * (x * 2 + 1)
y = y * (0 * 2 + 1)
y = y * (0 + 1)
y = y * 1
So there you have it. It's not always going to be the best way, but it's good to know about.
Friday 8/22/2008 - Excel InStrRev
|
|
comments (0)
|
VBA has a really useful function called InStrRev that finds the last occurrence of a sub-string in a string. To give an example InStrRev("C:\Test\Bar","\") would return 7 which is the position of the rightmost (or last) backslash. This is a very handy function to have around when you need to perform certain text parsing tasks, such as extracting a file name from a path etc.
Unfortunately, while Excel has FIND and SEARCH functions that work like InStr, there is no Excel equivialent to InStrRev. A lot of people simply use a UDF (user defined fuction) to just call the InStrRev in VBA, but there is a way to do it with just Excel formulas. Here is how:
=FIND(CHAR(26,),SUBSTITUTE("A1","MySubString",CHAR(26),(LEN(A1)-LEN(SUBSTITUTE(A1,"MySubString","")))/LEN("MySubString")))
How It Works:
The FIND and SEARCH functions can only find the first occurance of a substring. So in order to find the last occurance of a substring you must first make that substring unique within the string so it (and only it) will be found. The SUBSTITUTE function will replace a specific occurance of a substring with something unique. But you have to be able to specify the occurance number. We want the last occurance so all we need is a straight count of how many times the specified substring occurs in a string. Now to put it all together:
1.) Assume A1 has the value "C:\Test\Foo"2.) Find the occurances of the specified substring within the string:
=FIND(CHAR(26,),SUBSTITUTE("A1","\",CHAR(26),LEN(A1)-LEN(SUBSTITUTE(A1,"","")))/LEN("\")))
=FIND(CHAR(26,),SUBSTITUTE("A1","\",CHAR(26),(11-9)/1))
=FIND(CHAR(26,),SUBSTITUTE("A1","\",CHAR(26),2))
4.) Replace the last occurance of the substring with a special char unlikely to occur in a string (I used the substitute character, it seemed fitting):
=FIND("
","C:\Test
Foo")
= 8
Thursday 8/21/2008 - Flash Clipboard Hijack, an easy manual fix.
|
|
comments (0)
|
OK, so by now you have probably heard of the Clipboard Hijacking problem. Apparently some joyous advertisers are, in a misguided (and annoying) attempt to get you to visit their site have come up with away via flash ads to put the URL of their website into your clipboard... Permanently. Which is, of course, really annoying because you probably still wanted to use your clipboard to copy and paste stuff.
The details and proof of concept are available at ZDNet's Zero Day Blog.But the good news is if you have Firefox there is a manual fix without having to restart your browser or reboot your system (as usual IE users are left in the cold.. at least so far). All you have to do is go toTools>Add-ons>Plugins, scroll down until you see "ShockwaveFlash" (I don't know why it's still called Shockwave, but that is what it is labeled), then click the "Disable" button once, and immediately click "Enable" again. Close the add ons window you are done. Note the value will still be in the clipboard, but if you go to copy/cut something else you will now be able to.
Wednesday 7/9/2008 - Added Code Archive
|
|
comments (0)
|
Not much there yet, but I intend for more. Link is in the navigation panel to the left (or here).
Tuesday 6/24/2008 - Firefox 3 Del.icio.us Add-in Installation Pains (and solution)
|
|
comments (0)
|
So I went with the masses and switched to FireFox 3. I noticed that, much like the beta, the full release still wouldn't run about 30%of my add-ons. Some of them would, or self-updated to version thatwould, but some of them just wouldn't. One of these was my Del.icio.us Add-in. Then a light bulb came on, and I was like "I wonder if they released it as a different plug-in?" So I went and looked and, sure enough, they had. So after a quick "W00t!" I downloaded it and restarted. And this is where the trouble started.
On page two (of four) of the install wizard you are asked to log in. I logged in. But the wizard's "Next" button stubbornly remained grayedout. Curses. So I logged in again. Still gray. I clicked previous andnext in the vain hope that the login detection would be tripped. Nojoy. I did it a few more time as if doing the exact same thing wouldyield different results. It did not (I know, I'm amazed too). So Iclicked Cancel to look up troubleshooting tips and it uninstalled itself! That's right, either log in on the first run or no Del.icio.us for you.
That was annoying, but not nearly as annoying as it was on the 4thor 5th time I had to re-download the plug-in whilst I tried various(futile) solutions. And finally it occurred to me. In my privacysettings (tools>options>privacy) I was not allowing third partycookies. I was like "nooo, couldn't be could it? The website works finewith that setting." And work fine the website does indeed do, but notthe plug in. One click fixed it.
So if you are trying to install the Firefox 3 Del.icio.us plug-i, as of version 2.0.64... Enable Third Party Cookies. You can do this by by just enabling 3rd Party Cookies enabled, or you can create a special exception for http://del.icio.us In Firefox 3 go to tools>options>privacy>exceptions.
Friday 5/9/2008 - Calculating Iterations in a For Loop
|
|
comments (0)
|
This may seem obvious to a lot of you, and if you have never made a mistake because of zero-based math and/or just don't care about that sort of thing, well.. you might as well just move along then :-)
Recently I was working with code that made extensive use of loops. While debugging it, I kept running across "one-off" errors that were causing boundary violations.
After a particularly enthralling session of fixes, I thought I would post a quickie on how to tell how many times your loop is going to calculate. Assuming a legal* executable loop, A loop will execute |(U - L) ÷ S| +1 times. Where U is the loop's upper-bound; L is the lower-bound; and S is the step. The pipes ("|") represent an integer value (Ex: 8 ÷ 5 =1.6 whereas |8 ÷5| = 1).
Explanation:
When you do division, say "16/4" you are essentially creating an imaginary number line from 0 to 16. (Yes Zero, wait for it.) You then start at 4 then count forward by fours (4,8,12,...) until the next hop will cause you to exceed 16. The number of hops is the result.
To get the number of iterations in a loop you are doing essentially the same thing... Expect you also count the first number of the line then begin your hops. Essentially, you count the Zero as a hop.
Example1:
VB:
For i = 0 To 16 Step 4
C#
for (int i =0; a<16; i+4)
Iterates 0,4,8,12,16.
Example2:
VB:
For i = -3 To 12 Step 5
C#
for (int i =-3; a<12; i+5)
Iterates: -3,2,7,12
*"Legal Loop": I am defining "legal" as a loop that a positive non-zero step, with an upper bound greater than the lower bound, and vice-versa if a negative step is used.
Wednesday 3/19/2008 - Knowledge Base Standards
|
|
comments (0)
|
As some of you know, I am one of the Knowledge Base Approvers for VBA Express.The VBA Express Knowledge Base is a compendium of how-to articles forcode written in VBA (Visual basic for Applications) geared towardsteaching beginners.
The staff and membership have begundiscussions of implementing an agreed upon standard for the approval ofsuch articles. By popular request I am making available the firstproposed Draft. Please remember this is just a rough first copy, andwill likely change many time before being finalized.
I am currently soliciting feedback; please contact me via the board with your thoughts.