修正VBA宏：实现按变量定位Striped Right Arrow 2图形

Hey, let's get that shape positioning sorted out! The issue with your broken code is most likely related to unqualified worksheet references for the Cells object, plus we can clean up the code to make it more reliable and readable.

Here's the Corrected Code #

Sub mymacro()
    ' Declare your variable explicitly to avoid type issues
    Dim n As Integer
    n = 6
    
    ' Store the target shape in a variable to avoid repeated lookups
    Dim targetArrow As Shape
    Set targetArrow = ActiveSheet.Shapes("Striped Right Arrow 2")
    
    ' Use ActiveSheet.Cells to ensure we're referencing the correct sheet
    With ActiveSheet.Cells(2 * n + 10, n + 5)
        targetArrow.Top = .Top
        targetArrow.Left = .Left
    End With
End Sub

Key Fixes & Improvements #

• Explicit Variable Declaration: Adding Dim n As Integer prevents VBA from treating n as a Variant, which can cause unexpected calculation quirks.
• Shape Variable: Storing your arrow in targetArrow means we don't have to search for it every time we want to adjust its position—this is faster and less error-prone.
• Qualified Cells Reference: Using ActiveSheet.Cells instead of just Cells ensures we're grabbing the position from the same sheet where the shape lives. Without this, VBA might accidentally use a different active sheet's cells, leading to wrong positioning.
• With Statement: This cleans up the code by letting us reference the target cell once, instead of repeating ActiveSheet.Cells(...) twice.

Quick Troubleshooting if It Still Doesn't Work #

If the shape still isn't moving where you expect, check these things:

• Make sure n + 5 doesn't result in a column number beyond Excel's limit (XFD is column 16384—so n would need to be way larger than 6 for this to be an issue, but worth checking if you change n later)
• Verify the target cell isn't hidden—hidden cells can return unexpected Top/Left values
• Double-check the shape name is exactly correct (spaces, capitalization, and all—Excel's shape names are case-insensitive, but it's best to match the name exactly as it appears in the Selection Pane)

内容的提问来源于stack exchange，提问作者tmtran99