0

This is to do with ranges and arrays, which I find utterly confusing!

I am reading a range into an array variable as follows:

Output = Range(Results.Cells(2, 1), Results.Cells(2, 100)).Value

where Output is a Variant array of size 100, and Results is the name of the worksheet.

I want to pass this array Output as an argument to another sub as follows:

DBUpdate (Output)

The DBUpdate sub is defined as:

Sub DBUpdate(Output() as Variant)

However, when I run this, I get the following error:

Compile error:
Type mis-match: array or user-defined type expected

As I understand, I have defined Output as an array throughout and I have tried everything imaginable to try to overcome this, but failed. Can anyone here point me in the right direction, please?

GSerg
  • 76,472
  • 17
  • 159
  • 346
KMLN
  • 79
  • 2
  • 3
  • 14
  • 1
    You don't have to specify the `Output` parameter as an array. So instead of `Output() as Variant` just list it as `Output as Variant`. – PeterT Jul 03 '17 at 22:02
  • @PeterT thanks. I changed the code as you suggested, and it works. But I need to extract the elements of the array that I am passing on the sub DBUpdate - basically I need to join them together to pass as values in an SQL query. Is there a way I can extract them like output(0), output(i) etc? – KMLN Jul 03 '17 at 22:26
  • @KMLN While changing `Variant()` to `Variant` suppresses the error, it is neither the source nor the correct solution. The correct solution is given in the duplicate question. You can keep the `Variant` though and [access it like an array anyway](https://stackoverflow.com/a/2265563/11683). – GSerg Jul 03 '17 at 22:37
  • @GSerg Read the entire post, but still not clear how I can use the variant containing the whole array and access its individual elements. Can I use a the 'split' method to assign the elements to a separate string array? – KMLN Jul 03 '17 at 22:53
  • 1
    @KMLN from the way you read ranges into your array, it is likely to be a 2 dimensional array with base 1. u can either use `for each vEle in Output strSQL = strSQL & vEle & ";" next vEle` for example, or via indexing like `for i = lbound(Output,2) to ubound(Output,2) strSQL = strSQL & Output(1, i) & ";" next i` – AiRiFiEd Jul 04 '17 at 06:03
  • @AiRiFiEd that worked beautifully, thanks a lot for your help – KMLN Jul 04 '17 at 13:01

0 Answers0