Assign Excel Calculated Value Directly to Variable in Excel VBA

user_13551048 0
Viewed 46

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)
vba excel
3 Answers
'let excel take the strain - just take 1 from first of march
iDays = Day(DateSerial(iYear, 3, 1) - 1)
user_100168580

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
user_98140690

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
user_100168580
Related