Error passing element of string array get ByRef argument type mismatch

**welchs101** · 10-17-2011, 01:22 PM

I have an array that is defined as follows:

Please Login or Register  to view this content.

I assign values to the array like this

Please Login or Register  to view this content.

I then try to pass the first element of the array and thats when iget the ByRef error.

Please Login or Register  to view this content.

any ideas what i am doing wrong?

**tigeravatar** · 10-17-2011, 01:27 PM

welchs101,

Is the code for create_header_row_new_combined_file expecting a string or worksheet variable as its second argument?

**welchs101** · 10-17-2011, 01:34 PM

right now it is setup to expect a string variable

**tigeravatar** · 10-17-2011, 01:51 PM

welchs101,

Try using this for the Call line. It uses the StrConv method to ensure that sheet_array(1) is being passed as a string argument:

Please Login or Register  to view this content.

**welchs101** · 10-17-2011, 02:09 PM

that worked...........but i wonder why my original code did not work.........it should have ......right?

thanks again.

**Leith Ross** · 10-17-2011, 02:39 PM

Hello welchs101,

This gets into the world of ByRef and ByVal arguments. When an argument is passed ByRef (by reference) the address of the argument is passed. For this to happen, the argument must have been declared by either by a Dim statement or the procedure being called.

ByVal means only the value of the argument is being passed not the address pointing to it. The Call statement examines the argument list presented and decides if the argument is to be passed by value or by reference. If you wish, you can explicitly state how you want to pass the argument in the argument list by preceding the argument with ByRef or ByVal.

You defined the variable "sheet_array" as an Array which can be passed by reference but the individual elements have not. Since they have not been assigned, they must be passed by value. The Call statement figures that piece out for you.

**Colin Legg** · 10-17-2011, 02:59 PM

It is because each element in sheet_array() is a Variant type, but your create_header_row_new_combined_file() sub expects to be passed a String type. You can either explicitly cast to a String (recommended approach for which I would use CStr()), or you can force it to be passed ByVal by surrounding it with brackets.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Alternatively, of course, you could change the parameter declaration in create_header_row_new_combined_file so it expects aVariant or the argument to be passed ByVal, or you could change the sheet_array() declaration so it is a String array and populate it using Split() rather than Array().