CODEWITHSUNDEEP
vbaexcel
Lamar Latrell
Lamar Latrell

Reputation: 1670

How to set a VBA variable to throw an error when used?

Is there a value that I can set a double to in VBA such that any attempt to do math with it will throw an error that will cause the line of code to not complete, but simply move to the next line instead?

Context:

I want to calculate values that have a functional relationship with each other (the mathematical kind).

The intention is to have excel cells being filled in by a user and the rest automatically filling in once there is enough information for them to be defined - to completion, if possible.

Any fields can be filled in, in any order...

(some more context here: https://superuser.com/questions/1213399/how-to-solve-for-missing-value-in-equation-in-excel/1213440#1213440)

I planned on doing this like so:

Sub myCalculator()
    On Error Resume Next   '<- a key part of this

    Dim startPosition As Double
    Dim endPosition As Double
    Dim distanceTravelled As Double

    'either take the value from the cells OR define it with the
    ' answer ('???') I'm looking for:
    If IsEmpty(Range("A1").Value) Then 
         startPosition = ???
    Else startPosition = Range("A1").Value
    EndIf
    If IsEmpty(Range("A2").Value) Then 
         endPosition = ???
    Else endPosition = Range("A2").Value
    EndIf
    If IsEmpty(Range("A3").Value) Then 
         distanceTravelled = ???
    Else distanceTravelled = Range("A3").Value
    EndIf

    'now run through the values and fill them if we can:
    startPosition = endPosition - distanceTravelled  
    endPosition = startPosition + distanceTravelled
    distanceTravelled = endPosition - startPosition

    'My idea here is that if say 'endPosition' was set to be ???
    'then the first line of code would simply error, and then
    'ResumeNext and then endPosition would actually be calculated,
    'ready for the next line which would then be able to actually
    'use it.

    'then fill in the cells with the old (untouched) and new info:
    Range("A1").Value = startPosition
    Range("A2").Value = endPosition
    Range("A3").Value = distanceTravelled

End Sub

vbNull and Nothing both seem to evaluate to '0' at some step or another. which means that I get garbage values where zero has been used instead of the line simply throwing an error and being missed.

I should point out that in the real case I would loop over the section that does the actual math in the case that not all values are filled in the first pass ... (also, contradictory and under-defined situation would be dealt with.)

This example is basic and made up, it certainly could be dealt with in better ways, I'm simply trying to keep succinct for discussion.

I dont plan on becoming a VBA professional ;), if there is such a solution but it would be seen as a bodge, I'd be happy with that. Hopefully there is a basic answer otherwise?

Upvotes: 0

Views: 473

Answers (1)

YowE3K
YowE3K

Reputation: 23984

Don't use Double, use Variant (and, more specifically, Variant/Double and perhaps Variant/Error):

Sub myCalculator()
    'On Error Resume Next   '<- Only use this on the lines when you expect to need it
    Dim startPosition As Variant
    Dim endPosition As Variant
    Dim distanceTravelled As Variant

    'either take the value from the cells OR define it with 
    ' an error value:
    If IsEmpty(Range("A1").Value) Then
        startPosition = CVErr(xlErrNA)
    Else
        startPosition = Range("A1").Value
    End If
    If IsEmpty(Range("A2").Value) Then
        endPosition = CVErr(xlErrNA)
    Else
        endPosition = Range("A2").Value
    End If
    If IsEmpty(Range("A3").Value) Then
        distanceTravelled = CVErr(xlErrNA)
    Else
        distanceTravelled = Range("A3").Value
    End If

    'now run through the values and fill them if we can:
    On Error Resume Next
    startPosition = endPosition - distanceTravelled
    endPosition = startPosition + distanceTravelled
    distanceTravelled = endPosition - startPosition
    On Error GoTo 0

    'then fill in the cells with the old (untouched) and new info:
    Range("A1").Value = startPosition
    Range("A2").Value = endPosition
    Range("A3").Value = distanceTravelled
End Sub

Upvotes: 3

Related Questions