是否有使用 LoadPicture("bmp_or_icon_file_path") 在 Excel 2007 VBA 中加载图像的替代方法

Is there an Alternative to using the LoadPicture("bmp_or_icon_file_path") to loading Images in Excel 2007 VBA

提问by Shiva

I have an Excel 2007 Worksheet with many buttons and labels that act as menu options (i.e. user clicks the buttons, labels with images) and is presented with forms, or some thing else.

我有一个 Excel 2007 工作表，其中包含许多充当菜单选项（即用户单击按钮、带有图像的标签）的按钮和标签，并以表单或其他形式呈现。

These images / icons for the buttons and labels are loaded in VBA by assigning the Pictureproperty of the Control and calling LoadPicture() method with the full image file path as parameter, like So.

这些按钮和标签的图像/图标通过分配控件的图片属性并以完整的图像文件路径作为参数调用LoadPicture() 方法在 VBA 中加载，如 So。

   With SomeFormObject
        .cmdOpenFile.Picture = LoadPicture("F:\projectname\images\fileopen.BMP")
   End With

This method of loading images for buttons, other controls is causing 2 issues.

这种为按钮加载图像的方法，其他控件会导致 2 个问题。

1) It creates a dependency on the image files and physical location for every user, so if a user does not have the drive mapped and files present, the VBA fails with runtime error of file or path not found.
2) The app gets very slow if the images are on a shared drive (which is the case)

1) 它为每个用户创建了对映像文件和物理位置的依赖，因此如果用户没有映射的驱动器和文件，VBA 将失败并显示文件或路径未找到的运行时错误。
2）如果图像在共享驱动器上，应用程序会变得非常慢（就是这种情况）

I want to eliminate both issues and somehow load icons, images into control internally, without any external dependencies on external image files.

我想消除这两个问题，并以某种方式将图标、图像加载到内部控制中，而不需要对外部图像文件有任何外部依赖。

What is the best way to achieve this in Excel 2007 VBA?

在 Excel 2007 VBA 中实现这一目标的最佳方法是什么？

I could not file any Visual Basic 6.0 / Visual Studio style "Resource File Editor" / feature with which to accomplish this.

我无法提交任何用于完成此操作的 Visual Basic 6.0/Visual Studio 样式“资源文件编辑器”/功能。

Please advice! thank you

请指教！谢谢你

-Shiva @ mycodetrip.com

-Shiva @ mycodetrip.com

回答by marg

I really hope that there is a easier way to do this, but this is the only one I found:

我真的希望有一种更简单的方法来做到这一点，但这是我发现的唯一方法：

The Idea is: You keep the Pictures embedded in a Sheet and every time you want to set the pictures for the Command you export them from your worksheet to a file and load them through LoadPicture. The only way to export an embedded Picture through VBA that I found is by making it a Chartfirst.

想法是：您将图片嵌入工作表中，每次要为命令设置图片时，您都可以将它们从工作表导出到文件并通过LoadPicture. 我发现通过 VBA 导出嵌入图片的唯一方法是将其设为Chart第一个。

The following code is based on 'Export pictures from Excel'from johnske

以下代码基于johnske 的“从Excel 导出图片”

Option Explicit

Sub setAllPictures()
    setPicture "Picture 18", "CommandButtonOpen"
    setPicture "Picture 3", "CommandButtonClose"
End Sub

Sub setPicture(pictureName As String, commandName As String)
    Dim pictureSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim embeddedPicture As Picture
    Dim pictureChart As Chart

    Dim MyPicture As String
    Dim PicWidth As Long
    Dim PicHeight As Long

    Set pictureSheet = Sheets("NameOfYourPictureSheet") ' <- to Change '
    Set targetSheet = Sheets("NameOfYourSheet")  ' <- to Change '

    Set embeddedPicture = pictureSheet.Shapes(pictureName).OLEFormat.Object
    With embeddedPicture
        MyPicture = .Name
        PicHeight = .ShapeRange.Height
        PicWidth = .ShapeRange.Width
    End With

    Charts.Add
    ActiveChart.Location Where:=xlLocationAsObject, Name:=pictureSheet.Name
    Set pictureChart = ActiveChart

    embeddedPicture.Border.LineStyle = 0

    With pictureChart.Parent
          .Width = PicWidth
          .Height = PicHeight
    End With

    With pictureSheet
        .Select
        .Shapes(MyPicture).Copy

        With pictureChart
            .ChartArea.Select
            .Paste
        End With

        .ChartObjects(1).Chart.Export Filename:="temp.jpg", FilterName:="jpg"
    End With

    pictureChart.Parent.Delete
    Application.ScreenUpdating = True

    targetSheet.Shapes(commandName).OLEFormat.Object.Object.Picture = LoadPicture("temp.jpg")

    Set pictureChart = Nothing
    Set embeddedPicture = Nothing
    Set targetSheet = Nothing
    Set pictureSheet = Nothing
End Sub

Sub listPictures()
    ' Helper Function to get the Names of the Picture-Shapes '
    Dim pictureSheet As Worksheet
    Dim sheetShape As Shape

    Set pictureSheet = Sheets("NameOfYourSheet")
    For Each sheetShape In pictureSheet.Shapes
        If Left(sheetShape.Name, 7) = "Picture" Then Debug.Print sheetShape.Name
    Next sheetShape

    Set sheetShape = Nothing
    Set pictureSheet = Nothing
End Sub

To Conclude:Loading the Images from a Mapped Networked Drive seems less messy, and there shouldn't be that much of a speed difference.

结论：从映射的网络驱动器加载图像似乎不那么混乱，并且速度差异不应该太大。

回答by Patrick Lepelletier

2 alternatives i can think of: form.img.picture=pastepicture, and = oleobjects("ActiveXPictureName").object.picture.

我能想到的 2 个替代方案：form.img.picture=pastepicture, 和= oleobjects("ActiveXPictureName").object.picture.