Rectangle 27 1

Is this question about finding pdf files to merge using their filenames or about merging PDF files using Acrobat API through VBA?

In case you need to find the best workflow then I suppose you should find all files in the folder and find the find the "ticker" filename using regular expressions (see this awesome answer about regular expressions in VBA and this tutorial on matching filenames using regular expressions in VBA).

I am hoping to find files within a set, static directory and then merge using their filenames. I would have a variable string and a static string for each of two files, and a static file and directory for the third. I suspect it would be something like: merge "*[static RootDoc append].pdf" with "*[static DefDoc append].pdf" by adding *[static RootDoc append].pdf" after page 0 (a cover page)

The reason I'm thinking in VBA is that there are other set scripts I have that run on the source excel docs to convert to pdf.

It is not quite clear to me but if seems like you should first 1) find all PDF files in the given folder (like this mrexcel.com/forum/excel-questions/ ) and then 2) process each file in the folder and merge with 2 others?

I am able to generate a list of all filenames and directories to be used in a set of 3 columns. I am thinking of using a For Each loop for the range of rows and then column offsets to open each file. I am still unclear on how to word the merge operation.

excel vba - Open and Merge multiple PDF files using VBA - Stack Overfl...

vba excel-vba pdf merge pdf-generation
Rectangle 27 486

VBA

Excel 2007 Excel 2010 Excel 2013 - 32 bit version. Excel 2016 - 32 bit version.

  • The VBE will call a system function to create the password dialog box.
  • if this value is 1, Excel will "think" that the password is right, hence the locked VBA project will be opened.
  • If user enters the right password and click OK, this function returns 1. If user enters the wrong password or click Cancel, this function returns 0.
  • After the dialog box is closed, the VBE checks the returned value of the system function
  • if this value is 1, the VBE will "think" that the password is right, hence the locked VBA project will be opened.
  • The code below swaps the memory of the original function used to display the password dialog with a user defined function that will always return 1 when being called.
  • Open the file(s) that contain your locked VBA Projects
  • Create a new xlsm file and store this code in Module1 code credited to Siwtom (nick name), a Vietnamese developer Option Explicit Private Const PAGE_EXECUTE_READWRITE = &H40 Private Declare Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" _ (Destination As Long, Source As Long, ByVal Length As Long) Private Declare Function VirtualProtect Lib "kernel32" (lpAddress As Long, _ ByVal dwSize As Long, ByVal flNewProtect As Long, lpflOldProtect As Long) As Long Private Declare Function GetModuleHandleA Lib "kernel32" (ByVal lpModuleName As String) As Long Private Declare Function GetProcAddress Lib "kernel32" (ByVal hModule As Long, _ ByVal lpProcName As String) As Long Private Declare Function DialogBoxParam Lib "user32" Alias "DialogBoxParamA" (ByVal hInstance As Long, _ ByVal pTemplateName As Long, ByVal hWndParent As Long, _ ByVal lpDialogFunc As Long, ByVal dwInitParam As Long) As Integer Dim HookBytes(0 To 5) As Byte Dim OriginBytes(0 To 5) As Byte Dim pFunc As Long Dim Flag As Boolean Private Function GetPtr(ByVal Value As Long) As Long GetPtr = Value End Function Public Sub RecoverBytes() If Flag Then MoveMemory ByVal pFunc, ByVal VarPtr(OriginBytes(0)), 6 End Sub Public Function Hook() As Boolean Dim TmpBytes(0 To 5) As Byte Dim p As Long Dim OriginProtect As Long Hook = False pFunc = GetProcAddress(GetModuleHandleA("user32.dll"), "DialogBoxParamA") If VirtualProtect(ByVal pFunc, 6, PAGE_EXECUTE_READWRITE, OriginProtect) <> 0 Then MoveMemory ByVal VarPtr(TmpBytes(0)), ByVal pFunc, 6 If TmpBytes(0) <> &H68 Then MoveMemory ByVal VarPtr(OriginBytes(0)), ByVal pFunc, 6 p = GetPtr(AddressOf MyDialogBoxParam) HookBytes(0) = &H68 MoveMemory ByVal VarPtr(HookBytes(1)), ByVal VarPtr(p), 4 HookBytes(5) = &HC3 MoveMemory ByVal pFunc, ByVal VarPtr(HookBytes(0)), 6 Flag = True Hook = True End If End If End Function Private Function MyDialogBoxParam(ByVal hInstance As Long, _ ByVal pTemplateName As Long, ByVal hWndParent As Long, _ ByVal lpDialogFunc As Long, ByVal dwInitParam As Long) As Integer If pTemplateName = 4070 Then MyDialogBoxParam = 1 Else RecoverBytes MyDialogBoxParam = DialogBoxParam(hInstance, pTemplateName, _ hWndParent, lpDialogFunc, dwInitParam) Hook End If End Function

Tested and works on .xlsm file from Excel 2013.

This needs to be upvoted a lot.

  • Paste this code in Module2 and run it Sub unprotected() If Hook Then MsgBox "VBA Project is unprotected!", vbInformation, "*****" End If End Sub

Brilliant answer, the standout solution here

Some explanation would be nice of how this is working.

Now the only question left (after seeing this impressive method works perfectly), is how the hell can I make my VBA project protected stronger to prevent others from using this hack on it :)

This code works perfectly in unlocking the VBA code although each time I have used this it prevents me from re-protecting the project with a different password, has anyone else had this problem?

Is there a way to crack the password on an Excel VBA Project? - Stack ...

excel excel-vba passwords
Rectangle 27 473

VBA

Excel 2007 Excel 2010 Excel 2013 - 32 bit version. Excel 2016 - 32 bit version.

  • The VBE will call a system function to create the password dialog box.
  • If user enters the right password and click OK, this function returns 1. If user enters the wrong password or click Cancel, this function returns 0.
  • After the dialog box is closed, the VBE checks the returned value of the system function
  • if this value is 1, the VBE will "think" that the password is right, hence the locked VBA project will be opened.
  • The code below swaps the memory of the original function used to display the password dialog with a user defined function that will always return 1 when being called.
  • Open the file(s) that contain your locked VBA Projects
  • Create a new xlsm file and store this code in Module1 code credited to Siwtom (nick name), a Vietnamese developer Option Explicit Private Const PAGE_EXECUTE_READWRITE = &H40 Private Declare Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" _ (Destination As Long, Source As Long, ByVal Length As Long) Private Declare Function VirtualProtect Lib "kernel32" (lpAddress As Long, _ ByVal dwSize As Long, ByVal flNewProtect As Long, lpflOldProtect As Long) As Long Private Declare Function GetModuleHandleA Lib "kernel32" (ByVal lpModuleName As String) As Long Private Declare Function GetProcAddress Lib "kernel32" (ByVal hModule As Long, _ ByVal lpProcName As String) As Long Private Declare Function DialogBoxParam Lib "user32" Alias "DialogBoxParamA" (ByVal hInstance As Long, _ ByVal pTemplateName As Long, ByVal hWndParent As Long, _ ByVal lpDialogFunc As Long, ByVal dwInitParam As Long) As Integer Dim HookBytes(0 To 5) As Byte Dim OriginBytes(0 To 5) As Byte Dim pFunc As Long Dim Flag As Boolean Private Function GetPtr(ByVal Value As Long) As Long GetPtr = Value End Function Public Sub RecoverBytes() If Flag Then MoveMemory ByVal pFunc, ByVal VarPtr(OriginBytes(0)), 6 End Sub Public Function Hook() As Boolean Dim TmpBytes(0 To 5) As Byte Dim p As Long Dim OriginProtect As Long Hook = False pFunc = GetProcAddress(GetModuleHandleA("user32.dll"), "DialogBoxParamA") If VirtualProtect(ByVal pFunc, 6, PAGE_EXECUTE_READWRITE, OriginProtect) <> 0 Then MoveMemory ByVal VarPtr(TmpBytes(0)), ByVal pFunc, 6 If TmpBytes(0) <> &H68 Then MoveMemory ByVal VarPtr(OriginBytes(0)), ByVal pFunc, 6 p = GetPtr(AddressOf MyDialogBoxParam) HookBytes(0) = &H68 MoveMemory ByVal VarPtr(HookBytes(1)), ByVal VarPtr(p), 4 HookBytes(5) = &HC3 MoveMemory ByVal pFunc, ByVal VarPtr(HookBytes(0)), 6 Flag = True Hook = True End If End If End Function Private Function MyDialogBoxParam(ByVal hInstance As Long, _ ByVal pTemplateName As Long, ByVal hWndParent As Long, _ ByVal lpDialogFunc As Long, ByVal dwInitParam As Long) As Integer If pTemplateName = 4070 Then MyDialogBoxParam = 1 Else RecoverBytes MyDialogBoxParam = DialogBoxParam(hInstance, pTemplateName, _ hWndParent, lpDialogFunc, dwInitParam) Hook End If End Function
  • Paste this code in Module2 and run it Sub unprotected() If Hook Then MsgBox "VBA Project is unprotected!", vbInformation, "*****" End If End Sub

Tested and works on .xlsm file from Excel 2013.

Works perfect for me on excel 2010 as well...

Some explanation would be nice of how this is working.

This code works perfectly in unlocking the VBA code although each time I have used this it prevents me from re-protecting the project with a different password, has anyone else had this problem?

Is there a way to crack the password on an Excel VBA Project? - Stack ...

excel excel-vba passwords
Rectangle 27 355

In the VBA editor, go to View, Toolbars, Customise... or right click on the tool bar and select Customise...

Under the Commands tab, select the Edit menu on the left.

Then approximately two thirds of the way down there's two icons, Comment Block and Uncomment Block.

Drag and drop these onto your toolbar and then you have easy access to highlight a block of code, and comment it out and uncomment with the click of a button!

That's handy - I can't believe I'd never found it before. A shortcut key would be really handy but unfortunately that doesn't seem to exist.

Same here. Hence thinking it's worthy of being posted! All these years of using the [poor] VBA editor, and something that is basically essential for decent testing, and it was there all along :)

Another button that belongs next to them (even though it's a little easier to find) is the Compile VBAProject button from the Debug menu. It's the three commands I use the most!

Olle: Right-click the menu bar, choose Customize (or choose View | Toolbars | Customize). Click the Commands tab. Drag any command you want to any toolbar you'd like to have it on. Bless whoever at MS didn't care enough about VBA to "improve" the UI the way they did the main apps.

Or just right click on the toolbar and turn on the Edit toolbar - the Comment and Uncomment buttons are included there.

How to comment and uncomment blocks of code in the Office VBA Editor -...

vba
Rectangle 27 72

  • Right-click on the toolbar and select Customize...
  • Drag the Comment Block entry onto the Menu Bar (yep! the menu bar)Note: You should now see a new icon on the menu bar.
  • Make sure that the new icon is highlighted (it will have a black square around it) then click Modify Selection button on the Customize dialog box.
  • An interesting menu will popup. Under name, add an ampersand (&) to the beginning of the entry. So now instead of "Comment Block" it should read &Comment Block. Press Enter to save the change.

Image and Text

  • Dismiss the Customize dialog box.
  • Highlight any block of code and press Alt-C. Voila.
  • Do the same thing for the Uncomment Block or any other commands that you find yourself using often.

Though this answers is same as correct answer, he has shown a trick to add shortcut. +1 for that.

How would "uncommenting" work?

In Office 2010, this also works if you choose "Text Only (Always)" in Step 7, rather than "Image and Text." It's obnoxious that it doesn't work if you choose any other display option, but at least on my screen I prefer to save a little space with "Text Only."

How to comment and uncomment blocks of code in the Office VBA Editor -...

vba
Rectangle 27 19

There is a built-in Edit toolbar in the VBA editor that has the Comment Block and Uncomment Block buttons by default, and other useful tools.

If you right-click any toolbar or menu (or go to the View menu > Toolbars), you will see a list of available toolbars (above the "Customize..." option). The Standard toolbar is selected by default. Select the Edit toolbar and the new toolbar will appear, with the Comment Block buttons in the middle.

*This is a simpler option to the ones mentioned.

How to comment and uncomment blocks of code in the Office VBA Editor -...

vba
Rectangle 27 19

Have you checked MZTools?? It does a lot of cool stuff...

If I'm not wrong, one of the functionalities it offers is to set your own shortcuts.

How to comment and uncomment blocks of code in the Office VBA Editor -...

vba
Rectangle 27 143

For nearly all programming purposes, VBA and VB 6.0 are the same thing.

VBA cannot compile your program into an executable binary. You'll always need the host (a Word file and MS Word, for example) to contain and execute your project. You'll also not be able to create COM DLLs with VBA.

Apart from that, there is a difference in the IDE - the VB 6.0 IDE is more powerful in comparison. On the other hand, you have tight integration of the host application in VBA. Application-global objects (like "ActiveDocument") and events are available without declaration, so application-specific programming is straight-forward.

Still, nothing keeps you from firing up Word, loading the VBA IDE and solving a problem that has no relation to Word whatsoever. I'm not sure if there is anything that VB 6.0 can do (technically), and VBA cannot. I'm looking for a comparison sheet on the MSDN though.

There seems to be no concise comparison page on the microsoft.com pages, or they hid them well. Suffice it to say that VB 6.0 code runs unaltered in VBA, unless you make references to COM objects that are not shipped with VBA, of course.

VBA and Vb6 use the same dll file, which is why the code runs in either. However, there is something like a Printer object in VB6 that's not in VBA and I don't know why that is. Otherwise, I believe there are no differences in the base languages.

Which is why I think it's worth adding that opening an Office Document carries nearly the same risk as opening an executable.

VB also has the Clipboard, Screen, and App objects, in addition to the Printers collection. The forms packages are completely different, as you have to go out of your way to avoid windowed controls in VB, but in VBA it's a lot harder to use hWnd-oriented API calls because most of the controls are windowless. And, speaking of controls, you can author your own in VB and use them in VBA as well as elsewhere. Lots of other niggles - see the Object Browser (press F2 in the IDE).

@eddyparkinson Nobody in this thread talks about VB.NET. Microsoft did a good job of creating confusion by calling "Visual Basic.NET" just "Visual Basic" all the time, while not talking about the actual Visual Basic anymore. Don't fall for this. VB.NET is a completely, radically different language. This thread is about the classic "Visual Basic" and the classic "Visual Basic for Applications".

vb6 - Difference between Visual Basic 6.0 and VBA - Stack Overflow

vba vb6
Rectangle 27 3

Make the Vb6 app into an ActiveX Exe project. Here's the VB6 manual tutorial on creating an ActiveX exe. Add a reference to the vb6 from the VBA code. You will be able to call into objects in the Vb6 from your VBA.

Use GetObject to instantiate a Vb6 object from the VBA. That will connect to any existing instance of the vb6 app, or start a new instance if necessary.

Thanks for the great VB6 tutorial - I learned a lot from that. I can now call and interact with the classes in the VB project, but I haven't figured out how to interact with the controls (textboxes, listboxes, etc). Any suggestions?

@user425751 Create methods in the public objects that interact with the controls on your behalf. You can't manipulate the controls directly from outside the exe, but you can call methods that do it for you.

Populate fields in VB6 executable from MS office VBA - Stack Overflow

vba vb6
Rectangle 27 601

To use in Excel follow these steps :

  • In "Microsoft Visual Basic for Applications" window select "Tools" from the top menu.
  • Check the box next to "Microsoft VBScript Regular Expressions 5.5" to include in your workbook.
  • E.g. a-z matches an lower case letters from a to z
0-5

[] Match exactly one of the objects inside these brackets.

  • E.g. [a] matches the letter a
  • E.g. [abc] matches a single letter which can be a, b or c
  • E.g. [a-z] matches any single lower case letter of the alphabet.
()

{} Multiplier for repeated copies of pattern defined before it.

[a]{2}
[a]{1,3}

+ Match at least one, or more, of the pattern defined before it.

a+
aa
aaa

? Match zero or one of the pattern defined before it.

  • E.g. Pattern may or may not be present but can only be matched one time.
[a-z]?

* Match zero or more of the pattern defined before it. - E.g. Wildcard for pattern that may or may not be present. - E.g. [a-z]* matches empty string or string of lower case letters.

  • E.g. a. Matches a two character string starting with a and ending with anything except \n
  • E.g. a|b means either a or b can be matched.
  • E.g. red|white|orange matches exactly one of the colors.
  • E.g. [^0-9] character can not contain a number
  • E.g. [^aA] character can not be lower case a or upper case A

\ Escapes special character that follows (overrides above behavior)

\.
\\
\(
\?
\$
\^
  • E.g. ^a First character must be lower case letter a
  • E.g. ^[0-9] First character must be a number.
  • E.g. a$ Last character must be lower case letter a
Order  Name                Representation
1      Parentheses         ( )
2      Multipliers         ? + * {m,n} {m, n}?
3      Sequence & Anchors  abc ^ $
4      Alternation         |
abr    same as       meaning
\d     [0-9]         Any single digit
\D     [^0-9]        Any single character that's not a digit
\w     [a-zA-Z0-9_]  Any word character
\W     [^a-zA-Z0-9_] Any non-word character
\s     [ \r\t\n\f]   Any space character
\S     [^ \r\t\n\f]  Any non-space character
\n     [\n]          New line

The following example macro looks at the value in cell A1 to see if the first 1 or 2 characters are digits. If so, they are removed and the rest of the string is displayed. If not, then a box appears telling you that no match is found. Cell A1 values of 12abc will return abc, value of 1abc will return abc, value of abc123 will return "Not Matched" because the digits were not at the start of the string.

Function simpleCellRegex(Myrange As Range) As String
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String


    strPattern = "^[0-9]{1,3}"

    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.test(strInput) Then
            simpleCellRegex = regEx.Replace(strInput, strReplace)
        Else
            simpleCellRegex = "Not matched"
        End If
    End If
End Function

Place your strings ("12abc") in cell A1. Enter this formula =simpleCellRegex(A1) in cell B1 and the result will be "abc".

This example is the same as example 1 but loops through a range of cells.

Private Sub simpleRegex()
    Dim strPattern As String: strPattern = "^[0-9]{1,2}"
    Dim strReplace As String: strReplace = ""
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A5")

    For Each cell In Myrange
        If strPattern <> "" Then
            strInput = cell.Value

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            If regEx.Test(strInput) Then
                MsgBox (regEx.Replace(strInput, strReplace))
            Else
                MsgBox ("Not matched")
            End If
        End If
    Next
End Sub

This example loops through a range (A1, A2 & A3) and looks for a string starting with three digits followed by a single alpha character and then 4 numeric digits. The output splits apart the pattern matches into adjacent cells by using the (). $1 represents the first pattern matched within the first set of ().

Private Sub splitUpRegexPattern()
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A3")

    For Each C In Myrange
        strPattern = "(^[0-9]{3})([a-zA-Z])([0-9]{4})"

        If strPattern <> "" Then
            strInput = C.Value
            strReplace = "$1"

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            If regEx.test(strInput) Then
                C.Offset(0, 1) = regEx.Replace(strInput, "$1")
                C.Offset(0, 2) = regEx.Replace(strInput, "$2")
                C.Offset(0, 3) = regEx.Replace(strInput, "$3")
            Else
                C.Offset(0, 1) = "(Not matched)"
            End If
        End If
    Next
End Sub
String   Regex Pattern                  Explanation
a1aaa    [a-zA-Z][0-9][a-zA-Z]{3}       Single alpha, single digit, three alpha characters
a1aaa    [a-zA-Z]?[0-9][a-zA-Z]{3}      May or may not have preceeding alpha character
a1aaa    [a-zA-Z][0-9][a-zA-Z]{0,3}     Single alpha, single digit, 0 to 3 alpha characters
a1aaa    [a-zA-Z][0-9][a-zA-Z]*         Single alpha, single digit, followed by any number of alpha characters

</i8>    \<\/[a-zA-Z][0-9]\>            Exact non-word character except any single alpha followed by any single digit

You should not forget to Set regEx = Nothing. You will get Out Of Memory exceptions, when that Sub is executed frequently enought.

Yay, now you can write an HTML parser in VBA6 using regular expressions!

Set regEx = CreateObject("VBScript.RegExp")

vba - How to use Regular Expressions (Regex) in Microsoft Excel both i...

regex vba excel-vba
Rectangle 27 587

To use in Excel follow these steps :

  • In "Microsoft Visual Basic for Applications" window select "Tools" from the top menu.
  • Check the box next to "Microsoft VBScript Regular Expressions 5.5" to include in your workbook.
  • E.g. a-z matches an lower case letters from a to z
0-5

[] Match exactly one of the objects inside these brackets.

  • E.g. [a] matches the letter a
  • E.g. [abc] matches a single letter which can be a, b or c
  • E.g. [a-z] matches any single lower case letter of the alphabet.
()

{} Multiplier for repeated copies of pattern defined before it.

[a]{2}
[a]{1,3}

+ Match at least one, or more, of the pattern defined before it.

a+
aa
aaa

? Match zero or one of the pattern defined before it.

  • E.g. Pattern may or may not be present but can only be matched one time.
[a-z]?

* Match zero or more of the pattern defined before it. - E.g. Wildcard for pattern that may or may not be present. - E.g. [a-z]* matches empty string or string of lower case letters.

  • E.g. a. Matches a two character string starting with a and ending with anything except \n
  • E.g. a|b means either a or b can be matched.
  • E.g. red|white|orange matches exactly one of the colors.
  • E.g. [^0-9] character can not contain a number
  • E.g. [^aA] character can not be lower case a or upper case A

\ Escapes special character that follows (overrides above behavior)

\.
\\
\(
\?
\$
\^
  • E.g. ^a First character must be lower case letter a
  • E.g. ^[0-9] First character must be a number.
  • E.g. a$ Last character must be lower case letter a
Order  Name                Representation
1      Parentheses         ( )
2      Multipliers         ? + * {m,n} {m, n}?
3      Sequence & Anchors  abc ^ $
4      Alternation         |
abr    same as       meaning
\d     [0-9]         Any single digit
\D     [^0-9]        Any single character that's not a digit
\w     [a-zA-Z0-9_]  Any word character
\W     [^a-zA-Z0-9_] Any non-word character
\s     [ \r\t\n\f]   Any space character
\S     [^ \r\t\n\f]  Any non-space character
\n     [\n]          New line

The following example macro looks at the value in cell A1 to see if the first 1 or 2 characters are digits. If so, they are removed and the rest of the string is displayed. If not, then a box appears telling you that no match is found. Cell A1 values of 12abc will return abc, value of 1abc will return abc, value of abc123 will return "Not Matched" because the digits were not at the start of the string.

Function simpleCellRegex(Myrange As Range) As String
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String


    strPattern = "^[0-9]{1,3}"

    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.test(strInput) Then
            simpleCellRegex = regEx.Replace(strInput, strReplace)
        Else
            simpleCellRegex = "Not matched"
        End If
    End If
End Function

Place your strings ("12abc") in cell A1. Enter this formula =simpleCellRegex(A1) in cell B1 and the result will be "abc".

This example is the same as example 1 but loops through a range of cells.

Private Sub simpleRegex()
    Dim strPattern As String: strPattern = "^[0-9]{1,2}"
    Dim strReplace As String: strReplace = ""
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A5")

    For Each cell In Myrange
        If strPattern <> "" Then
            strInput = cell.Value

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            If regEx.Test(strInput) Then
                MsgBox (regEx.Replace(strInput, strReplace))
            Else
                MsgBox ("Not matched")
            End If
        End If
    Next
End Sub

This example loops through a range (A1, A2 & A3) and looks for a string starting with three digits followed by a single alpha character and then 4 numeric digits. The output splits apart the pattern matches into adjacent cells by using the (). $1 represents the first pattern matched within the first set of ().

Private Sub splitUpRegexPattern()
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A3")

    For Each C In Myrange
        strPattern = "(^[0-9]{3})([a-zA-Z])([0-9]{4})"

        If strPattern <> "" Then
            strInput = C.Value
            strReplace = "$1"

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            If regEx.test(strInput) Then
                C.Offset(0, 1) = regEx.Replace(strInput, "$1")
                C.Offset(0, 2) = regEx.Replace(strInput, "$2")
                C.Offset(0, 3) = regEx.Replace(strInput, "$3")
            Else
                C.Offset(0, 1) = "(Not matched)"
            End If
        End If
    Next
End Sub
String   Regex Pattern                  Explanation
a1aaa    [a-zA-Z][0-9][a-zA-Z]{3}       Single alpha, single digit, three alpha characters
a1aaa    [a-zA-Z]?[0-9][a-zA-Z]{3}      May or may not have preceeding alpha character
a1aaa    [a-zA-Z][0-9][a-zA-Z]{0,3}     Single alpha, single digit, 0 to 3 alpha characters
a1aaa    [a-zA-Z][0-9][a-zA-Z]*         Single alpha, single digit, followed by any number of alpha characters

</i8>    \<\/[a-zA-Z][0-9]\>            Exact non-word character except any single alpha followed by any single digit

You should not forget to Set regEx = Nothing. You will get Out Of Memory exceptions, when that Sub is executed frequently enought.

Yay, now you can write an HTML parser in VBA6 using regular expressions!

Set regEx = CreateObject("VBScript.RegExp")

vba - How to use Regular Expressions (Regex) in Microsoft Excel both i...

regex vba excel-vba
Rectangle 27 62

I don't know, whether this is the best approach to the problem or not, but here is how I got it to work. I referenced the Microsoft XML, v2.6 dll in my VBA, and then the following code snippet, gives me the required values

Dim objXML As MSXML2.DOMDocument

    Set objXML = New MSXML2.DOMDocument

    If Not objXML.loadXML(strXML) Then  'strXML is the string with XML'
        Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason
    End If

Dim point As IXMLDOMNode
Set point = objXML.firstChild

Debug.Print point.selectSingleNode("X").Text
Debug.Print point.selectSingleNode("Y").Text

When I try to debug.print one of those points, I get an object variable or With block variable not set. Any suggestions?

How to parse XML using vba - Stack Overflow

xml vba
Rectangle 27 7

Just reviewing what you've got here...

The code would read much better with proper indentation:

Function FillInternetForm()
    Dim HWNDSrc As Long
    Dim ie As Object

    Set ie = CreateObject("InternetExplorer.Application")
    HWNDSrc = ie.HWND

    ie.Navigate "http://helppointinfo.farmersinsurance.com/OCR/Labor_Rates/laborrates.asp"
    ie.Visible = True
    While ie.Busy
        DoEvents  'wait until IE is done loading page.
    Wend
    ie.Document.getElementById("DirectZip").Value = Sheets("NAT").Range("C2").Value

    SetForegroundWindow HWNDSrc

    Application.SendKeys "{TAB 11}", True
    DoEvents
    Application.SendKeys "{NUMLOCK}", True

End Function

Public Sub RunRates()
    Call FillInternetForm
End Sub

As answered in this StackOverflow question, the Call instrucation is a relic from ancient versions of VB, it's not needed and, IMO, only adds clutter.

Public Sub RunRates()
    FillInternetForm
End Sub

The FillInternetForm function is needlessly coupled with the Excel object model - Sheets("NAT").Range("C2").Value should be passed as a String parameter to the function:

Function FillInternetForm(ByVal DirectZipValue As String)
    '...
    ie.Document.getElementById("DirectZip").Value = DirectZipValue
    '...
End Function

Public Sub RunRates()
    FillInternetForm Sheets("NAT").Range("C2").Value
End Sub

VB functions are procedures with a return value. If it's not specified, then it's returning a Variant - here FillInternetForm is never assigned a return value, and whatever it would be returning wouldn't be used. In other words, you have a procedure (Sub), not a function. The signature should be modified like this:

Public Sub FillInternetForm(ByVal DirectZipValue As String)

I like things explicit - if a member is going to be Private, it needs a Private access modifier; if it's going to be Public, I don't like relying on VB's "defaults", mostly because I code in different languages where these defaults differ (C#). Having explicit access modifiers eliminate the possible confusion, but that might be only me.

Lastly, I don't understand why FillInternetForm would have to press NUM LOCK, this looks misplaced, and has a side-effect that could be surprising to whoever is running that code.

Thank you for the response - haven't gotten a chance, as of yet, to take your suggestions, but wanted to answer the Num Lock question. I have it in place because when I run the Sendkeys it actually turns the Num Lock off. By having this code it place it will turn it back on.

That would make a good reason to put a comment, so that call doesn't get inadvertantly removed by a future maintainer (could be future you!) - feel free to upvote any useful answers you get ;)

My overall plan with this code is to return the values from the webpage back into the excel sheet: 1. Wouldn't I need to leave it as a funciton as I am seeking to return a value? 2. Do you know of any articles that will return results from iframe?

If FillInternetForm should return a value, then you're missing a line of code that would assign such a return value (FillInternetForm = TheReturnValue). You're not showing the code that's calling FillInternetForm, but if it's a procedure (sub) it will look like FillInternetForm and if it's a function it will look like Value = FillInternetForm. I don't know of any articles that return results from iframe (maybe Google does), but my initial thought was that if the form is on a .asp page you probably could get away with sending a simple HTTP request with your parameters, to the URL.

It's not part of the code shown bc it's not created yet. I first wanted to make sure sending the information was quick and accurate, then I was going to work on the return portion of it.

vba
Rectangle 27 45

This is a bit of a complicated question, but it seems like the most direct route would be to load the XML document or XML string via MSXML2.DOMDocument which will then allow you to access the XML nodes.

You can find more on MSXML2.DOMDocument at the following sites:

How to parse XML using vba - Stack Overflow

xml vba
Rectangle 27 261

Set dict = CreateObject("Scripting.Dictionary")
Dim dict As New Scripting.Dictionary

Example of use:

If Not dict.Exists(key) Then 
    dict.Add key, value
End If
Nothing
Set dict = Nothing

This data structure type is provided by the scripting runtime, not by VBA. Basically, VBA can use practically any data structure type that is accessible to it via a COM interface.

Just for the sake of completeness: you need to reference the "Microsoft Scripting Runtime" for this to work (go to Tools->References) and check its box.

@ David-W-Fenton: a collection is not keyed

Uh, VBA collections ARE keyed. But maybe we have a different definition of keyed.

I am using Excel 2010... but without the reference to "Microsoft Scripting Runtime" Tools - Ref.. Just doing CreateObject does NOT work. So, @masterjo I think your comment above is wrong. Unless I am missing something.. So, guys Tools -> references is required.

Does VBA have Dictionary Structure? - Stack Overflow

vba dictionary data-structures vb6
Rectangle 27 39

VBA stands for Visual Basic for Applications and so is the small "for applications" scripting brother of VB. VBA is indeed available in Excel, but also in the other office applications.

With VB, one can create a stand-alone windows application, which is not possible with VBA.

It is possible for developers however to "embed" VBA in their own applications, as a scripting language to automate those applications.

A. Microsoft Visual Basic for Applications (VBA) is an embeddable programming environment designed to enable developers to build custom solutions using the full power of Microsoft Visual Basic. Developers using applications that host VBA can automate and extend the application functionality, shortening the development cycle of custom business solutions.

Note that VB.NET is even another language, which only shares syntax with VB.

Yes. That's a fact that makes me want to pull my hair out every time I look for VB or VBA-specific help via Google. Stupid marketing decision.

@Tomalak: That's why I just Noted that ;-)

@DOK: Yes indeed, but that doesn't change the fact that VB.NET is another language than the visual basic that "we developers" know as VB6 or earlier.

@j_random_hacker In this case, it almost feels like MS treats VB.Net a variant of VB, at least based on the visual studio vb page - which would not be accurate. Oddly enough, this wiki article provides more information on its face than the MS visual studio page :) en.wikipedia.org/wiki/Visual_Basic_.NET. However, a MSDN forum question looks to at least touch on the differences: social.msdn.microsoft.com/Forums/en-US/Vsexpressvb/thread/

vb6 - Difference between Visual Basic 6.0 and VBA - Stack Overflow

vba vb6
Rectangle 27 131

To make use of regular expressions directly in Excel formulas the following UDF (user defined function) can be of help. It more or less directly exposes regular expression functionality as an excel function.

  • A text to use the regular expression on.
  • A format string specifying how the result should look. It can contain $0, $1, $2, and so on. $0 is the entire match, $1 and up correspond to the respective match groups in the regular expression. Defaults to $0.
E-Mail: some@email.com, Name: Peter Gordon

To take apart a combined string in a single cell into its components in multiple cells:

=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 2)
Peter Gordon
some@email.com

To use this UDF do the following (roughly based on this Microsoft page. They have some good additional info there!):

ALT+F11
  • Click on Tools -> References (please excuse the german screenshot)

Click on Insert Module. If you give your module a different name make sure the Module does not have the same name as the UDF below (e.g. naming the Module Regex and the function regex causes #NAME! errors).

  • In the big text window in the middle insert the following: Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object Dim replaceNumber As Integer With inputRegexObj .Global = True .MultiLine = True .IgnoreCase = False .Pattern = matchPattern End With With outputRegexObj .Global = True .MultiLine = True .IgnoreCase = False .Pattern = "\$(\d+)" End With With outReplaceRegexObj .Global = True .MultiLine = True .IgnoreCase = False End With Set inputMatches = inputRegexObj.Execute(strInput) If inputMatches.Count = 0 Then regex = False Else Set replaceMatches = outputRegexObj.Execute(outputPattern) For Each replaceMatch In replaceMatches replaceNumber = replaceMatch.SubMatches(0) outReplaceRegexObj.Pattern = "\$" & replaceNumber If replaceNumber = 0 Then outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value) Else If replaceNumber > inputMatches(0).SubMatches.Count Then 'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "." regex = CVErr(xlErrValue) Exit Function Else outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1)) End If End If Next regex = outputPattern End If End Function

This answer combined with the steps here to create an Add-In, has been very helpful. Thank you. Make sure you don't give your module and function the same name!

IMHO. Both answers are good: first is a reference on regexp , second gives the tool :).

Just reiterating the comment above from Chris Hunt. Don't call your Module 'Regex' as well. Thought I was going mad for a while as the function wouldn't work due to a #NAME error

Well, I'm gone nuts as I tried everything (including changing modules/names) and still getting the #NAME error >_> i.imgur.com/UUQ6eCi.png

vba - How to use Regular Expressions (Regex) in Microsoft Excel both i...

regex vba excel-vba
Rectangle 27 131

To make use of regular expressions directly in Excel formulas the following UDF (user defined function) can be of help. It more or less directly exposes regular expression functionality as an excel function.

  • A text to use the regular expression on.
  • A format string specifying how the result should look. It can contain $0, $1, $2, and so on. $0 is the entire match, $1 and up correspond to the respective match groups in the regular expression. Defaults to $0.
E-Mail: some@email.com, Name: Peter Gordon

To take apart a combined string in a single cell into its components in multiple cells:

=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 2)
Peter Gordon
some@email.com

To use this UDF do the following (roughly based on this Microsoft page. They have some good additional info there!):

ALT+F11
  • Click on Tools -> References (please excuse the german screenshot)

Click on Insert Module. If you give your module a different name make sure the Module does not have the same name as the UDF below (e.g. naming the Module Regex and the function regex causes #NAME! errors).

  • In the big text window in the middle insert the following: Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object Dim replaceNumber As Integer With inputRegexObj .Global = True .MultiLine = True .IgnoreCase = False .Pattern = matchPattern End With With outputRegexObj .Global = True .MultiLine = True .IgnoreCase = False .Pattern = "\$(\d+)" End With With outReplaceRegexObj .Global = True .MultiLine = True .IgnoreCase = False End With Set inputMatches = inputRegexObj.Execute(strInput) If inputMatches.Count = 0 Then regex = False Else Set replaceMatches = outputRegexObj.Execute(outputPattern) For Each replaceMatch In replaceMatches replaceNumber = replaceMatch.SubMatches(0) outReplaceRegexObj.Pattern = "\$" & replaceNumber If replaceNumber = 0 Then outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value) Else If replaceNumber > inputMatches(0).SubMatches.Count Then 'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "." regex = CVErr(xlErrValue) Exit Function Else outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1)) End If End If Next regex = outputPattern End If End Function

This answer combined with the steps here to create an Add-In, has been very helpful. Thank you. Make sure you don't give your module and function the same name!

IMHO. Both answers are good: first is a reference on regexp , second gives the tool :).

Just reiterating the comment above from Chris Hunt. Don't call your Module 'Regex' as well. Thought I was going mad for a while as the function wouldn't work due to a #NAME error

Well, I'm gone nuts as I tried everything (including changing modules/names) and still getting the #NAME error >_> i.imgur.com/UUQ6eCi.png

vba - How to use Regular Expressions (Regex) in Microsoft Excel both i...

regex vba excel-vba
Rectangle 27 5

I'm no VBA coder so just a few minor things:

My rational for using ByRef for TransformIndex was that the alternative was to make it a function, but then it would look like index = TransformIndex(index), which seems redundant. Otherwise great points, especially Clear(start, count).

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

vba
Rectangle 27 6

never seen a single reference that even remotely suggests that one may be able to determine what error trapping conditions exists.

It seems to me that your problem may be in regards to how you are structuring your error trapping.

I wrap subroutines in a single On Error Goto statement, located in the same subroutine as the On Error. I have an exit and when the error occurs, I log what happened so I can fix the bug. I then can use debug.print or create a string with debugging information that is appended to the log.

Sub main()
         On Error GoTo Main_Error
         'Insert Code Here
         Exit Sub
         Main_Error:
         WriteLog Err.Number, Err.Description
        MsgBox ("The program encoutnered an error.")
    End Sub
    Private Sub WriteLog(ErrNum As Integer, ErrDes As String)
        Open "Errors.txt" For Append As #1
        Print #1, Now & "  " & ErrNum & " - " & ErrDes
        Close #1 
    End Sub

The second pattern I use is in areas in which there is a good chance that an error may occur, like opening a file (files might be missing), or a database. then I use the error trapping like a try catch block like so.

Private Function LoadFile() As Boolean
    On Error GoTo Main_Error
    'Code before a potentially error-prone statement
    On Error Resume Next
    Open "aMissingFile.txt" For Input As #1
    If Err.Number <> 0 Then
        MsgBox ("aMissingFile.txt is missing")
        LoadFile = False
        Exit Function
    End If
    On Error GoTo LoadFile_Error
    'The rest of your code
    LoadFile = True
    Exit Function
LoadFile_Error:
    WriteLog Err.Number, Err.Description
    MsgBox ("The program encoutnered an error.")
    LoadFile = False
End Function

This makes dealing with errors easier and debugging is as easy as commenting out a single On error statement. Note that err.number lists the error code return, so you can add logic to handle different types of errors.

Syntax to determine current error handling method in VBA - Stack Overf...

vba