CODEWITHSUNDEEP
arraysexcelvbarange
Spurious
Spurious

Reputation: 1995

Create one row array from range (error handling)

I have a dynamic table with several columns including filenames.
Sometimes it happens that I only have one row but I still want to run the upcoming function.

The values are assigned like this:

arrFileNames = Range("Links[PDF_File]").Value

I receive a runtime error 13 in this line:

ReDim arrFilesExistence(1 To UBound(arrFileNames))

This happens because the the array is now only a one row range and thus get's saved as a string.

I tried to circumvent this by trying to redim but it does not seem to work:

If Range("Links[PDF_File]").Count = 1 Then
    arrFileNames = Array(arrFileNames)
    ReDim Preserve arrFileNames(1 To 1)
End If

I cannot seem to find a way. The workaround lets the array be a UBound = 0 array.

Anyone with a working solution?

Upvotes: 0

Views: 440

Answers (2)

gembird
gembird

Reputation: 14053

In case that the range contains only one cell then the value of such range is not array. So with IsArray() function we can check if value of a range is array and use ReDim to create space in our arrFileNames. It has then one row and one column and finally we assign the value to it. HTH

Dim data As Variant
Dim arrFileNames As Variant

data = Range("YourRangeName").value

If Not IsArray(data) Then
    ' data consists of one cell only
    ReDim arrFileNames(1 To 1, 1 To 1)
    arrFileNames(1, 1) = data
Else
    arrFileNames = data
End If

Upvotes: 1

Mailkov
Mailkov

Reputation: 1231

You can use on error resume next 

On Error Resume Next
arrFileNames = Range("Links[PDF_File]").Value
ReDim arrFilesExistence(1 To UBound(arrFileNames))
If Err.Number <> 0 Then
ReDim arrFilesExistence(1 To 1)
End If

Upvotes: 0

Related Questions