In managing date and time in programming, especially when working with Excel, we often need to extract specific components of a timestamp, like the hour. This is where the VBA HOUR function becomes a handy tool. I’ve found that in writing macros, being able to call upon this function allows for a broad range of applications, such as scheduling tasks or analyzing time data.

Understanding the VBA HOUR function opens up more possibilities in terms of the complexity and utility of your VBA scripts. What strikes me the most is its simplicity: give it a time, and it’ll give you the hour component, from 0 to 23. As a code, it feels familiar because it works just like its counterpart in the Excel worksheet environment. It’s a focused function that knows its job: to capture the hour, nothing more, nothing less.

Syntax

Hour(Time)

Arguments

When I use VBA’s Hour function, here’s what I pass as the parameter:

  • Time Argument: The time parameter accepts:
    • A date variable representing a specific time
    • The word "Now" to fetch the current hour
    • A numeric expression indicating time
    • A string expression resembling a time value (e.g., “6:45 PM”)

The function then gives me an hour value as an integer within 0 to 23.

Example

In VBA, extracting the hour from a time value can be incredibly straightforward. Check out my macro code below:

Sub example_HOUR()
    Range("B1").Value = Hour(Range("A1"))
End Sub

This line essentially tells Excel to take the time noted in cell A1 and to output the hour component into cell B1. For instance, if A1 reads “10:34:45 AM”, B1 will display “10”. It’s honestly that simple to use the Hour function within a macro to manage time values in Excel.

example-vba-hour-function

Notes

  • When I use VBA’s Hour function, it’s expecting a value within the 0 to 23 range, representing an hour of the day.
  • If I input an invalid time or a confusing string, VBA gives me a run-time 13 error.
  • Just a heads up, a NULL value in gives a NULL result out.
  • It’s all part of the charm in the Excel VBA development environment—got to keep an eye on those details!
  • Supported across various Excel versions like Excel 2016, Excel 2013, and all the way back to Excel 2003.

Similar Posts