0

As I am working with large csv files, I decided to load them into VBA memory instead of loading in my spreadsheet to make it quicker and lighter.

So I have a function CSVtoArray that read through my CSV and gives me an array.

Then if I still want to see my data in excel I just write {=(CSVtoArray(my_csv_path)} in my s/s.

But since the size of my csv changes over time, I wanted to write a function called AutoRange that would automatically fit the display area in my spreadsheet according to the size of my range.

So this is what I wrote but it's not working, it does nothing, only the cell in which I am writing the formula is filled. 

    Function AutoRange(my_array As Variant)

        Dim nb_rows, nb_cols As Integer
        Dim current_cell, target_range As Range

        nb_rows = UBound(my_array, 1)
        nb_cols = UBound(my_array, 2)

        Set current_cell = Selection

        current_cell.Resize(nb_rows, nb_cols).FormulaArray = current_cell.Formula

        AutoRange = Selection

    End Function

Thanks in advance guys.

DummyDumDum
  • 1
  • I think this is a double post https://stackoverflow.com/questions/12259595/load-csv-file-into-a-vba-array-rather-than-excel-sheet – XsiSecOfficial May 21 '18 at 08:25
  • hum no, actually this is the step after that. I have read the CSV already, it's stored in my memory, now I want to display it in the spreadsheet. – DummyDumDum May 21 '18 at 08:33

2 Answers2

0

Functions are for returning things. And if used in the cell are for returning things to that cell, not to manipulate other cells. Do you want actually want a sub like?

Code:

Option Explicit
Public Sub TEST()

    Dim my_Array()
    my_Array = [A1].CurrentRegion.Value

    AutoRange my_Array

End Sub

Public Sub AutoRange(ByVal my_Array As Variant)

    Dim nb_rows As Long, nb_cols As Long
    Dim current_cell As Range

    nb_rows = UBound(my_Array, 1)
    nb_cols = UBound(my_Array, 2)

    Set current_cell = Selection

    current_cell.Resize(nb_rows, nb_cols).FormulaArray = current_cell.Formula

End Sub

Result:

Result

From your comments: If you want to use as a function (Not a UDF, which cannot alter other cells) then you can use the following way, though I advise against it as it bad practice:

Option Explicit
Public Sub TEST()

    Dim my_Array()
    my_Array = [A1].CurrentRegion.Value
    Dim target_Range As Range
    Set target_Range = AutoRange(my_Array)

End Sub

Public Function AutoRange(ByVal my_Array As Variant) As Range

    Dim nb_rows, nb_cols As Long
    Dim current_cell, target_Range As Range

    nb_rows = UBound(my_Array, 1)
    nb_cols = UBound(my_Array, 2)

    Set current_cell = Selection
    Set target_Range = current_cell.Resize(nb_rows, nb_cols)
    Set AutoRange = target_Range

    target_Range.FormulaArray = current_cell.Formula

End Function
QHarr
  • 83,427
  • 12
  • 54
  • 101
  • yeah that was my idea about the error on the return. The sub could do but I want it as a function like "=AutoRange(CSVtoArray(my_csv_path))" that I would enter in a cell and that would extend automatically. I know it can be done because in my former job the IT team built one but I never paid attention to look into the code ..... – DummyDumDum May 21 '18 at 09:21
  • So maybe implement as above. – QHarr May 21 '18 at 09:22
  • How are you calling this function? You can't use it in a cell. – QHarr May 21 '18 at 10:09
  • Ok so I think I got my answer, from here [link](https://www.ozgrid.com/forum/forum/help-forums/excel-general/41643-can-a-subroutine-be-called-within-a-function). "A function called from a worksheet cell can only affect the content/appearance of the cell it was called from. " So I guess I need to figure another way to do it. – DummyDumDum May 23 '18 at 02:22
  • I already told you this twice in my answer above. So that answer is actually my answer.See above ① "used in the cell are for returning things to that cell, not to manipulate other cells" ; ② "Not a UDF, which cannot alter other cells". I then go on to show you how you can get around it. Lol. – QHarr May 23 '18 at 05:46
  • [This](https://support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel) would be a better reference to read on the subject. – QHarr May 23 '18 at 05:50
0

Ok so I did it another way,

I have my AutoRange sub :

    Sub AutoRange(my_Array As Variant, top_left_corner As Range)
        ' Here we take an array in input, the one we want to display, and the top left corner of the range where we want to put it

    Dim nb_rows, nb_cols As Integer

    nb_rows = UBound(my_Array, 1)
    nb_cols = UBound(my_Array, 2)

    Set current_cell = top_left_corner

    top_left_corner.Resize(nb_rows, nb_cols).FormulaArray = top_left_corner.Formula

    End Sub

and then I added a Worksheet_change sub to my s/s: 

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$A$1" Then
            if Target.value="load data" then
                Call Autorange(my_array, my_range)
            else
                Range(my_range, my_range.End(xlDown).End(xlToRight)).clearcontents
        End If
    End Sub

so I just need to say if I want to load my data or not and it will adjust.

I assume that at my former company they were using an addin rather than VBA itself.

thanks anyways guys. cheers

DummyDumDum
  • 1