Dim a Value VBA

[gheyman]

I need to use a value in several places in my code, so I know I need to "DIM" it and set it, but I have no idea how.

In this formula

Sub CostRvw_Planned_Vs_Actual()
'CostRvw10
    If IsDate(CostRvw_Actual) And IsDate(CostRvw_Planned) Then
        CostRvw_C = DateDiff("d", CostRvw_Planned, CostRvw_Actual)
    End If

End Sub

in this i am stating that CostRvw_C is where the value of the datediff formula goes

in another part of my code I want to use the same value of DateDiff("d", CostRvw_Planned, CostRvw_Actual).



how do I set the value of this formula so I can keep using it without rewriting it?

 

[mole999]

try this > https://www.excel-easy.com/vba/examples/variable-scope.html near the bottom of the page above the first sub

 

[gheyman]

I'm not seeing it.

I guess I am trying to do something like

Dim X as a Value
x= If IsDate(CostRvw_Actual) And IsDate(CostRvw_Planned) Then
DateDiff("d", CostRvw_Planned, CostRvw_Actual)
End If

Then I could say

Text111=X
Text123=X

And so on
End Sub

 

[mole999]

Option Explicit
Dim X as Value

Sub working

X = range("r1")

end sub

 

[Joe4]

Dim X as Value

I don't believe that "Value" is a valid data type to declare a variable as.
See this for the different data types: https://www.accessallinone.com/data-types-in-vba/

 

[james_lankford]

Option Explicit
'************************************************************
Sub doit()
    
    Dim CostRvw_Planned As Variant
    Dim CostRvw_Actual As Variant
    Dim CostRvw_C As Variant
        
    CostRvw_Planned = #1/1/2018#
    CostRvw_Actual = #5/5/2018#
    
    CostRvw_C = Null
    
    CostRvw_C = get_CostRvw_C(CostRvw_Planned, CostRvw_Actual)
    
    If Not IsNull(CostRvw_C) Then
        Debug.Print "THE NUMBER OF DAYS IS " & CostRvw_C
    Else
        Debug.Print "NOT A DATE"
    End If
    
    CostRvw_Planned = #1/1/2018#
    CostRvw_Actual = Null
    
    CostRvw_C = Null
    
    CostRvw_C = get_CostRvw_C(CostRvw_Planned, CostRvw_Actual)
    
    If Not IsNull(CostRvw_C) Then
        Debug.Print "THE NUMBER OF DAYS IS " & CostRvw_C
    Else
        Debug.Print "NOT A DATE"
    End If
    
End Sub
'************************************************************
Function get_CostRvw_C(CostRvw_Planned As Variant, CostRvw_Actual As Variant) As Variant
    
    If IsDate(CostRvw_Actual) And IsDate(CostRvw_Planned) Then
        get_CostRvw_C = DateDiff("d", CostRvw_Planned, CostRvw_Actual)
    Else
        get_CostRvw_C = Null
    End If
    
End Function
'************************************************************

 

[xenou]

The above idea (using a function) is good.

Also, by the way,

Then I could say

Text111=X
Text123=X

And so on

You could instead say (whatever the reason for duplicating the data on the form is here):

Text111=DateDiff(...)
Text123=Text111
'And so on

Assuming you have a good way of getting that first value in Text111