http://www.songhaysystem.com/kb/subject/vba The Funky Knowledge Base at songhaysystem.com. The subject of this channel is Visual Basic for Applications. Sat, 12 Feb 2005 09:21:57 GMT http://www.songhaysystem.com/kb/number/2076071644/subject/vba Article Excerpt: Public Sub RepairAttributeViolations(ByVal NodeBaseName As String, _ BadAttrBaseName As String, NewAttrBaseName As String) ' 'Fixes attribute-level schema violation problem 'when schema files are updated or replaced. ' Dim nodes As… Sat, 12 Feb 2005 09:21:57 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071641/subject/vba Article Excerpt: Function basIsEmailValid(ByVal EmailAddr As String) As Boolean 'This function checks for a valid email address. Dim lngPosAtSymbol As Long Dim lngPosPeriod As Long Dim lngPosSpace As Long Dim lngLength As Long basIsEmailValid … Fri, 04 Feb 2005 05:05:23 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071642/subject/vba Article Excerpt: Option Compare Database Option Explicit Private x1a0(9) As Long Private cle(17) As Long Private x1a2 As Long Private inter As Long Private res As Long Private ax As Long Private bx As Long Private cx As Long Private dx As Long Private si As Long Priv… Fri, 04 Feb 2005 05:09:12 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071638/subject/vba Article Excerpt: "If you use the objects that are available in the Microsoft VBScript Regular Expressions 5.5 library, you can compare a string against a specific pattern, replace a string pattern with another string, or retrieve only portions of a formatted string. This … Thu, 27 Jan 2005 00:01:44 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071635/subject/vba Article Excerpt: Option Explicit ' ' rasx 05/25/1999 ' Public Function CountStr(ByVal Str As String, SubStr As String, _ Optional StartPos = 1) As Long Dim lngCount If VBA.VarType(StartPos) <> vbEmpty Then _ If VBA.VarType(StartPos) <> vbInteger… Thu, 20 Jan 2005 20:44:44 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071446/subject/vba Article Excerpt: Private Sub basFavoritesScan(ByRef objADO As rasxADO.ADOServices, _ Optional ByVal Directory = "\") Dim objFolders As Scripting.Folders Dim objFolder As Scripting.Folder Dim objFiles As Scripting.Files Dim objFile As Scripting.File … Tue, 15 Jan 2002 09:10:49 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071417/subject/vba Article Excerpt: Public Sub TogglePicturePlaceHolder() Dim intIndex As Integer If Application.Documents.Count = 0 Then Exit Sub If Application.ActiveDocument.InlineShapes.Count = 0 Then Exit Sub With ActiveWindow.View .ShowPicturePlaceHolder… Mon, 29 Oct 2001 02:10:38 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071401/subject/vba Article Excerpt: Public Sub GetHTMLStyles() Dim objParagraph As Word.Paragraph Dim objStyle As Word.Style, blnHTMLStyleFound As Boolean 'Check for Word document with Word HTML Styles. With Application If .Documents.Count = 0 Then Call … Mon, 29 Oct 2001 02:09:49 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071327/subject/vba Article Excerpt: The code written to automate Oulook is stored in a file called VbaProject.OTM. As of this writing, the locations of this file across Windows platforms is detailed at the following location: http://msdn.microsoft.com /library/officedev/odeopg … Wed, 08 Nov 2000 04:02:00 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071292/subject/vba Article Excerpt: Function basRandomNum(Lower As Long, Upper As Long) basRandomNum = VBA.Int((Upper - Lower + 1) * VBA.Rnd + Lower) End Function Tue, 15 Aug 2000 00:52:00 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2076071293/subject/vba Article Excerpt: Public Sub basSendMail() Dim objMailItem As MailItem Set objMailItem = Application.CreateItem(olMailItem) With objMailItem .To = "rasx@kintespace.com" .Subject = "Is Haley's Comet for You?" .Body = "This is t… Tue, 15 Aug 2000 01:07:00 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/950165041/subject/vba Article Excerpt: Back in the days of VB 5.0, Keith Pleas in "Avoid Programming Pitfalls" (currently at http://msdn.microsoft.com/library/periodic/period98/kp0398.htm) writes: [begin quote] To keep from being embarrassed, you should know these terms: "": A zero-length s… Thu, 02 Mar 2000 03:31:45 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/900911580/subject/vba Article Excerpt: Mod in Ordinal Extractions In the same manner that sub-strings can be extracted from strings with the Left() function, the Mod operator can be used to extract digits from integer values: (19984 - (19984 Mod 10))/10 = 1998 The Mod operator used like… Tue, 11 Jan 2000 05:20:01 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/933407838/subject/vba Article Excerpt: Public Function basEOM(TheDate As Date) Dim dtmNextMonth As Date dtmNextMonth = VBA.DateAdd("m", 1, TheDate) ' 'By subtracting all of the days out of dtmNextMonth 'for a given date (TheDate), the last day of the previous … Sat, 31 Jul 1999 16:00:09 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/2006695784/subject/vba Article Excerpt: In many financial applications text files are sometimes needed for automated input of data. Some data feeds are text files containing fixed-width columns. Oftentimes these columns are right-justified with the form: XXX123 X12345 XXX234 where… Wed, 19 May 1999 03:16:26 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/-1331745693/subject/vba Article Excerpt: Use the Val() function if you are contemplating using LTrim$() and CDbl() on a string literal. You only want the leftmost characters from this string. You want to separate the numeric values from the non-numeric values from this string---and the numeric v… Wed, 19 May 1999 03:02:41 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/896215984/subject/vba Article Excerpt: If you have ever wondered why a VBA design-time error is generated when passing arguments wrapped in parentheses to a Sub procedure, then you have forgotten about the importance of the Call keyword. For example, MySub(arg1, arg2) 'will generate an e… Sun, 25 Apr 1999 06:00:13 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/922826868/subject/vba Article Excerpt: 'The following design should speak for itself: Select Case VBA.MsgBox("Yes, No or Cancel?", _ vbQuestion + vbYesNoCancel) Case vbYes 'The Yes case. Case vbNo 'The No case. Case vbCancel … Wed, 31 Mar 1999 05:58:36 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/1131791619/subject/vba Article Excerpt: Since Optional function arguments are variants, the VarType() function should be used to "sub-type" these arguments for a non-variant data type. The sample code fragment below illustrates the form: Public Sub OpenTextFile(FileName As String, _ Op… Wed, 10 Feb 1999 02:21:46 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/-589421703/subject/vba Article Excerpt: I have decided to use fully qualified references to OLE Automation Libraries---even those considered "default" like the VB, VBA and Application objects. I do this because Microsoft says it does not hurt (it may improve performance slightly) and it allows … Fri, 05 Feb 1999 17:59:32 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/901832194/subject/vba Article Excerpt: Public Function basGetDigits(ByVal Literal as String, _ Optional ByVal InclDec = False) As String Dim lngCount As Long Dim strChar As String Dim strRet As String Dim strCmp As String If InclDec Then strCmp = "1234567890."… Tue, 12 Jan 1999 05:35:05 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/81051056/subject/vba Article Excerpt: When a variable is passed to a function, by default (since VB 4.0) it is passed by reference. If the variable is local to the procedure calling the function, an error will be generated if the function does not use the ByVal keyword in the argument list. H… Tue, 12 Jan 1999 05:34:33 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/911637588/subject/vba Article Excerpt: Public Function basScan() As Boolean Dim strPath As String, strName As String On Error GoTo basScan_Err basScan = True strPath = "C:\" strName = VBA.Dir(strPath, vbDirectory) Do While strName <> Empty … Tue, 01 Dec 1998 02:36:08 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/362287989/subject/vba Article Excerpt: Using multiple conditions in the Do Loop structure with the Like operator do not always evaluate properly. In such cases, the use of Exit Do helps. For example, the following Do Loop condition is never true: Do Until g_str Like "*PAGE*" Or g_str Like… Thu, 29 Oct 1998 20:54:50 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/892576584/subject/vba Article Excerpt: If VBA.MsgBox("Are you sure?", vbYesNo + _ vbDefaultButton2 + vbQuestion) = vbNo Then 'User is not sure. End If Thu, 29 Oct 1998 20:48:31 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/904007897/subject/vba Article Excerpt: Public Function basMyFunc() As Boolean On Error Goto basMyFunc_Err basMyFunc = True 'Code for function goes here. basMyFunc_Exit: 'Clean-up code goes here. Exit Function basMyFunc_Err: Select Case VBA.Err 'Case <Err numb… Thu, 29 Oct 1998 20:50:40 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/-165293778/subject/vba Article Excerpt: Public Sub basDelaySec(ByVal Seconds As Long) Dim dtmDate as Date dtmDate = DateAdd("s", Seconds, Now) Do DoEvents Loop Until Now > dtmDate End Sub Thu, 29 Oct 1998 20:39:10 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/899226217/subject/vba Article Excerpt: To avoid Run-time Error 94 (Invalid Use of Null) when using type conversion functions, "protect" them by concatenating "default" values where logically possible. Suppose that g_varReturn is a variant. We can use type conversion functions on g_varReturn wi… Tue, 30 Jun 1998 17:12:05 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/-1062179649/subject/vba Article Excerpt: The online help for VBA has a topic called "Data Type Summary." For those of us who are not computer scientists we should take note of the following with regard to floating point numbers: The use of scientific notation in the help file topic to denote ra… Sat, 13 Jun 1998 06:03:15 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/183/subject/vba Article Excerpt: Wrong. VBA can tell what day it is. VBA has the Weekday() function. Tue, 19 May 1998 16:14:05 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/125/subject/vba Article Excerpt: The following code fragment demonstrates how the FreeFile function searches for a free file number when opening a file: Dim i As Integer i = FreeFile Open <file> For Input As #i Do Until EOF(i) <stuff> Loop Close #… Tue, 19 May 1998 16:17:43 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/893286145/subject/vba Article Excerpt: The Timer function resets itself at 12 midnight each day. Its value never exceeds 86,400 (24hrs/day * 60mins/hr * 60sec/min). Wed, 22 Apr 1998 23:41:30 GMT Bryan Wilhite http://www.songhaysystem.com/kb/number/891062485/subject/vba Article Excerpt: You will always have to subtract one from a zero-based group when iterating via the number of items in the group. For example, to iterate through the Fields collection of a TableDef object based on the Count property, you need to subtract 1 from the Count… Sat, 28 Mar 1998 07:36:00 GMT Bryan Wilhite