Showing posts with label VBA. Show all posts
Showing posts with label VBA. Show all posts

2024-07-10

Changing a Picture in Microsoft Visio

Visio doesn't give you a built in way to change the picture that you've added to a diagram to another picture. But with some VBA code, you can perform the task.

First, you will need to add the code below. Press Alt+F11 on the keyboard to open the Visual Basic Editor. Click Insert > Module from the menu. Paste the code below into the module. Save your drawing as a Visio Macro-Enabled Drawing (with a .vsdm extension).

Then to change a picture to another picture, select it. Then press Alt+F8 to open the macros dialog. Select ChangePicture from the dialog and click Run. A dialog will open asking you for a file. Select the file and click OK. The picture will change without losing things such as connections to the picture.

Most of the code is just to open the dialog to ask for the filename. The main  task is to call the shape's ChangePicture method.


Option Explicit

Private Declare PtrSafe Function GetOpenFileName _
    Lib "comdlg32.dll" _
    Alias "GetOpenFileNameA" ( _
    pOpenfilename As OPENFILENAME) As Long

Private Declare PtrSafe Function CommDlgExtendedError _
    Lib "comdlg32.dll" () As Long

Private Type OPENFILENAME
    lStructSize         As Long
    hwndOwner           As LongPtr
    hInstance           As LongPtr
    lpstrFilter         As String
    lpstrCustomFilter   As String
    nMaxCustFilter      As Long
    nFilterIndex        As Long
    lpstrFile           As String
    nMaxFile            As Long
    lpstrFileTitle      As String
    nMaxFileTitle       As Long
    lpstrInitialDir     As String
    lpstrTitle          As String
    flags               As Long
    nFileOffset         As Integer
    nFileExtension      As Integer
    lpstrDefExt         As String
    lCustData           As LongPtr
    lpfnHook            As LongPtr
    lpTemplateName      As String
    '#if (_WIN32_WINNT >= 0x0500)
    pvReserved          As LongPtr
    dwReserved          As Long
    FlagsEx             As Long
    '#endif // (_WIN32_WINNT >= 0x0500)
End Type

Private Const OFN_READONLY = &H1
Private Const OFN_OVERWRITEPROMPT = &H2
Private Const OFN_HIDEREADONLY = &H4
Private Const OFN_NOCHANGEDIR = &H8
Private Const OFN_SHOWHELP = &H10
Private Const OFN_ENABLEHOOK = &H20
Private Const OFN_ENABLETEMPLATE = &H40
Private Const OFN_ENABLETEMPLATEHANDLE = &H80
Private Const OFN_NOVALIDATE = &H100
Private Const OFN_ALLOWMULTISELECT = &H200
Private Const OFN_EXTENSIONDIFFERENT = &H400
Private Const OFN_PATHMUSTEXIST = &H800
Private Const OFN_FILEMUSTEXIST = &H1000
Private Const OFN_CREATEPROMPT = &H2000
Private Const OFN_SHAREAWARE = &H4000
Private Const OFN_NOREADONLYRETURN = &H8000&
Private Const OFN_NOTESTFILECREATE = &H10000
Private Const OFN_NONETWORKBUTTON = &H20000
Private Const OFN_NOLONGNAMES = &H40000          '  force no long names for 4.x modules
Private Const OFN_EXPLORER = &H80000             '  new look commdlg
Private Const OFN_NODEREFERENCELINKS = &H100000
Private Const OFN_LONGNAMES = &H200000           '  force long names for 3.x modules

Private Const OFN_SHAREFALLTHROUGH = 2
Private Const OFN_SHARENOWARN = 1
Private Const OFN_SHAREWARN = 0

Private Const CDERR_DIALOGFAILURE = &HFFFF&

Private Const CDERR_GENERALCODES = &H0
Private Const CDERR_STRUCTSIZE = &H1
Private Const CDERR_INITIALIZATION = &H2
Private Const CDERR_NOTEMPLATE = &H3
Private Const CDERR_NOHINSTANCE = &H4
Private Const CDERR_LOADSTRFAILURE = &H5
Private Const CDERR_FINDRESFAILURE = &H6
Private Const CDERR_LOADRESFAILURE = &H7
Private Const CDERR_LOCKRESFAILURE = &H8
Private Const CDERR_MEMALLOCFAILURE = &H9
Private Const CDERR_MEMLOCKFAILURE = &HA
Private Const CDERR_NOHOOK = &HB
Private Const CDERR_REGISTERMSGFAIL = &HC

Private Function GetFileName() As String
    Dim lngResult As Long
    Const MAX_BUFFER As Long = 250

    Dim OFN As OPENFILENAME

    With OFN
        .lpstrFilter = "All Files (*.*)" & vbNullChar & "*.*" & vbNullChar
        .nFilterIndex = 1
        .lpstrFile = Space$(MAX_BUFFER - 1) & vbNullChar
        .nMaxFile = Len(.lpstrFile)
        .lpstrFileTitle = Space$(MAX_BUFFER - 1) & vbNullChar
        .nMaxFileTitle = Len(.lpstrFileTitle)
        .lpstrInitialDir = "C:\"
        .flags = OFN_FILEMUSTEXIST Or OFN_PATHMUSTEXIST
        .lStructSize = LenB(OFN)
    End With

    lngResult = GetOpenFileName(OFN)

    If lngResult <> 0 Then
        GetFileName = Left$(OFN.lpstrFile, InStr(1, OFN.lpstrFile, vbNullChar) - 1)
    Else
        GetFileName = vbNullString
    End If
End Function

Public Sub ChangePicture()
    Dim strFileName As String
    Dim shp As Shape
    
    ' Ensure a shape is selected
    If Application.ActiveWindow.Selection.Count = 0 Then
        MsgBox "Please select a shape first."
        Exit Sub
    End If
    
    strFileName = GetFileName()
    If Len(strFileName) > 0 Then
        Set shp = Application.ActiveWindow.Selection.PrimaryItem
        Call shp.ChangePicture(strFileName)
    End If
End Sub

2021-05-13

Displaying an Image in a Microsoft Access Image Control

If you have a column in a Microsoft Access table that has the name of an image that refers to a file on the disk, you can update an image control in either a form or report with the code below. Put this code into a standard VBA module that you create with Insert Module from the menu. This code assumes that all the images are in a subdirectory of the location of the database called "images" if there are relative paths to the image file.

Public Const strImageFolder = "images"

Public Sub DisplayImage(ctlImageControl As Control, strImagePath As Variant)
    On Error GoTo ErrorHandler
    
    If IsNull(strImagePath) Then
        ctlImageControl.Visible = False
    Else
        If InStr(1, strImagePath, "\") = 0 Then
            strImagePath = Application.CurrentProject.Path & "\" & strImageFolder & "\" & strImagePath
        End If
        ctlImageControl.Visible = True
        ctlImageControl.Picture = strImagePath
    End If
    Exit Sub
ErrorHandler:
    Select Case Err.Number
        Case 2114 'Doesn't support the format of the file
            ctlImageControl.Visible = False
        Case 2220 ' Can't find the picture.
            ctlImageControl.Visible = False
        Case Else  ' Some other error.
            MsgBox "Unexpected Error #" & Err.Number & " " & Err.Description, vbExclamation, "Unexpected Error"
    End Select
End Sub

To use this code in a form, if the control that contains the image name txtPicture and the image control is named imgPicture then add this code to the module for the form:

Private Sub Form_AfterUpdate()
    Call DisplayImage(Me!imgPicture, Me!txtPicture)
End Sub

Private Sub Form_Current()
    Call DisplayImage(Me!imgPicture, Me!txtPicture)
End Sub

Private Sub txtPicture_AfterUpdate()
    Call DisplayImage(Me!imgPicture, Me!txtPicture)
End Sub

To use it in a report, assuming there is a column in the table or query the report is based on called strPicture and an image control named imgPicture, add this code to the report:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    Call DisplayImage(Me!imgPicture, Me!strPicture)
End Sub

Open a URL using VBA

This is just a quick tip on opening a hyperlink using the default browser in VBA. It uses the ShellExecute Windows API call. Call the OpenHyperlink function shown below with the URL that you want to open.

Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
  ByVal hWnd As Long, _
  ByVal lpOperation As String, _
  ByVal lpFile As String, _
  ByVal lpParameters As String, _
  ByVal lpDirectory As String, _
  ByVal nShowCmd As Long _
  ) As Long

Public Function OpenHyperlink(ByRef strHyperlink As String) As Long
    OpenHyperlink = ShellExecute(0, "Open", strHyperlink, vbNullString, vbNullString, vbNormalFocus)
End Function

An example of calling it is:

Call OpenHyperlink("http://blog.xoc.net")

2021-05-09

Extracting Microsoft Access OLE Object Field Items

I created a Microsoft Access database for a small set of data (<1000 rows). Access was a perfect database for this particular problem, allowing easy input and good reporting, and the Access accdb database format allows easy installation on different computer that have Access. However, I made a mistake in storing bitmaps in an OLE object column. Access still has a limit of two gigabytes for its native database format. Bitmaps don't compress and quickly consume all of that limit. I had entered several hundred items before running into that limit. The two gigabyte limit was reasonable in the 1990s when a 1 gigabyte drive cost over $1000 (I have a receipt!), but is ridiculously small by today's standard.

The VBA code below works on the table tblExample. It extracts the bitmap from an OLE Object column (olePicture) and writes it to a file on the disk. It then updates another column (strPicture) with the name of the file it wrote. The filename is constructed by the name of the primary key field (ID) followed by .bmp, thus ID of 1 becomes 1.bmp in the same directory as the database.

An OLE Object field has a Package Header, an OLE header, the actual data of the bitmap, some optional other stuff, and an OLE footer. The problem is that the headers are variable length with sizes embedded into them, so the actual bitmap has to be located within the data before it can be extracted. So this code extracts the sizes and skips to the appropriate place and extracts the data. It uses a helper function that constructs a long from the first four bytes of an array of bytes (although it will break if a size is over 2^31 as it would try to convert an unsigned count to a signed count, which should never happen here).

After running this code successfully (use the Windows File Explorer to view the bitmaps), the OLE Object column can be deleted. Other VBA code will be necessary to display the picture in the external file, which is beyond the scope of what I want to show here. The code is not very fast as it writes the file one byte at a time, but it should be a one-time thing, at least for my purpose. It also probably has some boundary conditions related to some kinds of OLE objects that break it under some conditions, but it worked for what I needed.

Option Compare Database
Option Explicit

Public Sub ExtractImages()
    ' Need a reference to the Microsoft ActiveX Data Objects 6.1 Library
    Dim rst As ADODB.Recordset
    Dim varByte As Variant
    Dim i As Long
    Dim lngLength As Long
    Dim byteVal As Byte
    Dim strFileName As String
    Dim strFilePath As String
    
    Set rst = New ADODB.Recordset
    rst.Open "tblExample", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    Do While Not rst.EOF
        If Not IsNull(rst.Fields.Item("olePicture").Value) Then
            ' Create the filename from the primary key ID field.
            strFileName = rst.Fields.Item("ID").Value & ".bmp"
            
            ' Fill strPicture with the filename
            rst.Fields.Item("strPicture").Value = strFileName
            rst.Update
            
            strFilePath = Application.CurrentProject.Path & "\" & strFileName
            If Dir(strFilePath) = "" Then
                ' Read the package header the package header, the second byte is the size
                varByte = rst.Fields.Item("olePicture").GetChunk(3)
                
                'Extract the offset to the start of the OLE header
                varByte = rst.Fields.Item("olePicture").GetChunk(varByte(2) + 5)
                
                'Get the first four bytes which holds the OLE size
                varByte = rst.Fields.Item("olePicture").GetChunk(4)
                
                ' Use to size to of the header to move to the end of the header
                varByte = rst.Fields.Item("olePicture").GetChunk(GetLong(varByte))
                
                ' Skip the next eight bytes
                varByte = rst.Fields.Item("olePicture").GetChunk(8)
                
                ' The next four bytes retrieves the size of the Bitmap
                varByte = rst.Fields.Item("olePicture").GetChunk(4)
                
                ' Turn those bytes into a length
                lngLength = GetLong(varByte)
                
                ' Get the bitmap
                varByte = rst.Fields.Item("olePicture").GetChunk(lngLength)
                
                ' Write the bitmap to the file
                Open strFilePath For Binary As #1
                For i = 0 To lngLength - 1
                    byteVal = varByte(i)
                    Put #1, , byteVal
                Next i
                Close #1
            End If
        End If
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    MsgBox "Done"
End Sub

Public Function GetLong(ByRef varByte As Variant) As Long
    ' Convert the first four bytes of varByte into a long
    Dim i As Long
    Dim lngResult As Long
    
    For i = 3 To 0 Step -1
        lngResult = lngResult * 256 + varByte(i)
    Next i
    GetLong = lngResult
End Function

2020-06-17

Creating a Break Timer in PowerPoint using VBA

When I teach live classes, I use the SysInternals Zoomit application, which has a break timer built in. However, I was teaching a online class, and Zoomit did not seem to get along with WebEx. I decided to write a break timer directly into the PowerPoint slides I was using.

The first step is to create a slide at the end of the presentation that looks like this:


In other words, it is a standard slide with a title at the top and bullet points section below. I centered both and removed the bullet, so it just had text on the time. The code below counts on this slide as being the last in the presentation.

Next I brought up the PowerPoint Visual Basic Editor. You can do this with Alt+F11. Insert a module with Insert > Module from the menu. In the module, add this VBA code:

Option Explicit

Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
Private lngPreviousSlide As Long
Private boolEndBreak As Boolean

Public Sub BreakTimer()
    Dim dtmStart As Date
    Dim dtmEnd As Date
    Dim slidesCollection As slides
    Dim slideBreak As slide
    Dim lngCurrentSlide As Long
   
    dtmStart = Now
    dtmEnd = DateAdd("n", 10, dtmStart)
   
    Set slidesCollection = Application.ActivePresentation.slides
    Set slideBreak = slidesCollection(slidesCollection.Count)
   
    lngCurrentSlide = SlideShowWindows(1).View.slide.SlideIndex
    If lngCurrentSlide = slidesCollection.Count Then
        'On the break slide, end the break early
        boolEndBreak = True
    Else
        ' Go on break
        lngPreviousSlide = lngCurrentSlide
        boolEndBreak = False
        SlideShowWindows(1).View.GotoSlide slidesCollection.Count, msoTrue
        DoEvents
        Do Until (Now > dtmEnd) Or boolEndBreak
            slideBreak.Shapes(2).TextFrame.TextRange.Text = Format(dtmEnd - Now, "n:ss")
            Sleep 900
            DoEvents
        Loop
        SlideShowWindows(1).View.GotoSlide lngPreviousSlide, msoFalse
    End If
End Sub

When this code run, it remembers the current slide, changes the code to the last slide, and starts a 10 minute countdown (Change the 10 in the DateAdd function to another number to do a different number of minutes in your break).

I then went to the master slide (View > Slide Master) and added a small button in the lower right hand corner. To add a button, you need to have the Developer ribbon turned on. Use File > Options > Customize Ribbon and check the checkbox next to Developer in the dialog and press OK. Then switch to your Developer ribbon.

On the Developer ribbon, Click the Command Button icon, then draw the button on the master slide. Then click the Properties button on the ribbon. Set the name of the button to cmdBreak, and select a clock type image file in the Picture property by hitting the ... button on the right. Then double-click on the button you just created. This creates an Event Handler for the button. In the Event Handler, add this code:

Option Explicit

Private Sub cmdBreak_Click()
    Call BreakTimer
End Sub

Close the Master slide. Run your presentation. Whenever you want to call a break, click the button in the lower right of the current slide. It will jump to your break slide and start counting down. At the end of the break, it will jump back to the slide it was on. If you want to end the break early, on the break slide, click the break button and it will end it (the code is re-entrant, so it can be processing and the button is hit again, which executes it a second time while the first instance is still running).

2017-06-03

VBA Runtime Error Codes

I was teaching a VBA (Visual Basic for Applications) class for Excel today, and the question came up, "is there a list of all of the runtime error code numbers and what they mean?" You might be able to find one, but the easiest thing to do is to generate the list. Here is a small piece of VBA code that shows all of the runtime error code numbers and their descriptions.

Public Sub DisplayErrors()
    Dim i As Long
    
    For i = 1 To 65535
        If Error(i) <> "Application-defined or object-defined error" Then
            Debug.Print i & " " & Error(i)
        End If
    Next i
End Sub

When you run the code, it will print the list to the Immediate Window in the VBA Editor. Press Ctrl+G to make the Window visible.

It is possible to get other runtime errors, but only from some component that is called by VBA, not from VBA itself. When I run the code, this is the list that I get:

3 Return without GoSub
5 Invalid procedure call or argument
6 Overflow
7 Out of memory
9 Subscript out of range
10 This array is fixed or temporarily locked
11 Division by zero
13 Type mismatch
14 Out of string space
16 Expression too complex
17 Can't perform requested operation
18 User interrupt occurred
20 Resume without error
28 Out of stack space
35 Sub or Function not defined
47 Too many DLL application clients
48 Error in loading DLL
49 Bad DLL calling convention
51 Internal error
52 Bad file name or number
53 File not found
54 Bad file mode
55 File already open
57 Device I/O error
58 File already exists
59 Bad record length
61 Disk full
62 Input past end of file
63 Bad record number
67 Too many files
68 Device unavailable
70 Permission denied
71 Disk not ready
74 Can't rename with different drive
75 Path/File access error
76 Path not found
91 Object variable or With block variable not set
92 For loop not initialized
93 Invalid pattern string
94 Invalid use of Null
96 Unable to sink events of object because the object is already firing events to the maximum number of event receivers that it supports
97 Can not call friend function on object which is not an instance of defining class
98 A property or method call cannot include a reference to a private object, either as an argument or as a return value
321 Invalid file format
322 Can't create necessary temporary file
325 Invalid format in resource file
380 Invalid property value
381 Invalid property array index
382 Set not supported at runtime
383 Set not supported (read-only property)
385 Need property array index
387 Set not permitted
393 Get not supported at runtime
394 Get not supported (write-only property)
422 Property not found
423 Property or method not found
424 Object required
429 ActiveX component can't create object
430 Class does not support Automation or does not support expected interface
432 File name or class name not found during Automation operation
438 Object doesn't support this property or method
440 Automation error
442 Connection to type library or object library for remote process has been lost. Press OK for dialog to remove reference.
443 Automation object does not have a default value
445 Object doesn't support this action
446 Object doesn't support named arguments
447 Object doesn't support current locale setting
448 Named argument not found
449 Argument not optional
450 Wrong number of arguments or invalid property assignment
451 Property let procedure not defined and property get procedure did not return an object
452 Invalid ordinal
453 Specified DLL function not found
454 Code resource not found
455 Code resource lock error
457 This key is already associated with an element of this collection
458 Variable uses an Automation type not supported in Visual Basic
459 Object or class does not support the set of events
460 Invalid clipboard format
461 Method or data member not found
462 The remote server machine does not exist or is unavailable
463 Class not registered on local machine
481 Invalid picture
482 Printer error
735 Can't save file to TEMP
744 Search text not found
746 Replacements too long

2015-10-26

Writing Landscape Text into Microsoft Word Document

For a book I am writing using Microsoft Word, I needed to include some source code listings. These listings are wider than will fit  in a portrait orientation, so I needed them to be landscape. I could create sections that are landscape, but the publisher I am using can't deal with that, so I needed them to be landscape text on a portrait page. I also needed to be able to re-add the listings if the source code changed. This sounds like a job for VBA. Below is the code that I wrote.

I add textboxes to each page, and then fill the textbox with the vertical text.

Option Explicit
' Copyright © 2015 Xoc Software
' Put source listings landscape on a page
' Applies two styles RotatedFileName and RotatedCode

Public Sub AddSourceListings()
    Dim shape As shape
    Dim files As Collection
    Dim strsFileNames As Collection
    Dim strFileName As String
    Dim varFileName As Variant
    Dim i As Long
    Dim lngLineNumber As Long
    Dim varLine As Variant
    
    'Maximum lines per page...adjust to page width
    Const lngMaxLines As Long = 35
    
    Set files = New Collection
    Set strsFileNames = New Collection
    
    'FileNames to document
    strsFileNames.Add "c:\src.cs\Xoc.CoverGenerator\Xoc.CoverGenerator\Properties\AssemblyInfo.cs"
    strsFileNames.Add "c:\src.cs\Xoc.CoverGenerator\Xoc.CoverGenerator\GraphicsExtensions.cs"
    strsFileNames.Add "c:\src.cs\Xoc.CoverGenerator\Xoc.CoverGenerator\PageType.cs"
    strsFileNames.Add "c:\src.cs\Xoc.CoverGenerator\Xoc.CoverGenerator\Program.cs"
    strsFileNames.Add "c:\src.cs\Xoc.CoverGenerator\Xoc.Penrose\Properties\AssemblyInfo.cs"
    strsFileNames.Add "c:\src.cs\Xoc.CoverGenerator\Xoc.Penrose\RhombusTiler.cs"
    strsFileNames.Add "c:\src.cs\Xoc.CoverGenerator\Xoc.Penrose\RhombusType.cs"
    strsFileNames.Add "c:\src.cs\Xoc.CoverGenerator\Xoc.Penrose\Triangle.cs"
    
    For Each varFileName In strsFileNames
        files.Add ProcessFile(varFileName)
    Next varFileName
    
    lngLineNumber = 0
    Set shape = AddPage
    For i = 1 To files.Count
        If lngLineNumber >= lngMaxLines Then
            Set shape = AddPage
            lngLineNumber = 0
        End If
        With shape.TextFrame
            strFileName = strsFileNames(i)
            
            'Remove up through the first slash each time
            strFileName = Mid$(strFileName, InStr(1, strFileName, "\") + 1)
            strFileName = Mid$(strFileName, InStr(1, strFileName, "\") + 1)
            strFileName = Mid$(strFileName, InStr(1, strFileName, "\") + 1)
            .TextRange.InsertAfter strFileName
            .TextRange.Select
            Selection.Collapse wdCollapseEnd
            Selection.Style = "RotatedFileName"
            .TextRange.InsertParagraphAfter
            .TextRange.Select
            Selection.Collapse wdCollapseEnd
            Selection.Style = "RotatedCode"
        End With
        lngLineNumber = lngLineNumber + 2
       
        For Each varLine In files(i)
            shape.TextFrame.TextRange.InsertAfter varLine & Chr(11)
            lngLineNumber = lngLineNumber + 1
            If lngLineNumber >= lngMaxLines Then
                shape.TextFrame.TextRange.InsertParagraphAfter
                Set shape = AddPage
                shape.TextFrame.TextRange.Select
                Selection.Collapse wdCollapseEnd
                Selection.Style = "RotatedCode"
                lngLineNumber = 0
            End If
        Next varLine
        shape.TextFrame.TextRange.InsertParagraphAfter
        lngLineNumber = lngLineNumber + 1
    Next i
    
    Set strsFileNames = Nothing
    Set files = Nothing
End Sub

Private Function AddPage() As shape
    Dim doc As Document
    Dim section As section
    
    Set doc = Application.ActiveDocument
    Set section = doc.Sections.Add
    section.Range.Select
    Selection.Collapse wdCollapseEnd
    
    'Size the textbox on the page, adjust to page size and margins
    Set AddPage = doc.Shapes.AddTextbox(Orientation:=msoTextOrientationUpward, Left:=54, Top:=54, Width:=324, Height:=540)
End Function

Private Function ProcessFile(ByVal strFileName As String) As Collection
    Dim strLine As String
    Dim boolFirst As Boolean
    Dim lines As Collection
    
    Set lines = New Collection
    
    boolFirst = True
    
    Open strFileName For Input As #1
    
    Do Until EOF(1)
        Line Input #1, strLine
        strLine = Replace(strLine, Chr(9), "     ")
        
        'Remove some miscellanous stuff Visual Studio adds to files
        strLine = Replace(strLine, "", "")
        strLine = Replace(strLine, "Â", "")
        lines.Add strLine
    Loop
    Close #1
    Set ProcessFile = lines
    Set lines = Nothing
End Function

2014-05-25

Forward All Headers from Outlook from a Button


There are times that you want to forward all headers an from Outlook email message. Usually this would be to report Spam. In the headers are the information necessary to block the offending email message. Since things like a return address can be (and frequently are) spoofed, the headers are the only definitive way of tracking these offenders.

You can get the headers by opening the message, then selecting File/Info/Properties from the menu. However, if the message is spam, I usually prefer not opening the message as there have been vulnerabilities in Outlook that allow malicious code to run. It's paranoia, but just because I'm paranoid doesn't mean they aren't out to get me. It is also tedious to copy the headers from the dialog and paste them into a message.

I have created a little code that allows creating an email message with just the headers from a press of a button. With a little more work, I could add the body of the message. It is just a little bit tricky, because an email message can contain really two bodies: a plain text body, and a HTML mail body. You could also add the attachments, if any. This code doesn't do either of those...it just creates an email message with the headers.

It opens the message in the editor so you can add a recipient and subject line. These could be automated if you knew the recipient, and then the message could be automatically saved and sent, which would place it in the outbox.

The code appears below. To add this code to Outlook, you need to bring up the VBA editor. The easiest way to do that is to press Alt+F11 on the keyboard. Then Insert Module, add the code, and save it.

Then assign the code to a Quick Access Toolbar button. Click on the little button at the end of the Toolbar called Customize Quick Access Toolbar. Select "More Commands" from the menu. Select "Macros" from the "Choose commands from" drop-down list. Then pick "ForwardHeaders" from the list, and click the Add button. You can modify the icon, if you like, by pressing the "Modify..." button.

Select one or more email messages. Click the button.

Here is the code:

Option Explicit

Public Sub ForwardHeaders()
    'From blog.xoc.net, written by Greg Reddick
    Dim selection As Outlook.selection
    Dim mail As Outlook.MailItem
    Dim i As Long
    Const PR_TRANSPORT_MESSAGE_HEADERS = "http://schemas.microsoft.com/mapi/proptag/0x007D001E"
    Dim headers As String
    Dim sendMessage As Outlook.MailItem
    
    Set selection = Application.ActiveExplorer.selection
    For i = 1 To selection.Count
        If selection.Item(i).Class = OlObjectClass.olMail Then
            Set mail = selection.Item(i)
            headers = mail.PropertyAccessor.GetProperty(PR_TRANSPORT_MESSAGE_HEADERS)
            Set sendMessage = Application.CreateItem(olMailItem)
            With sendMessage
                .Body = headers
                .display
            End With
            Set sendMessage = Nothing
            Set mail = Nothing
        End If
    Next i
    Set selection = Nothing
End Sub

2013-04-19

Common VBA Mistakes

I recently taught a VBA (Visual Basic for Applications, also known as Microsoft Office Macros) course using courseware that is frequently used. Although the person who wrote the courseware generally did a fine writing job, he made a few fundamental mistakes in the sample code. None of the mistakes actually stops the code from running, but they show a misunderstanding of how VBA works. Here are the mistakes, the reason why they are wrong, and what can be done to fix them.

Declaring Multiple Variables on a Line


Here is some code similar to what was in the book:

Dim var1, var2, var3 As Integer

In this example, it is obvious that the author intended to declare three integers, but that is not what happened. In VBA, the default data type is a variant. Variables declared with the variant data type can hold any kind of data, including integers. In VBA, although you can declare multiple variables on a line, you must give a data type to each variable. This is the code as it should have been written:

Dim var1 As Integer, var2 As Integer, var3 As Integer

Instead, the code as written results in two Variants and an integer. It is the equivalent of writing this:

Dim var1 As Variant, var2 As Variant, var3 As Integer

It still works because variables of the variant data type can still hold integers, but there is a larger overhead (an extra 16 bytes of memory), and the code runs slower because internally it must resolve any variant into an integer before performing math operations on it.

In my VBA coding conventions, declaring multiple variables on a line is prohibited to avoid just this error. This is the preferred way to write this code:

Dim var1 As Integer
Dim var2 As Integer
Dim var3 As Integer

Wrapping Arguments With Parentheses


Here is some code similar to what is in the book:

MsgBox ("Hello World")

VBA has two ways to call this function that are equally valid:

MsgBox "Hello World"
Call MsgBox("Hello World")

If you use the word "Call", you must use parentheses, otherwise they are omitted. The reason why the example works is that you can surround any expression with parentheses. For example:

Debug.Print ((((7) + (((4))) - ((9)))

The extra parentheses are discarded by VBA. The mistake becomes apparent when you add a second argument. If you try to add Yes and No buttons to the message box like this:

MsgBox ("Delete the Database?", vbYesNo)

This results in a syntax error, because it would be similar to writing this:

Debug.Print (7, 4)

The expressions (7, 4) has no meaning in VBA. To fix the error you would need to write either of these:

MsgBox "Delete the Database?", vbYesNo
Call MsgBox ("Delete the Database?", vbYesNo)

Extraneous Colons


Here is some code similar to what is in the book:

Select Case var1
    Case 1:
        MsgBox "1"
    Case 2:
        MsgBox "2"
End Select

The colons after the 1 and 2 are extraneous and are not needed. The reason why it works is because VBA allows multiple statements on a line separated by colons. This is an example of that:

Dim var1 As Integer : var1 = 1

This is treated exactly the same as doing this:

Dim var1 As Integer
var1 = 1

The colon separates the multiple statements on a line. However, you could put a colon at the end of any line because the second statement could be blank. VBA will accept this code as well:

Dim var1 As Integer:
var1 = 1:

In this case there is a second statement on each line that happens to be blank.

2011-06-29

The Four VBA Options You Should Always Change

I have been teaching and using Visual Basic and VBA for almost 20 years. Although the classic Visual Basic is dead as a programming language, VBA is still being used for macros in Microsoft Office. There are four settings in the options that you should always change:

  1. Auto Syntax Check. Uncheck this option. If this option is checked, it brings up a syntax error message box every time that you leave a line with a syntax error. This gets in the way of programming, as you frequently want to move to other lines of code before completing the current line. When this option is off, the line will turn red if there is a syntax error. If you want to know why it is red, you can press F5 and then the message box will appear.
  2. Require Variable Declarations. Check this option. This option is actually misnamed. It should be named "Insert Option Explicit at the top of every new module you create". It has no impact on existing modules and it is the Option Explicit that actually makes variable declarations required.
  3. Notify Before State Loss. Check this option. This will give you a message box before committing any changes to a line that would require you to exit the debugger while debugging. This saves effort if you are doing a long debugging session, and make some minor change that would cause you to exit the debugger.
  4. Compile On Demand. Uncheck this option. This will compile all of your code, rather than just the next procedure that it needs to execute. This will find syntax errors in code that you are not executing. Machines are 1000 times faster than when this feature was put in place.
This creates the best environment for programming in VBA.