Solving Runtime Error 438 in VBA: Using SUMPRODUCT and INDIRECT Functions

Learn how to efficiently use VBA with `SUMPRODUCT` and `INDIRECT` functions in Excel while avoiding common issues like runtime errors. This guide provides clear step-by-step instructions and code examples for practical implementation.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: VBA code using sumproduct and indirect functions

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Solving Runtime Error 438 in VBA: Using SUMPRODUCT and INDIRECT Functions

When working with Microsoft Excel, especially with VBA (Visual Basic for Applications), you might encounter various runtime errors while trying to implement complex formulas. One common scenario arises when attempting to utilize the SUMPRODUCT and INDIRECT functions within your VBA code. In this guide, we will explore a specific case where a user faced runtime error 438 with their formula and provide a comprehensive solution.

Understanding the Problem

The user aimed to calculate the number of Saturdays between two dates located in cells F4 (start date) and F5 (end date) in their Excel sheet. They were using the following formula in cell F10:

[[See Video to Reveal this Text or Code Snippet]]

While implementing this via VBA, they encountered a runtime error 438, indicating an issue with the code. Let's break down the error and how to fix it.

Identifying the Issues

Incorrect usage of the Evaluate method: The Evaluate function should be applied at the worksheet level rather than the range level.

Unnecessary use of INDIRECT: Since cell references are being constructed in a string format, INDIRECT isn't required.

Returning the correct value format: To avoid misinterpretation of data types, it’s best to return the .Value2 to ensure we receive a numeric value without any formatting issues.

The Solution

Adjusting the Original VBA Code

The following VBA code snippet corrects the issues outlined:

[[See Video to Reveal this Text or Code Snippet]]

This adjustment makes the following changes:

Correct usage of Evaluate: Ensures it references the parent worksheet.

Eliminates the use of INDIRECT: Constructs the string from the cell values directly.

Implementing a Pure VBA Solution

If you prefer to avoid using the SUMPRODUCT function altogether and compute the count of Saturdays using pure VBA, you can implement a loop as shown below:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Pure VBA Code

Initializing Variables:

st and ed hold the start and end dates from cells F4 and F5.

cnt is initialized to 0 to count Saturdays.

Looping through Dates:

The loop iterates from the start date to the end date.

The condition If i Mod 7 = 0 checks if the current day is a Saturday and increments the count accordingly.

Output Result: Finally, the total count of Saturdays is placed in cell F10.

Conclusion

By understanding the limitations of the Evaluate method and the unnecessary complexities of using INDIRECT, you can effectively use VBA to calculate your desired outcomes in Excel. The provided solutions ensure you avoid common pitfalls, resulting in efficient and clean coding practices. If you find yourself facing similar issues in your Excel projects, refer back to this guide for troubleshooting and solution strategies.

Ensure you test the provided VBA code in your Excel environment for accuracy and to streamline your workflow. Happy coding!