Improving speed by writing a table as an array

I have a great little WS that generates random data for testing purposes.  It uses a table that was built and the system, when the macro is fired, loops in order to drive the random numbers.

I need help writing the array and eliminating the loop so that performance can improve.

Thank you in advance.

B.
D--Data-Business-Programs-Archieve-Macro
Bright01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bright01Author Commented:
0
Glenn RayExcel VBA DeveloperCommented:
Are you talking about changing this section of code in the Generate_Samples subroutine?
    'apply random values
    For r = 0 To lngFreq - 1
        If LCase(Range("C11")) = "yes" Then
            Cells(r + 13, 3).Value = dtStart + (dblTime * r)
        End If
        If LCase(Range("D11")) = "yes" Then
            Cells(r + 13, 4).Value = WorksheetFunction.RandBetween(intMin, intMax)
        End If
    Next r
    If Not tTimer Then
        MsgBox ("Done.")
    Else
        'RunWhen = Now + TimeSerial(0, 0, Range("C7"))
        StartTimer Range("C7")
    End If

Open in new window


-Glenn
0
aikimarkCommented:
My remarks on Generate_Samples()
1. disable automatic calculations and set the formula =Randbetween(), using your min and max values, for all the cells.  This can be one statement.
Then do a manual calculation whenever the timer 'pops'

2. You will probably get better performance generating the pseudo random numbers if you use the intrinsic Rnd function, rather than the worksheetfunction object.
lngNewRandomNumber = intMin + Int(Rnd * (intMax-intMin+1))

Open in new window


3. If you need very random data, you can use the .Net framework's crypto library, the Windows Crypto32 API, or make a call to grab some truly random data.

quantum vacuum random data from ANU
https://qrng.anu.edu.au/API/api-demo.php

If you go that route, I can help you write your VBA code to make that GET call (MSXML2.XMLHTTP) and
parse and use the results.

=============
Read my article on reading/writing a block of cell values:
Fast Data Push to Excel:  http:A_2253.html

If you do use the intrinsic VB PRNG, please read my analysis:
An Examination of Visual Basic's Random Number Generation: http:A_11114.html
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

aikimarkCommented:
code example for #1 above:
intmin=5
intmax=25
application.calculation=xlCalculationManual
activesheet.range("$O$6:$T$11").formula="=randbetween(" & intMin & "," & intMax & ")"

Open in new window


When the timer pops:
activesheet.calculate

Open in new window

0
Bright01Author Commented:
Glenn,

Yes.

Aikimark,

You lost me.  I tried the single line with the rnd statement and got a declaration error.
0
aikimarkCommented:
I used my own variable names in my code example.  They represent an explanatory overview of what I'm describing.  You would still need to iterate through the rows and columns of your array, populating it with a new set of random numbers before assigning the array to your worksheet range.
0
Bright01Author Commented:
Aikimark,

So a different question on this.  The Random data that is generated produces a different number of rows each time a new timeframe is used (i.e. months/days, years/months).  Is there a way to name a range that will automatically adapt to changes in the number of rows?   I want to refer the date/time and changes to the numbers on another sheet and don't know how to do that without using a range name.  I tried naming the first 2 rows but it didn't expand when I changed the timeframe.

Thank you,

B.
0
aikimarkCommented:
In that case, you would use the name of the range.  So, you would change my earlier example from this:
activesheet.range("$O$6:$T$11")

Open in new window

To this:
activesheet.range("YourNamedRangeName")

Open in new window

substituting the actual name of the range for YourNamedRangeName
0
Bright01Author Commented:
Yes...but the problem is that when a different random dataset is choosen..... (e.g. changing months/days to years/months and the data rows change from 30 to 12) the range doesn't automatically adjust.

B.
0
aikimarkCommented:
I want to refer the date/time and changes to the numbers on another sheet and don't know how to do that without using a range name.
Maybe I didn't understand this.  Do you have named ranges or not?

If not, then you can adjust the size of the range (rows and columns) several ways.

Example 1:

If you know the upper left cell, then you can use offsets.
activesheet.range(activesheet.range("O6"),activesheet.range("O6").Offset(rowcount, colcount))

Open in new window


Example 2:

You can use the Cells addressing scheme.
activesheet.range(activesheet.Cells(15,6),activesheet.Cells(21,19))

Open in new window


Example 3:

You can use two different range variables
set rngUpperLeft = activesheet.range("O6") 
set rngLowerRight = activesheet.range("AA19") 
activesheet.range(rngUpperLeft, rngLowerRight)

Open in new window

0
Bright01Author Commented:
Aikimark,

Thanks for the tips.   However, I don't think I'm being clear here.

When you change the timing on the WS (Years, Days, Months, Hours, Min. Sec.), the cells in C and D populate.  I would like to use a range name because that's the only way I know how to have rows expand and contract with you insert them.  The macro does not insert rows, it performs a formula and paste into the right number of cells/rows.  That's the challenge.

B.
0
aikimarkCommented:
Let me play with your workbook.  Hopefully, I'll have a better understanding of what you need.
0
Bright01Author Commented:
Thank you!

B
0
aikimarkCommented:
Where are you trying to place the data?  It looks like you are only populating C13:C22.  I see a red textbox asking to assign values with an array that points to O6:T11, but I do not see any code that populates that area.
0
Bright01Author Commented:
I was originally looking to create an array to speed up the refresh on the random data that is created.... but after your comments and Glenn's comments, I changed the question to ask about being able to create a range name that would expand and contract with the data that gets both reset and refreshed in columns C and D. Once it's in a range, I can reference the data that is created regardless of its size (a.k.a. -- I can reference it on other worksheets.).

Make sense?

B.
0
aikimarkCommented:
The simplest solution is to suspend screen updating
    'apply random values
    Application.ScreenUpdating = False
    For r = 0 To lngFreq - 1
        If LCase(Range("C11")) = "yes" Then
            Cells(r + 13, 3).Value = dtStart + (dblTime * r)
        End If
        If LCase(Range("D11")) = "yes" Then
            Cells(r + 13, 4).Value = intMin + Int(Rnd * (intMax - intMin + 1))
        End If
    Next r
    Application.ScreenUpdating = True

Open in new window

Populating and pushing an array of data would look something like this:
Option Explicit


    Dim vData As Variant

    Application.ScreenUpdating = False
    ReDim vData(0 To lngFreq - 1, 1 To 2)
    'apply random values
    For r = 0 To lngFreq - 1
        If LCase(Range("C11")) = "yes" Then
            vData(r, 1) = dtStart + (dblTime * r)
        End If
        If LCase(Range("D11")) = "yes" Then
            vData(r, 2) = intMin + Int(Rnd * (intMax - intMin + 1))
        End If
    Next r
    With Worksheets("Realtime Data")
        .Range(.Range("C13:D13"), .Range("C13:D13").Offset(lngFreq)).Value = vData
    End With
    Application.ScreenUpdating = True

Open in new window

Please note that you should always include an Option Explicit statement in all your modules and forms.
0
Bright01Author Commented:
OK.... I can use Application Screen updating....that will work for me.

How about the variable range capability?

Thanks,

B.
0
aikimarkCommented:
slight correction to my earlier code:
    With Worksheets("Realtime Data")
        .Range(.Range("C13:D13"), .Range("C13:D13").Offset(lngFreq - 1)).Value = vData
    End With

Open in new window


The number of rows is determined by the frequency. (or seems to be)  To what "variable range capability" are you referring?
0
Bright01Author Commented:
So the number of rows is determined by C3, C4 and D3.  It's the Time Horizon, Frequency and Number of Time Horizon Units.  Think of it this way,  "you can put in a Time Horizon (C3) in any time increment and Number (D3); then you put in the Frequency, or "What is the actual increment you want to divide it by....".  That creates the # of rows.  So, if I put in Year, 1, and Month, it will create 12 rows of data.  If I put in Year, 2, and Month, it will create 24 rows of data.

You have to play with it for a sec. to get the hang of it.  

I was thinking that the range would automatically expand if it were named but given how rows are copied and pasted, the range naming wouldn't work.  

You could calculate the number of rows starting in C13:D13 that contain data by the same formula as used above;

Horizon X Frequency X # of Horizons = number of rows --- then give it a range name that can change each time any of the variables change....

Would that work?

B.
0
Bright01Author Commented:
I added your array code and now get an error when I run the Random generator.

B.
D--Data-Business-Programs-Archieve-Macro
0
aikimarkCommented:
1. D6 has a date component as well as a time component.

2. When you incorporated the new code, you commented your old code that set important variables, such as lngFreq, dtStart, intMin, and intMax.  Here is a section of code that I've corrected.
    Application.ScreenUpdating = False
    lngFreq = Round(Range("D4"), 0)
    dtStart = Range("D5") + Range("D6")
    intMin = Range("C10").Value
    intMax = Range("C9").Value
    ReDim vData(0 To lngFreq - 1, 1 To 2)
    'apply random values
    For r = 0 To lngFreq - 1
        If LCase(Range("C11")) = "yes" Then
            vData(r, 1) = dtStart + (dblTime * r)
        End If
        If LCase(Range("D11")) = "yes" Then
            vData(r, 2) = intMin + Int(Rnd * (intMax - intMin + 1))
        End If
    Next r
   With Worksheets("Realtime Data")
        .Range(.Range("C13:D13"), .Range("C13:D13").Offset(lngFreq - 1)).Value = vData
    End With

Open in new window

0
Bright01Author Commented:
Aikimark,

I'm still getting errors.  Can you put the code into Module 1 for me?

Thank you,

B.
0
Bright01Author Commented:
OK.... I think I added it correctly, but now it doesn't update on the frequency.
D--temp-Realtime-v62.xlsm
0
Bright01Author Commented:
Sorry..... it doesn't update on Interval Update (the Green Button starts the update value).

B.
0
aikimarkCommented:
You didn't include some code at the bottom as well:
Sub Generate_Samples()

    Dim lngFreq, r As Long
    Dim dtStart As Date
    Dim dblTime As Double
    Dim intMin, intMax As Integer
    
    Application.EnableEvents = False
    
    lngFreq = Round(Range("D4"), 0)
    Range("D6") = Range("D6") + TimeSerial(0, 0, Range("C7"))
    dtStart = Range("D5") + Range("D6")
    
    intMin = Range("C10").Value
    intMax = Range("C9").Value
    
    Select Case Range("C4").Value
        Case "Year(s)"
            dblTime = 365.04
        Case "Month(s)"
            dblTime = 30.42
        Case "Day(s)"
            dblTime = 1
        Case "Hour(s)"
            dblTime = 1 / 24
        Case "Min(s)"
            dblTime = 1 / 1440
        Case "Sec(s)"
            dblTime = 1 / 86400
    End Select
    
    'Clear existing data
    'Range("C13:D" & Cells.SpecialCells(xlLastCell).Row).ClearContents
    
    'apply random values
Application.ScreenUpdating = False
    lngFreq = Round(Range("D4"), 0)
    dtStart = Range("D5") + Range("D6")
    intMin = Range("C10").Value
    intMax = Range("C9").Value
    ReDim vData(0 To lngFreq - 1, 1 To 2)
    'apply random values
    For r = 0 To lngFreq - 1
        If LCase(Range("C11")) = "yes" Then
            vData(r, 1) = dtStart + (dblTime * r)
        End If
        If LCase(Range("D11")) = "yes" Then
            vData(r, 2) = intMin + Int(Rnd * (intMax - intMin + 1))
        End If
    Next r
   With Worksheets("Realtime Data")
        .Range(.Range("C13:D13"), .Range("C13:D13").Offset(lngFreq - 1)).Value = vData
    End With
        
    Application.EnableEvents = True
    If Not tTimer Then
        MsgBox ("Done.")
    Else
        'RunWhen = Now + TimeSerial(0, 0, Range("C7"))
        StartTimer Range("C7")
    End If
    
End Sub

Open in new window

0
Bright01Author Commented:
Aikimark,

Thanks for the fix..... it almost works.  In testing it, if you put "no" in column C 11 and run the random macro, it doesn't put the original data in the associated cells.  It leaves them blank.  If you do that with the original sheet I sent you (v6) you will see that C11 is an "UPDATE" decision in response to the START/STOP macro.  When you press RANDOM, it still populates. But if C11 is "no" then it  doesn't update.

Thanks,

B.
0
aikimarkCommented:
then make two different 1xN arrays and conditionally assign them to the two ranges.
0
Bright01Author Commented:
Aikimark,

"I give up".  If I knew how to create a range in the first place.... I wouldn't have asked the question or wasted your time.

You are clearly a very smart person.... but I cannot keep up with your recommendations simply because I don't have the same skill level as you.  I'm spending more time troubleshooting the snippets you send me then working on the business application of the WS.

I'm sorry.... I'm just out of my league when it comes to your comments.

B.
0
aikimarkCommented:
Don't give up.  Ask questions.

Do you know what this statement does?
ReDim vData(0 To lngFreq - 1, 1 To 2)

Open in new window

0
Bright01Author Commented:
I have no idea.  DIM means dimension I believe.  Lng = long?  Freq = frequency?

I'm good at assembling components; I have learned much through EE.  But you are beyond my capabilities and a man should know his limitations.  I can work on them, and I have over time..... but unless I get the help I need, I spend days on troubleshooting instead of advancing something that is actually usable.  

B.
0
aikimarkCommented:
Is this your code?  You posted a workbook that contained VBA code.  How were we supposed to know that you didn't understand it or author it?!?  What if someone posted code that deleted or encrypted files on your hard drive?

Learning about the language can be as simple as doing a web search or positioning the cursor over a word (in this case DIM) and pressing the F1 key.  I am forcing you to learn enough to carry on a conversation about the code you are executing.
0
Bright01Author Commented:
And I do appreciate that.  Again, I have learned much about assembling code.  But I believe you have to be, like in most languages, "immersed" in it to really learn it well.  The code you have is not mine but I do modify it from time to time; it was assembled by me with the assistance of two other versions of code written for particular functions by 3 others, that I assembled and tested into a Excel based app.   If my primary role at work was coding and technical (programming), I'd be responding to questions on EE instead of asking them.  So I'm a "closet" tech. guy because I enjoy it.  With that said, I have business deadlines and pressures that do not give me the flexibility to spend hours learning how to do this.  The best way I learn is by getting small snippets back, like you provided, and then either seeing how they work or having comments that provide clues to how they work.

Again, I appreciate you trying to teach me new things; but as I've said, I'm not at your level of technical talent.

B.
0
aikimarkCommented:
I acknowledge that you are not at my level.  I am attempting to mentor you.  If you do not do that, I can not help you to learn.  If you refuse to participate in this discussion, I will cease my participation in this question.  Learning is not an unpleasant way to spend your Sunday morning.

I asked you what the DIM statement did.  I expect something more than "DIM means dimension I believe."  Yes.  Dim stands for dimension.  What does the statement do?  You have many such statements in your code and I have added a few.
0
Bright01Author Commented:
Well, given you told me to use the F1 key over the text, I did that after your last post.  DIM means a declaration of a variable and the allocation of storage space.  

I have no problem with you mentoring me or anyone.  I think that is a very noble cause.  But I would say two things about mentorship, you must also know how someone learns and what they are trying to accomplish.  I'm not trying to get you to do my work for me.  I'm trying to assemble an application.   The best way for me to learn is through trying to trouble shoot and assemble.  I've spent countless hours doing this.  And I have learned much.  Again, I appreciate what you are doing to help me along........

Let's not confuse helping me out with the need for mentoring.  I'd like both, but I need more help at the moment.  The best EE people I have worked with provide me with help and enough comments to mentor me along.

Hopefully this also helps you learn how to deal with users who are not sophisticated and have requirements and the need for assistance.  

B.
0
aikimarkCommented:
Ok.  Now you know that all the variables are declared/defined by the Dim statement.  You have read that the variable name is followed by a data type.  If omitted the default data type is variant, which can hold any data type.

In my earlier question, I asked you what this particular statement did.
ReDim vData(0 To lngFreq - 1, 1 To 2)

Open in new window

Hint: ReDim is related to Dim.  If you understand what this form of Dim statement does, it will go a loooong way to help your understanding of what I'm talking about and the code that I've offered you so far.
0
Bright01Author Commented:
So, ReDim is a way to change the size of one or more dimensions of an array that has already been declared. So Dim means declare a variable and ReDim is a way to resize a declared dimension...?     So is vData a declared variable? and what is lngFreq?  It appears to be a declared variable and then referenced as "Dim lngFreq, r As Long" and "lngFreq = Round(Range("D4"), 0)"?

B.
0
aikimarkCommented:
Ok.  That's a start.
Quick question before we continue...Do you understand the concept of an array?  If so, please give me your definition in your own words.

=========================
Now I'm going to state some things.  Tell me if you understand these three sentences.

* vData is a 2D array, with lngFreq rows and 2 columns.

* vData is populated in the For...Next loop with random data.

* After population, the entire array's data is pushed into the range that starts at C13.
0
Bright01Author Commented:
My concept of an array is a 2 dimensional list of cells (typically numbers) that are cross referenced by their X and Y coordinates.  

Sentence 1.) yes.... but is IngFreq a dim name?  I would assume that it is a name of the number of rows that get created which is variable each time a timeframe is choosen.
Sentence 2.) OK..... I understand what it does because you explained what it does.  Not sure why or how.
Sentence 3.) I see what you mean by pushing (or publishing) it into the range.  But there is no range starting at C13.  It's a starting row, but we haven't declared a range or a range name right?

Aikimark,

Why did you delete my other question?  I'm really pushed for time.  I thought you and I were working on the Array aspect so I authored a different question about the range name.  It's ok that you did that... but again.... I'm pushed hard against a deadline here.  Is the work we are doing here both to add another column to the array as well as create a range name for variable data?

TY,

B.
0
aikimarkCommented:
You are correct in your understanding of an array.  It helps that you are working in an Excel environment, where you can easily visualize rows and columns.

lngFreq is a variable name.  Remember that the words immediately following "Dim" are the name of the variable.  What follows that name are attributes of the variable, such as its data type and dimensionality (if a 1D,2D,3D... array).  Yes. We are using the value in the lngFreq variable to specify the number of 'rows' in the vData array.

==============
Not sure why or how.
You assign values to variables and object properties (range values) with an equals sign.  The receiving variable name is on the left of the equals sign and some expression is on the right side.  When the expression has been evaluated, its value is assigned.  If variables are on the right side of the equals sign, their value does not change during the expression evaluation process.

In your question, you wanted to assign the value of an entire block of cells in one operation.  We are populating the array variable in order to accomplish this.

==============
C13 is the upper left corner of the range.  It is the address of a single cell, not a row.

==============
Since you may need to populate the left column separately from the right column, we may need to think about two separate ranges, starting with C13 and D13.

When we push the data from the vData array variable into the worksheet range, the size of the range is the same as the size of the 2D array (vData variable).  We may need two different 1D array variables.

It may be that we can still use the vData 2D array.  However, you will need to communicate the following:
Q1. What should happen to the values in column, at and below C13, when C12 is "yes" and when C12 is "no"?
Q2. What should happen to the values in column, at and below D13, when D12 is "yes" and when D12 is "no"?
Bear in mind that you must consider whether there is already data in these cells or if these cells are empty.

Q3. My use of the Frequency value to determine the number of rows may not be correct.  How should the number of rows be calculated?
0
Bright01Author Commented:
I'm ok making it part of this discussion.  They were actually two different questions -- the Array Question was to speed up the process and the range name was to build a range that was variable and could be referenced on another WS.  I should have originally authored two separate questions.  I thought it was not part of our conversation because we were talking about Arrays.  If I can get this done  with your coaching..... I'm good with that.

So to answer your questions above;

Q1. What should happen to the values in column, at and below C13, when C12 is "yes" and when C12 is "no"?

When "yes"; C13 and below are updated with the Interval update rate (C7) -- (e.g. 2 seconds).
When "no"; C13 does not get updated (but the original Random numbers remain displayed until cleared).

Whenever you put a no in the cell above the output (i.e. C12, or D12), it does not update the cells below.  It still publishes them the first time, but the frequency timing (the original loop) does not impact or update the data in that column.  In other words, when you press "Random" it generates the original numbers in both columns; when you press start, to begin an automatic update, if C12 or D12 have a "yes" then it gets the update.....otherwise, it stays in as the original random selection value until cleared.

 Q2. What should happen to the values in column, at and below D13, when D12 is "yes" and when D12 is "no"?
 Bear in mind that you must consider whether there is already data in these cells or if these cells are empty.

When "yes" it updates with the interval value -- for Col. C "Date/Time" it updates the increment in interval value; for Col. D, it's a completely new random number.   From a business perspective, it is simulating realtime data coming in from a sensor device (random) while maintaining the date/time.

Make sense?

B.
0
aikimarkCommented:
The simplest approach was to get a snapshot of the range before the loop.  I can then conditionally update the vData array with new data or from the (snapshot) array.
Sub Generate_Samples_Q_28508262()
    Dim lngFreq, r As Long
    Dim dtStart As Date
    Dim dblTime As Double
    Dim intMin, intMax As Integer
    Dim vData
    Dim vCurrentData
    
    Application.EnableEvents = False
    
    lngFreq = Round(Range("D4"), 0)
    Range("D6") = Range("D6") + TimeSerial(0, 0, Range("C7"))
    dtStart = Range("D5") + Range("D6")
    
    intMin = Range("C10").Value
    intMax = Range("C9").Value
    
    Select Case Range("C4").Value
        Case "Year(s)"
            dblTime = 365.04
        Case "Month(s)"
            dblTime = 30.42
        Case "Day(s)"
            dblTime = 1
        Case "Hour(s)"
            dblTime = 1 / 24
        Case "Min(s)"
            dblTime = 1 / 1440
        Case "Sec(s)"
            dblTime = 1 / 86400
    End Select
    
    'Clear existing data
    'Range("C13:D" & Cells.SpecialCells(xlLastCell).Row).ClearContents
    
    Application.ScreenUpdating = False
    ReDim vData(1 To lngFreq, 1 To 2)
    With Worksheets("Realtime Data")
        vCurrentData = .Range(.Range("C13:D13"), .Range("C13:D13").Offset(lngFreq - 1)).Value
    End With
    'apply random values
    For r = 1 To lngFreq
        If LCase(Range("C11")) = "yes" Then
            vData(r, 1) = dtStart + (dblTime * r)
        Else
            vData(r, 1) = vCurrentData(r, 1)
        End If
        If LCase(Range("D11")) = "yes" Then
            vData(r, 2) = intMin + Int(Rnd * (intMax - intMin + 1))
        Else
            vData(r, 2) = vCurrentData(r, 2)
        End If
    Next r
    With Worksheets("Realtime Data")
        .Range(.Range("C13:D13"), .Range("C13:D13").Offset(lngFreq - 1)).Value = vData
    End With
    Application.ScreenUpdating = True
    If Not tTimer Then
        MsgBox ("Done.")
    Else
        'RunWhen = Now + TimeSerial(0, 0, Range("C7"))
        StartTimer Range("C7")
    End If
    
    Application.EnableEvents = True
End Sub

Open in new window

D--temp-Realtime-v5.xlsm
0
Bright01Author Commented:
Wow....that's good!  So, my table just went from static to embedded in the code as to the values (i.e. month, day, year, etc.) and none of that is now relevant.  In otherwords, in the original code I had converted a Month/Day combination into a sample size of 30 for 1 month, 60 for 2 months, etc.  It seems that now I simply put in the number of samples I want and it gives me that number of samples.  I think I can live with that.  But then, the table; now array..... do I need to leave that in place?  It changes randomly as the intervals change the numbers.  I can leave it if required.  I may play with it.

In reading through the code, I do not see where a variable range is created that is "named" so as to be used elsewhere in other WSs.  Is that a part that I have missed somewhere?  

Very good!  And I've learned new things about DIM and ReDIM.  And it appears that you "push" the data into the columns....but we still don't name the range so it can be used elsewhere.  Think of it like this.  If you change the Frequency in D4, it should reestablish the range (We can use the same range name)....that way it can be referenced and refreshed on other sheets right?

You are a very good teacher!

B.
0
aikimarkCommented:
I do not know how you intend to use this C13:D13 and down range data.  Why do you need a named range?
0
Bright01Author Commented:
Yes.  A named range that auto expands and contracts based on the Frequency specified in D4.  That was the question I had authored that you deleted.

Thank you,

B.
0
aikimarkCommented:
Let me clarify...
HOW do you need to use this C13:D13 (and below) range?  (in a formula? in VBA code? in an external reference? in a database call?)

I'm not convinced that you need a named range.
0
Bright01Author Commented:
So I will eventually be using the data that is produced from the array to feed into a statistical package that can read data from Excel for statistical analysis.  I would like to do that by sourcing the data from another WS.  By having a range that I can refer to, to display on another WS, I can combine the realtime simulation with some fixed static information (in other columns) and then import the the dataset into the analysis.

Make sense?

B.
0
aikimarkCommented:
The best way to do this is to change the layout of your worksheet.  Place the (Time, Reading) column headings for these two columns on row 1.  You can move the other cells off to the right and below row 1.

This way, other programs only need to refer to the sheet and their drivers should correctly pick up your two columns of data.

==============
If you insist on a changing named range, then you can update the RefersTo property of the named range at the bottom of the routine.

Example that assumes you have a named range that has the name "MyData".  The first statement gets to the named range through the application object and the second statement gets to the named range through the activeworkbook object.
Application.Names("MyData").RefersTo   = "=$C$13:$D$19"
ActiveWorkbook.Names("MyData").RefersTo   = "=$C$13:$D$21"

Open in new window

A bit more useful example uses a range object to get the proper RefersTo string value
Dim rng as Range


set rng = Worksheets("Realtime Data").range(Worksheets("Realtime Data").range("C13:D13"),Worksheets("Realtime Data").range("C13:D13").end(xldown))
Application.Names("MyData").RefersTo   = "=" & rng.Address
ActiveWorkbook.Names("MyData").RefersTo   =  "=" & rng.Address

Open in new window

Note: you might also use Activesheet in place of Worksheets("Realtime Data"), since the code is running in a workbook with only one worksheet.
0
aikimarkCommented:
Tidying the routine up a bit and removing range references inside the For...Next loop:
Sub Generate_Samples_Q_28508262()
    Dim lngFreq, r As Long
    Dim dtStart As Date
    Dim dblTime As Double
    Dim intMin, intMax As Integer
    Dim vData As Variant
    Dim vCurrentData
    Dim rng As Range
    Dim wks As Worksheet
    Dim vUpdateFlags As Variant
    
    Application.EnableEvents = False
    
    lngFreq = Round(Range("D4"), 0)
    Range("D6") = Range("D6") + TimeSerial(0, 0, Range("C7"))
    dtStart = Range("D5") + Range("D6")
    
    intMin = Range("C10").Value
    intMax = Range("C9").Value
    
    Select Case Range("C4").Value
        Case "Year(s)"
            dblTime = 365.04
        Case "Month(s)"
            dblTime = 30.42
        Case "Day(s)"
            dblTime = 1
        Case "Hour(s)"
            dblTime = 1 / 24
        Case "Min(s)"
            dblTime = 1 / 1440
        Case "Sec(s)"
            dblTime = 1 / 86400
    End Select
    
    'Clear existing data
    'Range("C13:D" & Cells.SpecialCells(xlLastCell).Row).ClearContents
    
    Set wks = Worksheets("Realtime Data")
    Set rng = wks.Range("C13:D13")
    
    Application.ScreenUpdating = False
    ReDim vData(1 To lngFreq, 1 To 2)
    vCurrentData = wks.Range(rng, rng.Offset(lngFreq - 1)).Value
    vUpdateFlags = rng.Offset(-2).Value
    vUpdateFlags(1, 1) = LCase(vUpdateFlags(1, 1))
    vUpdateFlags(1, 2) = LCase(vUpdateFlags(1, 2))
    'apply random values
    For r = 1 To lngFreq
        If vUpdateFlags(1, 1) = "yes" Then
            vData(r, 1) = dtStart + (dblTime * r)
        Else
            vData(r, 1) = vCurrentData(r, 1)
        End If
        If vUpdateFlags(1, 2) = "yes" Then
            vData(r, 2) = intMin + Int(Rnd * (intMax - intMin + 1))
        Else
            vData(r, 2) = vCurrentData(r, 2)
        End If
    Next r
    wks.Range(rng, rng.Offset(lngFreq - 1)).Value = vData
    
    Set rng = wks.Range(rng, rng.End(xlDown))
    ActiveWorkbook.Names("MyData").RefersTo = "=" & rng.Address
    'or wks.Parent.Names("MyData").RefersTo = "=" & rng.Address
    
    Application.ScreenUpdating = True
    If Not tTimer Then
        MsgBox ("Done.")
    Else
        'RunWhen = Now + TimeSerial(0, 0, Range("C7"))
        StartTimer Range("C7")
    End If
    
    Application.EnableEvents = True
End Sub

Open in new window

0
Bright01Author Commented:
Aikimark,

I moved the headings to Row 1 in C and D and moved the other elements to the right below Row 1 (E).  You didn't tell me I had to change all the reference points in the code....but given your mentoring approach.... I figured it out and made the changes.  You'll be happy to know.... IT WORKED!  Then I placed your "tidying up" code in and got a debug error;

vUpdateFlags = rng.Offset(-2).Value

I see you added this Dim and think it might have something to do with the range name I haven't declared or established.

I went back to your first instruction about;

Application.Names("MyData").RefersTo   = "=$C$13:$D$19"
ActiveWorkbook.Names("MyData").RefersTo   = "=$C$13:$D$21"

And I have not put this in.  For two reasons;  1.) I don't know where to put it in and secondly since I moved the C13 and D13 rows up, are the RefersTo correct?

Please advise... Learning fast.

B.

I've attached my latest.

B.
D--temp-Realtime-v71.xlsm
0
aikimarkCommented:
vUpdateFlags = rng.Offset(-2).Value

Open in new window

the range offset was for the original configuration of the cells.  This is where the yes/no values were placed to indicate to the code whether or not to update the C13:D13 and below cells.  They were originally in C11:D11

Where are those yes/no cells now?
0
aikimarkCommented:
@B

You still here?
0
Bright01Author Commented:
Sorry.... F11, G11.  That's where the yes/no's are.

B.
0
aikimarkCommented:
Change line 45 to
vUpdateFlags = wks.range("F11:G11").Value

Open in new window

0
Bright01Author Commented:
Two questions;  How do I turn on line numbering?   And I changed out the vUpdateFlags line and got a different compile error.

Thank you,

B.
0
aikimarkCommented:
I was referring to the line numbers in the code snippet I posted earlier
0
Bright01Author Commented:
Aikimark,

I made the change and now get an error on;  ActiveWorkbook.Names("MyData").RefersTo = "=" & rng.Address

Can you check out the WS?  Press Random and you'll see the error.

Thank you,

B.
D--temp-Realtime-v72.xlsm
0
aikimarkCommented:
If you are putting the data in row one, why do you need to mess with the named range?
0
Bright01Author Commented:
That line was one of the lines you instructed me to use (in the cleanup).

b.
0
aikimarkCommented:
I think it was from a comment where I prefaced the code example with
If you insist on a changing named range
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Aikimark,

Thank you for all the help and mentoring.  You advanced my understanding of Excel Macro coding and I really appreciate that.  It was a long process...but a very good learning experience.

Again, "thank you"!

B.
0
aikimarkCommented:
you're welcome
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.