VBA/Macro for saving a range into an array (variant) and use this for defining data for a graph

[mwahlgreen]

Hey forum

I am trying to do many things in my program for analysing a dataset. However I have run into some kind of wall. I will need help from you guys.

I started of making a lot of code where I have hard coded some ranges into the program.
These ranges tells the program where it should find data, labels, title, and stuff from my test dataset.
This part runs as a charm and produces eight fine looking graphs in a new sheet if I load my test data into the first sheet.
(Runs as one macro)

In another macro, I now want to write code that can input these ranges by asking for how many datasets are in a file
When it works I will implement it into the first marco.

First question for the user is "Write how many dataset there are", which via an Application.InputBox returns a number (integer).
So fare so good. Some of the error handling is not working as I would like to (I do not seem to be able to handle the Cancel button)
I have implemented this code, however this is seems to always go into the Else-statement for some unknown reason (the StrPtr(varNum) returns something else than 0 if Cancel is pushed.)

Dim varNum As Variant
'...
varNum = Application.InputBox("Write how many dataset there are (max 15)", "Obtain the number of datasets in this spreadsheet", "1", , , , , 1)

If StrPtr(varNum) = 0 Then
        'StatementsIfCancel
    ElseIf varNum = "" Then
        'StatementsIfNoInput
    'Else
        'StatementsIfInputAndOK
End If

I can live with the issue of not handling the "errors", and this is not my main issue.

Now, when I have the number of dataset in my file, I would like to run a For loop for each dataset where I let the user select the range of rows in which the data is saved in different columns:

Dim intNum as Integer
Dim rngDataset(15) as Variant
'...
For i = 1 To intNum        
        rngDataset(i) = Application.InputBox("Select a range (no. " & i & ")", "Obtain Range Object", , , , , , 8)
        'Same error handling as above here, still not working
Next i

The output of the above is a variant array, which I fail to translate back to the ranges I need to continue forward.
What I need is the start cell for each of the input ranges and the height of each input ranges, then I can reconstruct the ranges to input into my script for the graphs.
This can be done by

rangeObject.Row
rangeObject.Row.Count

However I do not have a range object, but a variant

If I try to read the variant (rngDataset(1)) into something else (a rangeObject):

Dim rng As Range'...
        Set rng = rngDataset(1)

or simply

Dim rng As Range
'...
        rng = rngDataset(1)

I get various errors simply because I do not understand what I am doing to change the information in the variant array back to something I can use properly.

I hope someone can help me a that I make myself clear

Another path
Now the above is one approach. Another approach could be as the following.

Pseudocode

For.....
        Extract Range using Inputbox (E.g. i=1 A2:A11 i=2 A12:A21)
        Extract first row of that range and store it one array (string)
        Extract Row.count of that range and store it another array (string)
Next i
... Do something else ...
'Reconstruct the ranges with data
For i
i=1
        Look for labels in B2:B11
        Look for data for Series1 in CV2:CV11
        Look for data for Series2 in CW2:CW11
        Look for data for Series3 in CX2:CX11
        Look for data for Series4 in CY2:CY11
i=2
        Look for labels in B12:B21
        Look for data for Series1 in CV12:CV21
        Look for data for Series2 in CW12:CW21
        Look for data for Series3 in CX12:CX21
        Look for data for Series4 in CY12:CY21
Next i

I will continue on the second option for now, however I would really like to know if the first option is possible to solve and if I was taking my program in the right direction. I spend at least day on it and I am sad to just let it go without knowing what went wrong.

If someone want to see my full code I can post both this and a data file for testing, without letting any cats out of the bag according to my firms knowledge or similar. Just ask.

 

[dmt32]

So fare so good. Some of the error handling is not working as I would like to (I do not seem to be able to handle the Cancel button)
I have implemented this code, however this is seems to always go into the Else-statement for some unknown reason (the StrPtr(varNum) returns something else than 0 if Cancel is pushed.)

Hi,
to answer one of you questions, StrPtr only works with the InputBox Function (standard inputbox).

Using the Application.InputBox method cancel button returns False.

Dim varNum As Variant
'...
varNum = Application.InputBox("Write how many dataset there are (max 15)", "Obtain the number of datasets in this spreadsheet", "1", , , , , 1)


If varNum = False Then
        'StatementsIfCancel
    ElseIf varNum = "" Then
        'StatementsIfNoInput
    Else
        'StatementsIfInputAndOK
End If

To handle different data types, varNum can be declared as Variant - you can then if required, coerce to correct data type using one of the type conversion functions.

Dave

 

[mwahlgreen]

Thank you very much Dave
My secondary issue has now been solved.

I will dig into the type conversion functions to see if they will help me solve my primary issue along the way.

 

[mwahlgreen]

I followed my own pseudocode example and build the following code from an example here

For i = 1 To intNum
   colRange.Add Application.InputBox("Select a range (no. " & i & ")", "Obtain Range Object", , , , , , 8)
   If TypeOf colRange(1) Is Range Then Set rngOutput = colRange(1)
   Set colRange = New Collection
    
   If rngOutput Is Nothing Then
      Debug.Print "The inputbox has been canceled"
      Exit Sub
   End If
     
   strRangeHeight(i) = rngOutput.Height
   strRangeFirstRow(i) = rngOutput.Row
   strRangeFirstCol(i) = rngOutput.Column
Next i

And now I have to reconstruct the ranges for my project, so if an user select range A2:A10 it will get data from other columns in the same height (9) and starting at the same row (A2).

I will try to remember to post an example of that when it is working.