You can get the number of records using the rs.getRows function.

Function invested_funds() As Integer
    Dim c As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim connectionstring As String
    Dim sql As String
    
    connectionstring = "Provider=SQLOLEDB;Data Source=DESKTOP-2TTG3GQ\SQLEXPRESS;" & _
                       "Initial Catalog=DB;" & _
                       "Integrated Security=SSPI;"
    
    Set c = New ADODB.Connection
    Set rs = New ADODB.Recordset
    c.Open connectionstring
    
    sql = "select [DB].[dbo].[db].[CSRoot] " & _
          "from [DB].[dbo].[db] "
    
    If c.State = adStateOpen Then
        Debug.Print ("Connected") 'This prints!
    End If
    
    Set rs = c.Execute(sql)
    
    Dim arr As Variant, n As Integer
    
    arr = rs.GetRows
    n = UBound(arr, 2) + 1
    'Debug.Print (rs.RecordCount)
    Debug.Print n
    invested_funds = n

End Function

A RecordCount of -1 does not mean that your query did not return any records ( documentation ):
The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount.

[...]

The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.

The default value of CursorType is adOpenForwardOnly , so this behavior is expected. To get an actual count, use a CursorType of adOpenStatic or adOpenKeyset instead.