VBA Autorange function for Array to Range in excel

Question

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.

I think this is a double post stackoverflow.com/questions/12259595/… — XsiSecOfficial
– XsiSecOfficial, Commented May 21, 2018 at 8: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
– DummyDumDum, Commented May 21, 2018 at 8:33

QHarr · Accepted Answer · 2018-05-21 10:15:15Z

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:

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

edited May 21, 2018 at 10:15

answered May 21, 2018 at 8:41

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

DummyDumDum Over a year ago

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 .....

QHarr Over a year ago

So maybe implement as above.

QHarr Over a year ago

How are you calling this function? You can't use it in a cell.

DummyDumDum Over a year ago

Ok so I think I got my answer, from here link. "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.

QHarr Over a year ago

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.

|

DummyDumDum · Accepted Answer · 2018-05-23 04:11:31Z

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

Collectives™ on Stack Overflow

VBA Autorange function for Array to Range in excel

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related