Excel VBA: how do I copy a range to an array as text?

February 16, 2022

I want to copy a range of cells (custom format) filled with time data (e.g. 8:00, 7:30, 5:45, …) as text to write to another program through Application.SendKeys. When I grab the cells as they are, they’re written out as e. g. 1.041666666 instead of 7:30. How do I copy or convert them to text? Trying to get the value/text from the whole range as I copy won’t work, neither did attempts at looping through afterwards to change the values separately. There might be workarounds using clipboard, but I want to leave it untouched. Code right now:

DayArray = Application.ActiveSheet.Range("A1:E4")

For j = 1 To UBound(DayArray)
    For k = 1 To 5 'fixed column count
        DayArray(j, k)= DayArray(j, k).Text
        Next k
    Next j
 
(... SendKeys example)
Application.SendKeys DayArray(1, 1), True

>Solution :

Use the Format() Function. It returns a string in the format desired:

DayArray = Application.ActiveSheet.Range("A1:E4")

For j = 1 To UBound(DayArray)
    For k = 1 To 5 'fixed column count
        DayArray(j, k)= Format(DayArray(j, k),"h:mm")
    Next k
Next j