Fix VBA Unable to Get the Sum Property of WorksheetFunction Error
Question details
The user encounters a runtime error when attempting to calculate the sum of cell values using the WorksheetFunction.Sum method in a VBA macro.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Writing or executing a VBA macro to sum a dynamic or static range of cells using string address variables.
- Observed behavior
- VBA throws an 'Unable to get the Sum property of the WorksheetFunction' error because a text string (such as '$C$24:$H$24') is passed directly into the function instead of a required Range object.
Before editing your VBA code, open the Visual Basic Editor and ensure that the string variables representing your cell addresses hold valid spreadsheet coordinates.
Convert Text Addresses into a Range Object
The WorksheetFunction.Sum method strictly requires a Range object. Wrapping your string variables inside a VBA Range() function resolves the data type mismatch.
When you pass a raw text string like "$C$24:$H$24" to the WorksheetFunction.Sum method, VBA cannot interpret it as a valid spreadsheet range. To fix this, you must convert the starting and ending string variables into a proper Range object before triggering the calculation.
Press ALT + F11 to open the VBA Editor and locate the specific line of code triggering the Sum property error.
Check the variables inside your Sum function (for example, StartAdr1 and EndAdr1) to confirm they contain text addresses.
Modify the code to wrap the addresses in a Range function using concatenation. Change the line to: TaxErrors = Application.WorksheetFunction.Sum(Range(StartAdr1 & ":" & EndAdr1)).
Alternatively, you can use the comma-separated Range syntax which automatically spans between two points: TaxErrors = Application.WorksheetFunction.Sum(Range(StartAdr1, EndAdr1)).
Write and Debug VBA Macros Seamlessly in WPS Spreadsheet
WPS Office features robust, built-in support for VBA macros, allowing you to seamlessly run, debug, and edit your Excel VBA scripts without compatibility issues.
- 1. Open your macro workbook: Launch WPS Spreadsheet and open the file containing your VBA script.
- 2. Access Developer Tools: Navigate to the 'Tools' tab on the top ribbon and click on 'Developer' to access macro features.
- 3. Open the VBA Editor: Click 'Visual Basic Editor' to view your code, apply the Range fix to your WorksheetFunction, and click 'Run' to test your updated script.
Frequently Asked Questions
Why does WorksheetFunction work differently than standard formula functions?
Standard formulas typed directly into spreadsheet cells can automatically interpret text representations of cell addresses. However, VBA's WorksheetFunctions strictly require VBA objects (like a Range object) to process references, meaning text strings must be explicitly converted within the code.
Can I use Evaluate instead of WorksheetFunction.Sum in VBA?
Yes. You can use the Evaluate function, which allows you to process string formulas directly without explicitly converting them to a Range object. For example, Evaluate("SUM(" & StartAdr1 & ":" & EndAdr1 & ")") will successfully calculate the sum using text addresses.
How do I ensure the VBA Sum function calculates data from a specific background sheet?
To reference a Range on a different worksheet, you must prepend the Worksheet object to the Range property. For example: Application.WorksheetFunction.Sum(Worksheets("DataSheet").Range(StartAdr1, EndAdr1)). This guarantees the data is pulled from 'DataSheet' regardless of which tab is currently active.
