To change the zoom of the active Excel worksheet with VBA, set ActiveWindow.Zoom to a percentage from 10 to 400, such as ActiveWindow.Zoom = 110. To fit a selected range to the window instead, select the cells first and set ActiveWindow.Zoom = True.
What Excel VBA Zoom Controls
Zoom changes how large worksheet cells and their contents appear on screen. It does not change the stored values, resize the actual cells, or alter the printed size of a report. For example, setting the zoom to 80 makes the sheet appear smaller so more rows and columns fit on screen; setting it to 125 makes the content appear larger and usually shows less of the sheet at once.
In VBA, zoom is controlled through the Zoom property of a Window object. The usual reference, ActiveWindow.Zoom, means the window Excel currently considers active. The value is a percentage, normally an integer from 10 through 400. A zoom change applies to the active sheet in that window, not automatically to every worksheet in the workbook.
That distinction matters because Excel can have multiple workbook windows open, including two windows for the same workbook. The active window determines which view VBA changes. Also, zoom is a display setting: if you need to change printed output, use page layout and print scaling settings rather than this property.
Set a Fixed Zoom Percentage
A short macro is enough to set a consistent magnification. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the procedure into the module. Run it while the worksheet and window you want to change are active.
Sub SetActiveSheetZoom()
Dim targetZoom As Long
' Choose a zoom percentage between 10 and 400.
targetZoom = 110
If targetZoom < 10 Or targetZoom > 400 Then Exit Sub
ActiveWindow.Zoom = targetZoom
End Sub
Before running a macro, save a copy of the workbook: macro changes cannot be reversed with Ctrl+Z. The range check in this example is useful if you later replace the fixed number with a value read from a cell or supplied by a user. Excel normally rejects zoom values outside its supported range, but checking first makes the intended limits clear.
To run the procedure, return to Excel and press Alt+F8, select SetActiveSheetZoom, and choose Run. You can also run it from the Visual Basic Editor. On a Mac, the Visual Basic Editor shortcut is commonly Option+F11, sometimes with Fn as well depending on the keyboard; Developer > Visual Basic is an alternative. The macro itself is largely the same on Windows and Mac, though some workbook features and shortcuts can differ.
Fit a Selected Range to the Window
A fixed percentage is predictable, but it may not be ideal when the selected area changes size. To make a selected table or chart-adjacent block fill the available worksheet area, select the range and use the special Boolean value True for the zoom property. Excel chooses a magnification that fits the selection in the active window.
Sub ZoomToSelectedCells()
' Only worksheet cell ranges can be fitted this way.
If TypeName(Selection) <> "Range" Then Exit Sub
ActiveWindow.Zoom = True
End Sub
For example, select A1:H25 before running the macro to fit that block. If the selected area is very wide or tall, Excel may choose a low zoom so the entire selection is visible. That is expected: fitting the range prioritizes showing all selected cells, not making the text comfortable to read. A selection containing only one or two cells may produce a very high zoom, so use a fixed percentage when a consistent reading size matters more than fitting the selection.
The selected-range method does not permanently define a special zoom for the range. It changes the current sheet view based on the selection at that moment. If a user later changes the selection, the zoom generally remains at the resulting percentage until another action changes it.
Apply the Same Zoom to Every Visible Worksheet
If a workbook has many sheets, setting the zoom one sheet at a time is repetitive. The following procedure visits each visible worksheet in the workbook containing the code, applies the same zoom, and then returns to the sheet that was active when the macro began. It skips hidden and very hidden worksheets because those sheets cannot be activated like visible tabs.
Sub SetZoomForVisibleWorksheets()
Dim ws As Worksheet
Dim originalSheet As Worksheet
Dim targetZoom As Long
' Save a valid percentage and remember the starting sheet.
targetZoom = 100
If targetZoom < 10 Or targetZoom > 400 Then Exit Sub
Set originalSheet = ActiveSheet
' Set the active window zoom on each visible worksheet.
For Each ws In ThisWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
ws.Activate
ActiveWindow.Zoom = targetZoom
End If
Next ws
' Restore the original active sheet.
originalSheet.Activate
End Sub
This version uses ThisWorkbook, which refers to the workbook where the macro is stored. If you keep the macro in Personal.xlsb or an add-in and intend to change the workbook currently selected in Excel, the target workbook logic needs to use the appropriate workbook object instead. Be especially careful when several workbooks are open: a macro can affect a different file than expected if its workbook reference is not explicit.
The macro activates sheets as it processes them, so Excel may visibly switch tabs while it runs. For a normal workbook this is usually brief. If a workbook has protected structure, unusual window arrangements, or sheets that cannot be activated, test the procedure on a copy and adjust it for that workbook rather than assuming every sheet can be changed in the same way.
Where Excel Stores the Zoom Setting
Zoom belongs to a window’s view of its active worksheet, which explains why it is not simply a property such as Worksheet.Zoom. The same worksheet can be displayed in more than one window, and each window can have its own view. A macro using ActiveWindow changes the window that is active when the line runs. It does not loop through every open window automatically.
In ordinary use, Excel retains worksheet view settings, including zoom, when the workbook is saved. If a workbook opens at an unexpected magnification, check whether the file was saved after the desired view was set, whether the macro targeted the intended window, and whether another macro or workbook-open event changes the view. A zoom macro is not a replacement for setting every relevant sheet and window when a workbook deliberately uses multiple views.
For a one-time manual change, use View > Zoom and choose a percentage, or use the zoom slider on the status bar. View > Zoom to Selection fits the current selection without VBA. The status-bar slider is convenient for interactive adjustment, while a macro is useful when a repeatable percentage must be applied as part of a workflow.
Zoom Versus Print Scaling and Cell Size
A common misconception is that zoom makes a worksheet print larger or smaller. It does not. Zoom only changes the on-screen view. To fit a printed worksheet to pages, use the page layout or print scaling controls; for example, inspect the scaling choices under Page Layout and the print preview before printing. Page scaling can reduce text on paper even when the worksheet is displayed at 100 percent.
Zoom also does not change column width, row height, font size, or the size of shapes in the workbook. Those are separate properties. If a report looks cramped at 100 percent, increasing zoom may make it easier to inspect but will show fewer cells. If the report itself needs larger text or wider columns for everyone, edit the cell formatting or layout instead of using a display-only zoom adjustment.
This difference is useful when troubleshooting. If the worksheet looks right on screen but prints too small, changing ActiveWindow.Zoom will not solve the print problem. If only one person needs a larger on-screen view, changing the zoom is less disruptive than changing shared formatting that affects the workbook’s layout for all users.
Common Problems and Practical Safeguards
- The zoom does not change the sheet I expected: Check which workbook window is active and which sheet is active in that window. The macro uses the active window unless you deliberately activate another one.
- The value causes an error: Keep numeric zoom values within 10 to 400. A variable read from a cell should be validated before assigning it to the property.
- The macro is missing from the macro list: Confirm the code is in a standard module, macros are permitted by your organization’s security settings, and the workbook is saved in a macro-enabled format such as .xlsm. Do not enable macros in files from an untrusted source.
- The view changes back: Look for other VBA procedures, including workbook-open or worksheet-activation events, that set zoom again. Also confirm that you saved the workbook after making the desired view change.
- The procedure changes only one sheet: That is the expected behavior of the short active-sheet example. Use a loop that activates each intended worksheet if you need a workbook-wide change.
For a workbook that other people will use, avoid forcing a new zoom every time they select a sheet unless that behavior is genuinely required. A startup macro that overrides saved views can frustrate readers who use different screens or accessibility settings. If you do add automatic zoom behavior, document it and provide a clear way to turn it off.
Frequently Asked Questions
What is the VBA code to set Excel zoom to 100 percent?
Use ActiveWindow.Zoom = 100 while the target worksheet is active. It sets the current window’s view of that sheet to 100 percent; it does not set every sheet in the workbook.
Can VBA zoom to fit the selected cells?
Yes. Select a cell range, then assign True to ActiveWindow.Zoom. Excel picks a zoom that fits the selection in the active window. Use a numeric percentage instead if you need consistent magnification.
Can I set a different zoom for each worksheet?
Yes. Activate each worksheet and set ActiveWindow.Zoom while it is active. A loop can assign the same value to all visible sheets, or conditional logic can assign different values based on each sheet’s name or purpose.
Does VBA zoom affect printing?
No. It changes screen magnification only. Print size and page fit are controlled by print scaling and page layout settings, which should be checked separately in print preview.
Why does ActiveWindow.Zoom affect only one sheet?
The property changes the view of the active sheet in the active window. It does not represent a workbook-wide zoom command. To change several worksheets, activate each one and set the property for each view.
Can I undo a zoom macro with Ctrl+Z?
No. Running VBA clears Excel’s normal undo history, and macro actions cannot be reversed with Ctrl+Z. Save a copy before running a macro that changes workbook views or other content.
Related
- Conditional Formatting in Microsoft Project: What Works
- Data Validation and Verification in Excel Explained
- Power Query Replace Text: Steps, Options, and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Error Handling for Multiple Errors in One MacroVBA handles runtime errors one at a time: use On Error GoTo to route an unexpected failure to…
- Excel VBA Bold Text: Cells, Ranges, and Partial TextIn Excel VBA, make a cell or range bold by setting its Font.Bold property to True, such as…
- Visual Basic: Add a Comment to an Excel Cell with VBAYes. In Excel for Microsoft 365 on Windows, use VBA’s Range.AddComment method to add a note to a…
- VBA InputBox Character Limit and Longer Text OptionsThe standard VBA InputBox is limited to about 255 characters of entered text, so it is not suitable…
- Excel VBA: Find a Date in a RangeIn Excel VBA, use the Range.Find method to locate an exact date in a range; set its search…
- Excel VBA Range Interior Color: Set and Read Cell FillsIn Excel VBA, use the Interior.Color property to set or read a cell’s fill color: for example, Range("A1").Interior.Color…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.