Dim a Value VBA

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
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

Code:
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?
 
Last edited:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
 
Last edited:
Upvote 0
Option Explicit
Dim X as Value

Sub working

X = range("r1")

end sub
 
Upvote 0
Code:
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
'************************************************************
 
Last edited:
Upvote 0
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):
Code:
Text111=DateDiff(...)
Text123=Text111
'And so on

Assuming you have a good way of getting that first value in Text111
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top