Insert different data type into a function using array.

[Squareroot]

Hi,

I am trying to generate headers for a table. I decided to create a function just to generate header for my table. and to decide which row the header will go to my idea is to put the first element of my array(array that will be pass into the function)as the row number. following is my code.

Function create(header)
For x = 1 To 5
Sheets(1).Cells(header(0), x).Value = header(x)
Sheets(1).Cells(header(0), x).Interior.ColorIndex = 43

Next x
End Function
XXXXXXXXXXXXXXXXXXXXXXXXX main function XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Dim header As Variant
....
...
create (header = Array(1, "apple", "banana.", "carrot", "durian", "eggplant" ))
create (header = Array(2, "a", "b", "c", "d", "e" ))

the above code is not working as it will give me mismatch data type. Does anyone know how I can pass both int and string using the same array? or VBA doesn't allow user to do that.

 

[HaHoBe]

Hi Squareroot,

maybe like this:

Function fncCreateHeader(varHeader) As Boolean
'https://www.mrexcel.com/board/threads/insert-different-data-type-into-a-function-using-array.1222176/
Dim x As Long
On Error GoTo err_here
For x = LBound(varHeader) + 1 To UBound(varHeader)
  With Sheets(1).Cells(varHeader(LBound(varHeader)), x)
    .Value = varHeader(x)
    .Interior.ColorIndex = 43
  End With
Next x
fncCreateHeader = True
Exit Function

err_here:
End Function

Sub MrE1222176()
Dim varHeader As Variant
Dim blnRet As Boolean
'....
'...
varHeader = Array(1, "apple", "banana", "carrot", "durian", "eggplant")
If fncCreateHeader(varHeader) = False Then
  MsgBox "Error creating varHeader", vbInformation, "Ending here"
  Exit Sub
End If
varHeader = varHeader = Array(2, "a", "b", "c", "d", "e")
If fncCreateHeader(varHeader) = False Then
  MsgBox "Error creating varHeader", vbInformation, "Ending here"
  Exit Sub
End If
End Sub

Ciao,
Holger

 

[Alex Blakenburg]

@HaHoBe - I think you doubled up here:

varHeader = varHeader = Array(2, "a", "b", "c", "d", "e")

 

[Squareroot]

Hi Squareroot,

maybe like this:

Function fncCreateHeader(varHeader) As Boolean
'https://www.mrexcel.com/board/threads/insert-different-data-type-into-a-function-using-array.1222176/
Dim x As Long
On Error GoTo err_here
For x = LBound(varHeader) + 1 To UBound(varHeader)
  With Sheets(1).Cells(varHeader(LBound(varHeader)), x)
    .Value = varHeader(x)
    .Interior.ColorIndex = 43
  End With
Next x
fncCreateHeader = True
Exit Function

err_here:
End Function

Sub MrE1222176()
Dim varHeader As Variant
Dim blnRet As Boolean
'....
'...
varHeader = Array(1, "apple", "banana", "carrot", "durian", "eggplant")
If fncCreateHeader(varHeader) = False Then
  MsgBox "Error creating varHeader", vbInformation, "Ending here"
  Exit Sub
End If
varHeader = varHeader = Array(2, "a", "b", "c", "d", "e")
If fncCreateHeader(varHeader) = False Then
  MsgBox "Error creating varHeader", vbInformation, "Ending here"
  Exit Sub
End If
End Sub

Ciao,
Holger

it worked thank you

 

[Squareroot]

@HaHoBe - I think you doubled up here:

varHeader = varHeader = Array(2, "a", "b", "c", "d", "e")

thanks

 

[HaHoBe]

Thanks Alex, I really missed that when alteringn code changing names - glad you pointed it out.

Holger