Excel VBA跨模块变量调用问题：如何在EvaluateFormatSketch函数中获取PackageDimensionsCalculator子过程计算的PackageInnerHorizontalwidth值

Let’s break down this problem and fix it step by step—this is a common VBA pitfall related to variable scope and execution order, so we’ll address both core issues:

1. Correctly Declare the Variable as Module-Level Public #

First, you need to declare your variable outside of any subroutine or function at the very top of your module. This makes it accessible to all procedures in that module (and even other modules if needed).

If you previously tried declaring PackageInnerHorizontalwidth inside PackageDimensionsCalculator() (even with Public), that’s invalid—VBA doesn’t allow public declarations inside a procedure.

Example Module Setup:

' Declare this at the TOP of your module, before any Sub/Function
Public PackageInnerHorizontalwidth As Single ' Keep as Single to preserve decimal precision

Public Sub PackageDimensionsCalculator()
    ' Your actual calculation logic here
    ' Example:
    PackageInnerHorizontalwidth = (OuterWidth - 2 * PaddingValue) ' Replace with your formula
End Sub

Function EvaluateFormatSketch() As Boolean
    ' Now you can access the variable here
End Function

2. Ensure PackageDimensionsCalculator() Runs Before the Function #

If EvaluateFormatSketch() executes before PackageDimensionsCalculator() runs, your variable will still hold its default value (0 for numeric types). You have two reliable fixes here:

• Call the subroutine explicitly inside the function if you need to refresh the value every time:

  Function EvaluateFormatSketch() As Boolean
      ' Force recalculation of the width first
      PackageDimensionsCalculator
      
      ' Now use the updated value
      Debug.Print "Calculated Width: " & PackageInnerHorizontalwidth ' Test in Immediate Window
      
      ' Your other logic here
      EvaluateFormatSketch = True ' Adjust return value based on your needs
  End Function
  

• Align your workflow to trigger PackageDimensionsCalculator() first (e.g., a button click runs the subroutine before the function is called).

3. Revert to a Precise Numeric Type #

Switching from Single to Integer was likely a mistake. If your calculation returns a decimal value (e.g., 12.3), converting it to Integer truncates the decimal part—this could even result in 0 if the calculated value is less than 1. Stick with Single or Double to preserve precision.

4. Double-Check for Typos #

It’s easy to mistype long variable names. Make sure PackageInnerHorizontalwidth is spelled exactly the same in both procedures—VBA is case-insensitive, but consistency helps avoid silly errors.

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