Excel VBA 用户表单 - 设置与用户表单图像大小相同的图表

Excel VBA Userform - Setting a chart the same size as userform image

提问by user1130306

I have an image in a userform which displays a pre-existing chart, according the properties menu the height = 246 and width = 462 (it does not specify units). I want my chart to be sized so that when a .GIF of the chart is saved and loaded to the userform image is matches the dimensions. In the chart properties I can change the dimensions but only has the option for inches. How can I get these dimensions to match?

我在用户窗体中有一个图像，它显示一个预先存在的图表，根据属性菜单，高度 = 246 和宽度 = 462（它没有指定单位）。我希望我的图表调整大小，以便在保存图表的 .GIF 并将其加载到用户表单图像时与尺寸匹配。在图表属性中，我可以更改尺寸，但只能选择英寸。我怎样才能让这些尺寸匹配？

采纳答案by Siddharth Rout

If you are using an Image control to display the chart, then size the image control to fit the userform as per your requirements and then set the "PictureSizeMode" property of the image control to "fmPictureSizeModeStretch".

如果您使用图像控件来显示图表，则根据您的要求调整图像控件的大小以适合用户窗体，然后将图像控件的“PictureSizeMode”属性设置为“fmPictureSizeModeStretch”。

回答by Roy

The conversion factor you're looking for is 72. Resize your chart to (Image ctl height / 72) inches by (Image ctl width / 72) inches. In your case that would give you 3.42 x 6.42 inches. When you load this into the Image ctl in Clip Mode it will fit perfectly. If you've selected to Center the picture in the Image with PictureAlignment, then switching back and forth between Clip and Stretch will barely produce a wiggle.

您正在寻找的转换因子是 72。将图表大小调整为 (Image ctl height / 72) 英寸乘 (Image ctl width / 72) 英寸。在您的情况下，这将为您提供 3.42 x 6.42 英寸。当您在剪辑模式下将其加载到 Image ctl 中时，它将完美契合。如果您选择使用 PictureAlignment 将图片居中，则在 Clip 和 Stretch 之间来回切换几乎不会产生摆动。

Here are 3 ways you can get the chart into the Image:

以下是将图表放入图像的 3 种方法：

1) You can CopyPicture the resized Chart on the sheet to get it on the Clipboard, and then in the Form Editor at Design time select your Image ctl, then select the Picture Property for your Image ctl in the Properties Window, and Paste.

1）您可以在工作表上复制调整大小的图表以将其复制到剪贴板上，然后在设计时在表单编辑器中选择您的图像ctl，然后在属性窗口中为您的图像ctl选择图片属性，然后粘贴。

2) You can save the resized chart as a jpg, (it will also work to save as bmp but the file sizes get huge) and load the chart into the Image ctl from a file using:

2) 您可以将调整大小的图表保存为 jpg，（也可以保存为 bmp，但文件大小会变大）并使用以下命令将图表从文件加载到 Image ctl 中：

myImageCtl.picture = loadPicture("C:\whatever")

3) If you want to keep a Picture of the resized chart on the sheet and insert it into your Image ctl from there instead of saving it to a file first, you can CopyPicture the Chart and paste it back onto the sheet. If you want the Picture to be a live updating copy of the original Chart (which could be on some other sheet), then use PastePicture to put it back on the sheet. Select the newly pasted Picture, type in a Name for it (myResizedChart) in the Name Box (upper left corner) and hit Return.

3) 如果您想在工作表上保留调整大小的图表的图片并将其从那里插入到您的 Image ctl 中，而不是先将其保存到文件中，您可以复制图表并将其粘贴回工作表。如果您希望图片成为原始图表的实时更新副本（可能位于其他工作表上），请使用 PastePicture 将其放回工作表上。选择新粘贴的图片，在名称框（左上角）中为它输入一个名称（myResizedChart），然后点击 Return。

Download modPastePicture from here http://www.oaltd.co.ukand install it, (and set a reference to OLE Automation).

从这里http://www.oaltd.co.uk下载 modPastePicture并安装它，（并设置对 OLE 自动化的引用）。

Then in your code you can say: (assuming your original CopyPicture was "Format Picture")

然后在您的代码中，您可以说：（假设您的原始 CopyPicture 是“格式化图片”）

shapes("myResizedChart").CopyPicture
myImage.picture = pastePicture

Finally, unless you need the Transparent Property of an Image ctl, there are a lot of advantages to using Frames instead of Images to show your Pictures on a Form.

最后，除非您需要图像 ctl 的透明属性，否则使用框架而不是图像在表单上显示您的图片有很多优点。

回答by Jon Peltier

Do it all at runtime, using the approach that saves the chart as a GIF and loads the gif into the image control.

在运行时完成所有操作，使用将图表另存为 GIF 并将 gif 加载到图像控件中的方法。

The userform is named F_DisplayChart. It contains an Image control named imgChart and a button named btnClose. Here is the code in the UserForm's code module:

用户窗体名为 F_DisplayChart。它包含一个名为 imgChart 的 Image 控件和一个名为 btnClose 的按钮。这是用户窗体的代码模块中的代码：

Private Sub btnClose_Click()
  Unload Me
End Sub

Public Property Set Chart(cht As Chart)
  ' pass chart from calling code to userform
  Dim dHeight As Double, dWidth As Double
  Dim sPath As String

  dHeight = cht.Parent.Height
  dWidth = cht.Parent.Width

  cht.Parent.Height = Me.imgChart.Height
  cht.Parent.Width = Me.imgChart.Width

  sPath = ThisWorkbook.Path & "\temp1.gif"
  cht.Export Filename:=sPath, FilterName:="gif"

  cht.Export sPath

  Me.imgChart.Picture = LoadPicture(sPath)

  cht.Parent.Height = dHeight
  cht.Parent.Width = dWidth

End Property

In the property code, the chart is sized to fit the image control, exported, loaded into the image control, then the chart's size is reset to the original size.

在属性代码中，将图表大小调整为适合图像控件，导出，加载到图像控件中，然后将图表的大小重置为原始大小。

Here is the code that calls the UserForm, passes the active chart into the UserForm, then shows the form:

这是调用用户窗体，将活动图表传递到用户窗体，然后显示窗体的代码：

Sub ShowFormWithChart()
  Dim chrt As Chart

  If ActiveChart Is Nothing Then Exit Sub

  Set chrt = ActiveChart

  With F_DisplayChart
    Set .Chart = chrt
    .Show
  End With

End Sub

You could also use bmp in place of gif, and get a larger file size. jpg also works, but it usually does not render the chart as nicely.

您也可以使用 bmp 代替 gif，并获得更大的文件大小。jpg 也可以使用，但它通常不会很好地呈现图表。