LoadPicture not showing as function in Excel 2013 VBA

[twilsonco]

I'm making a userform to run on both Mac and PC. The form does stuff with pictures, and since certain functions don't exist in the Mac VBA, I identify which platform is running and the proper code is used.

However, when trying to use LoadPicture() to put an image in the userform image control in Windows Excel 2013, I get the same compiler error as when I tried to use it in Mac Excel 2011, "Compiler Error: Sub or Function not defined"

This is strange, as I'm using the function based on syntax at the microsoft website. It's a known issue that LoadPicture doesn't work in Mac, but why should this be happening with the windows version? Did they remove LoadPicture in Office 2013 or replace it with something else?

I know I can to get around this with the PastePicture method by Stephen Bullen, but I'd like to avoid having to install custom stuff.

Here's the line of code I'm using, where Picture1 is a string containing the full path and filename of the picture to be loaded, and pictureWindow is the image control in the userform:

pictureWindow.Picture = LoadPicture(Picture1)

After the compiler error is thrown, "LoadPicture" is highlighted.

Any help is appreciated.

Thanks,

Tim
******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;">******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;"></object>

 

[rohithrrao]

That does look pretty cool, but I'm just curious why my Windows machine doesn't think that the LoadPicture function exists... I've already finished a workaround for those running Mac.******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;">

Hi, could you please explain in detail the "workaround for Mac" that you have used.
Thanks

 

[twilsonco]

Hi, could you please explain in detail the "workaround for Mac" that you have used.
Thanks

Sure, and Happy Saturnalia!

So the goal is to be able to have a picture in the spreadsheet, and to view that picture using a user form. But Excel for Mac doesn't yet have the ability to do so, because the picture window object in user forms is useless (on Mac and Windows), and the LoadPicture custom function doesn't work on Mac.

The solution is in two parts. The first part is identical to the solution in Excel for Windows: copy and paste the desired picture in to a blank chart and export that chart to secondary storage (hard drive, etc...). Next, call an Applescript to open the exported image using Preview. It's not pretty, but it works.

Here's the function for exporting a picture to the hard drive (Note that this function takes the name of the picture to be exported and the name of the worksheet in which the picture resides. The exported picture is saved to the same directory as the workbook with the name "TempPicture.jpg". I DID NOT WRITE THIS FUNCTION, BUT I CAN'T REMEMBER WHO DID):

Public Sub PictureExport(Picture2Export As String, ChartWorksheet As String)
Dim TempChart As String ', Picture2Export As String
Dim PicWidth As Long, PicHeight As Long
Dim maxWidth As Integer, maxHeight As Integer
Dim aspectRatio As Single


maxHeight = 372
maxWidth = 432


ActiveWorkbook.Worksheets(ChartWorksheet).Shapes(Picture2Export).Select
Picture2Export = Selection.Name


'Store the picture's height and width in a variable
With Selection
PicHeight = .ShapeRange.Height
PicWidth = .ShapeRange.Width
End With


'Add a temporary chart in sheet1
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:=ChartWorksheet
Selection.Border.LineStyle = 0
For Each tmpShape In ActiveWorkbook.ActiveSheet.Shapes
If Split(tmpShape.Name, " ")(0) = "Chart" Then
TempChart = tmpShape.Name
Exit For
End If
Next tmpShape


With Worksheets(ChartWorksheet)
'Change the dimensions of the chart to suit your need
With .Shapes(TempChart)
.Width = PicWidth
.Height = PicHeight
End With


'Copy the picture
.Shapes(Picture2Export).Copy


'Paste the picture in the chart
With ActiveChart
.ChartArea.Select
.Paste
End With


'Finally export the chart
If winPC Then
aspectRatio = PicWidth / PicHeight
With .Shapes(TempChart)
If .Width > maxWidth Then
.Width = maxWidth
.Height = maxWidth / aspectRatio
End If
If .Height > maxHeight Then
.Height = maxHeight
.Width = maxHeight * aspectRatio
End If
End With
' Here the file will be saved in the same directory as the workbook, with the name "TempPicture.jpg"
.ChartObjects(1).Chart.Export filename:=Application.ActiveWorkbook.Path & "\TempPicture.jpg", FilterName:="jpg"
Else
.ChartObjects(1).Chart.Export filename:=Application.ActiveWorkbook.Path & ":TempPicture.jpg", FilterName:="jpg"
End If
'Destroy the chart. You may want to delete it...
.Shapes(TempChart).Delete
End With

End Sub

Next, with the filename and full path known, we can run a simple Applescript call using the MacScript() function (this part, I did write, which explains why it's ugly). The Applescript opens the file in preview and then trashes it. Volume is muted for this so that the user doesn't need to hear the trash noises. Here is a quick example of the above function being called and the exported picture being opened with preview:

Call PictureExport("PictureName", "SheetName")
filePath = Application.ActiveWorkbook.Path & ":TempPicture.jpg"
' blankInt is of Integer type, since MacScript() returns an integer (not sure if you need to capture it, but I did)
blankInt = MacScript("set currentVol to alert volume of (get volume settings)" & Chr(13) & _
"set volume alert volume 0" & Chr(13) & _
"set theFile to """ & filePath & """ as alias" & Chr(13) & _
"tell application ""Preview""" & Chr(13) & "Activate" & Chr(13) & _
"open theFile" & Chr(13) & "Activate" & Chr(13) & "end tell" & Chr(13) & _
"tell application ""Finder"" to move theFile to trash" & Chr(13) & _
"set volume alert volume currentVol" & Chr(13) & _
"return 0")

Here's what the Applescript would look like inside Applescript Editor, which is also useful for testing it. Say filePath is equal to "/path/to/file.jpg":

set currentVol to alert volume of (get volume settings)
set volume alert volume 0
set theFile to "/path/to/file.jpg" as alias
tell application "Preview"
activate
open theFile
activate
end tell
tell application "Finder" to move theFile to trash
set volume alert volume currentVol
return 0

Good luck!

 

[twilsonco]

Sorry. Should be "Path:to:file.jpg". Since it's an HFS path.

 

[rohithrrao]

Thanks a lot twilsonco for the prompt response. Im currently using the Visual Basic Editor in Excel 2011 for Mac. All my code is written and run on the same editor, so I dont know how to use AppleScript. This is the code I'm using currently, it works fine on Windows but gives an error on Mac.


Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long


Private Const MAX_PATH As Long = 260


Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim wsTemp As Worksheet
Dim rng As Range
Dim oChrt As ChartObject


'~~> Set the sheet where you have the charts data
Set ws = [Sheet1]


'~~> This is your charts range
Set rng = ws.Range("A1:B8")


'~~> Delete the temp sheeet if it is there
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Sheets("TempOutput").Delete
On Error GoTo 0
Application.DisplayAlerts = True


'~~> Add a new temp sheet
Set wsTemp = ThisWorkbook.Sheets.Add


With wsTemp
'~~> Give it a name so that we can delete it as shown above
'~~> This is just a precaution in case `wsTemp.Delete` fails below
.Name = "TempOutput"


'~~~> Add the chart
Set oChrt = .ChartObjects.Add _
(Left:=50, Width:=300, Top:=75, Height:=225)


'~~> Set the chart's source data and type
'~~> Change as applicable
With oChrt.Chart
.SetSourceData Source:=rng
.ChartType = xlXYScatterLines
End With
End With


'~~> Export the chart as bmp to the temp drive
oChrt.Chart.Export Filename:=TempPath & "TempChart.bmp", Filtername:="Bmp"


'~~> Load the image to the image control

Me.Image1.Picture = LoadPicture(TempPath & "TempChart.bmp")


'~~> Delete the temp sheet
Application.DisplayAlerts = False
wsTemp.Delete
Application.DisplayAlerts = True


'~~> Kill the temp file
On Error Resume Next
Kill TempPath & "TempChart.bmp"
On Error GoTo 0
End Sub


'~~> Function to get the user's temp path
Function TempPath() As String
TempPath = String$(MAX_PATH, Chr$(0))
GetTempPath MAX_PATH, TempPath
TempPath = Replace(TempPath, Chr$(0), "")
End Function

 

[twilsonco]

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

I'm pretty sure this line is the problem. kernel32 doesn't exist, so you can't make kernel calls.

Use a preprocessor if to set a boolean to tell you if you're on Mac or PC, then use that boolean to ensure that no illegal things are done on Mac.

Public winPC As Boolean

#If Mac Then
  winPC = False
#Else
  winPC = True
#End If

Should do the trick.

 

[Jim Gordon]

I’m a bit surprised that Ron didn’t mention this web page of his:
[h=2]Load picture in image control on userform[/h]