Insert a picture using VBA, different size between Excel 2010 and Excel 2016

Question

I'm writing because I'm struggling in trying to understand why I get 2 different results when importing a picture using VBA.

The image source is: https://maps.googleapis.com/maps/api/staticmap?size=390x280&zoom=8&maptype=terrain&markers=color:red%7Clabel:A%7C18.510516,-72.287768&key=mykey

And I'm using this VBA code to insert the picture in cell Z8:

Range("Z8").Select
ActiveSheet.Pictures.Insert(URL).Select
With Selection
.ShapeRange.ScaleWidth 0.999, msoTrue
End With

*the .ShapeRange.ScaleWidth 0.999 is not need in this specific case but even if I remove the With Selection instruction, I get the same output.

If I run the VBA code in Excel 2010, which I used to build the worksheet, everything is looking great, as shown below:

but when I run the VBA code in Excel 2016, I get something different:

The picture in this case is larger and off the page limits. The same is happening also for the QR code you can see on the top left. Also the vertical (and horizontal) page breaks have moved (and I cannot explain myself why) but I'll keep this question for another message.

In general the picture in Excel 2016 is 1.25 larger than the picture in Excel 2010. Excel 2016 is installed on another laptop with a different screen resolution. Could be this the reason for the issue I'm facing? If so, do you know a way to fix it in a way that is screen-resolution independent?

Thanks, Stefano

Answer 1

Microsoft introduced new picture resolution and compression handling processes beginning with Excel 2010, and then tweaking the defaults as technology quickly improved over the next few years. This could be responsible for your issue.

Your varying monitor sizes/resolution between your machines will also affect the default (and available) settings.

Even though the monitors are different sizes, it would be helpful to try to set your Windows Display Resolution to the same value on both, at least temporarily.

The steps vary slightly depending on which version of Windows you're running, but they're not hard to Google. (For example, here are instructions for Windows 7 installations.)

Check the following settings on both computers to see which options are available (not greyed-out) before changing anything, because you'll need to decide on a setting that is available on both.

---

Default Resolution Settings

• In Excel, click File → Options
• On the left side of the dialog, choose Advanced
• On the right side of the dialog, scroll down to Image Size & Quality

• Take note of which settings are selected and which are available:

  ☑ Do not compress images in file (checkbox)

  Default Resolution (drop-down)

For the next step you'll need to have a picture on a worksheet, so you may as well use the "problem images" from your example.

• Single-click the image to select it. This should make the Picture Tools / Format menu appear.

• Click Compress Pictures

• In the dialog that opens, under Resolution, take note of which settings are selected and which are available.

Hopefully there's a combination that is available on both machines. Once you've set them both the same, reboot both machines, and try inserting the pictures again.

As I mentioned, there could be a number of factors affecting it. The only way to ensure graphics behave identically on two separate machines is if they have identical software and graphics hardware.

---

If all else fails and you want to try one more long-shot, there is a Registry Setting that may be affecting one or both machines.

It's tough to find documentation on Office's thousands of registry settings (many not used by default, such as this one) - and one source implies that this only affects resolution of images in Office documents that are converted to web pages.

However, I believe that if you've ever saved an Excel document as a web page, this registry key (and it's related "known issue") could have been affected as well, so you could double-check.

---

Registry "Hack"

To work around this problem, add the following registry key to prevent the size change of the image.

Standard Registry Warning: Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

1. Exit all programs.

2. Hit the Windows Key, type regedit, and then hit Enter.

3. Locate and then select the following registry subkey:

   HKEY_CURRENT_USER\Software\Microsoft\Office\Common

4. After you select the subkey, click the Edit menu, click New and choose DWORD Value.

5. Type DownRevRasterizationDPI, and then press Enter.

6. Right-click DownRevRasterizationDPI, and then click Modify.

7. In the Value data box, type a value in the range of 1 to 300 and then click OK.

In the Value data box, set a value of the resolution that you want to specify for the image. For example, if you set 96, which is a default screen resolution value for Windows, and insert a screenshot into Excel, the image resolution of the screenshot becomes 96 dpi.

8. On the File menu, click Exit to exit Registry Editor.

Normally I like to include links to "More Information" in my answers, but I'm afraid there just isn't much out there besides the source link (above).

Good luck!