CODEWITHSUNDEEP
excelvba
ByteDet
ByteDet

Reputation: 13

Assign Excel Calculated Value Directly to Variable in Excel VBA

I am attempting to create an annual calendar that will automatically determine is year of calendar is a Leap Year. I can do this using a formula in a cell then assigning the value of that cell to the variable. Is is possible to assign the iDays value without first writing the formula to the worksheet then assigning the value of the cell with the formula to the iDays variable.

Dim iYear As Integer, iDays As Integer
Dim strPrompt As String, strTitle As String

strPrompt = "Enter Year of Calendar"
strTitle = "YEAR"
iYear = InputBox(strPrompt, strTitle) 'Year of the calendar to be created.
ActiveSheet.Range("AK1").Value = iYear

'Formula to determine if the year is a Leap year
ActiveSheet.Range("Z1").Formula = "=IF(OR(MOD(AK1,400)=0,AND(MOD(AK1,4)=0,MOD(AK1,100)<>0)),29, 28)"
iDays = ActiveSheet.Range("Z1").Value 'Value of iDays used to cut the month of February at 28 or 29.

MsgBox "There are " & iDays & " days in the month of February " & iYear & "."

I've done this before with VLookup using WorksheetFunction with the following code:

lStart = Application.WorksheetFunction.VLookup(dStart2, rng, 3, False)

Upvotes: 1

Views: 87

Answers (3)

JohnnieL
JohnnieL

Reputation: 1231

Hopefully i deconstructed it ok. You have Mod and And and Or available as operators in VBA

If iYear Mod 400 = 0 Or (iYear Mod 4 = 0 And iYear Mod 100 <> 0) Then
  iDays = 29
Else
  iDays = 28
End If

Upvotes: 0

JohnnieL
JohnnieL

Reputation: 1231

'let excel take the strain - just take 1 from first of march
iDays = Day(DateSerial(iYear, 3, 1) - 1)

Upvotes: 1

VBasic2008
VBasic2008

Reputation: 54777

Test Leap Year

Function isLeapYear( _
    TestYear As Long) _
As Boolean
    If TestYear Mod 4 = 0 Then
        If TestYear Mod 100 = 0 Then
            If TestYear Mod 400 = 0 Then
                isLeapYear = True
            End If
        Else
            isLeapYear = True
        End If
    End If
End Function

With Comments

Function isLeapYearCommented( _
    TestYear As Long) _
As Boolean
    If TestYear Mod 4 = 0 Then
        If TestYear Mod 100 = 0 Then
            If TestYear Mod 400 = 0 Then
            ' Accounting for e.g. years 2000, 2400, 2800...8800, 9200, 9600.
                isLeapYear = True
            'Else
            ' Accounting for e.g. years 2100, 2200, 2300...9700, 9800, 9900.
            'isLeapYear = False
            End If
        Else
        ' Accounting for e.g. years 1904, 1908, 1912...1988, 1992, 1996.
            isLeapYear = True
        End If
    'Else
    ' Accounting for e.g. years 1901, 1902, 1903...1997, 1998, 1999.
    'isLeapYear = False
    End If
End Function

Upvotes: 0

Related Questions