CODEWITHSUNDEEP
excelvba
West
West

Reputation: 2570

How to use a date variable in VBA

I have cell A1 in excel with the value 3/09/2018. I can convert it to a date in VBA as follows.

startdate = Format(#3/09/2018#, "Short Date")

However I cant get this to work with a variable, for example:

value1=  ActiveSheet.Range("A1").Value
final_value = "#" & value1 & "#"
startdate= Format(final_value,"Short Date")

How can I get this working with the final_value variable? I am trying to get the result working with a filter in this way:

ActiveSheet.ListObjects("Table1").Range.AutoFilter =1, Criteria1=">="startdate

Upvotes: 1

Views: 3364

Answers (2)

Rory
Rory

Reputation: 34075

Assuming startdate is declared as a Date variable, you could just use:

startdate = ActiveSheet.Range("A1").Value

Now that you've clarified what you're doing, I'd use a Double for an autofilter (especially if there might be international issues) or a Long if you aren't interested in time portions:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1=">=" & ActiveSheet.Range("A1").Value2

or:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1=">=" & CLng(ActiveSheet.Range("A1").Value2)

Upvotes: 2

user4039065
user4039065

Reputation:

Why bother with the var being a date-type? All you need to do anything in VBA is 43168 (or 43346 if you use DMY and thought VBA was DMY-friendly).

dim startDate as long
startDate = ActiveSheet.Range("A1").Value2

Upvotes: 1

Related Questions