Using VBA to test whether values input into a cell throw an error in another cell

Copper Contributor

I have this function called funcContentsErrorFree that takes three arguments and returns a Boolean value.

 

Arguments:

  • Range address As String to a series of values (or single value)
  • Range address As String to a single cell to input those values
  • Range address As String to a cell affected by changing the input cell

Note: For ease, let's think in terms of values, input, and output.

 

Function Returns:

  • True if all values are plugged into the input and there is no error in the output cell.
  • False if a single value causes an error in the output cell when plugged into the input cell.

 

Here's the strange thing. This function is located in a module called mRangeValidationFunctions. When I run a test in that module, the function works fine. When I run a test in frmGoalSeekPlus, the Userform crashes. 

 

When I run the function in mRangeValidationFunctions with the same string addresses passed from frmGoalSeekPlus, the function works. So, I thought the location of the function might be an issue. However when I cut/copied the function into frmGoalSeekPlus, it still crashed the form. So, I don't think the location should be an issue. (To further support this thesis, I have two other functions in mRangeValidationFunctions that work fine.)

 

I'm hoping this is a simple mistake that I'm overlooking. I've included the code and placed comments to make the error section obvious. I've also included the tests in frmGoalSeekPlus and mRangeValidationFunctions so you can see that the function works in the module, but not the form.

 

All sections are commented for ease of navigation. Use the comment banners and to navigate to the related code. Really hoping to get a second set of eyes on this one.

 

mRangeValidationFunctions

  • Function Location: mRangeValidationFunctions > Range Validation Functions > Function funcContentsErrorFree()
  • Function Local Test: mRangeValidationFunctions > Range Validation Function Tests > sub test_funcContentsErrorFree()

frmGoalSeekPlus

  • Function call location: frmGoalSeekPlus > Control Change Subs >sub refEditInputA_Change()
  • Unit test for refEditInputA_Change(): frmGoalSeekPlus > Change Event Tests > Section 4 > sub test_refEditInputA_change()
  • Userform Master Test: frmGoalSeekPlus > Master Test > Sub cmdTest_Enter()

 

Note: The goal for this project to make GoalSeek dynamic and give it the power to do some scenario analysis.

1 Reply

Update:

The source of the error is the Range(refInput).Value assignment lines. See Screenshot.

 

When these lines are commented out, the code runs error-free but does not perform its designed task.

 

Riley_Johnson_0-1713921159896.png