title: VBA Array/Sharepoint confusion
body: <p>I am working on a project that involves pulling information from Sharepoint, different (but known) sheets. What I am attempting to do is use <code>TestNameCaller</code> to do the following:</p>
  
  <p>Go through, check the sheet. If the cell has a name in it, (test in this case), it will then call the array, then load variable based on j (in this case 1, which correlates to the 'Tests' book). Then use the case to filter though which information gets pulled, and where to put it in the main sheet.</p>
  
  <p>Below is what I have. The principle is for Cells(5,3), to reference the Sharepoint link, specific worksheet, and cell.</p>
  
  <pre><code>Function TestNameCaller(Arrname() As Variant) As String
  
  Dim i As Integer
  Dim j As Integer
  Dim name As String
  Dim link As String
  
  j = 1
  
  For i = 3 To 8
      If Cells(3, i) &lt;&gt; "" Then
          name = Cells(3, i)
          link = Arrname(j)
          Select Case name
  
              Case "Test"
                  Cells(5, 3) = Workbooks(link).Worksheets("Sheet1").Cells(5, 7)
          End Select
      End If
  Next i
  
  End Function
  
  -----------------------------
  
  Sub ArrayCaller()
  Dim fString As String
  Dim Arrname As Variant
  
  Arrname(1 To 2)
  Arrname(1) = "https://xxx.sharepoint.com/Shared Documents/Sheets/Test.xlsx"
  Arrname(2) = "https://xxx.sharepoint.com/Shared Documents/Sheets/Book.xlsx"
  
  fString = TestNameCaller(Arrname())
  End Sub
  </code></pre>