Solved

# Improving speed by writing a table as an array

Posted on 2014-08-29
Medium Priority
102 Views
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.
0
Question by:Bright01
• 30
• 30

Author Comment

ID: 40293360
0

LVL 27

Expert Comment

ID: 40293409
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
``````

-Glenn
0

LVL 46

Expert Comment

ID: 40293503
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))
``````

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

LVL 46

Expert Comment

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

When the timer pops:
``````activesheet.calculate
``````
0

Author Comment

ID: 40293534
Glenn,

Yes.

Aikimark,

You lost me.  I tried the single line with the rnd statement and got a declaration error.
0

LVL 46

Expert Comment

ID: 40293550
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

Author Comment

ID: 40294369
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

LVL 46

Expert Comment

ID: 40294454
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")
``````
To this:
``````activesheet.range("YourNamedRangeName")
``````
substituting the actual name of the range for YourNamedRangeName
0

Author Comment

ID: 40294458
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

LVL 46

Expert Comment

ID: 40294470
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))
``````

## Example 2:

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

## Example 3:

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

Author Comment

ID: 40294486
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

LVL 46

Expert Comment

ID: 40294494
Let me play with your workbook.  Hopefully, I'll have a better understanding of what you need.
0

Author Comment

ID: 40294502
Thank you!

B
0

LVL 46

Expert Comment

ID: 40294742
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

Author Comment

ID: 40294763
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

LVL 46

Expert Comment

ID: 40294785
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
``````
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
``````
Please note that you should always include an Option Explicit statement in all your modules and forms.
0

Author Comment

ID: 40294890
OK.... I can use Application Screen updating....that will work for me.

How about the variable range capability?

Thanks,

B.
0

LVL 46

Expert Comment

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

The number of rows is determined by the frequency. (or seems to be)  To what "variable range capability" are you referring?
0

Author Comment

ID: 40294932
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

Author Comment

ID: 40294939
I added your array code and now get an error when I run the Random generator.

B.
0

LVL 46

Expert Comment

ID: 40294948
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
``````
0

Author Comment

ID: 40294960
Aikimark,

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

Thank you,

B.
0

Author Comment

ID: 40294963
OK.... I think I added it correctly, but now it doesn't update on the frequency.
D--temp-Realtime-v62.xlsm
0

Author Comment

ID: 40294965
Sorry..... it doesn't update on Interval Update (the Green Button starts the update value).

B.
0

LVL 46

Expert Comment

ID: 40294967
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
``````
0

Author Comment

ID: 40295431
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

LVL 46

Expert Comment

ID: 40295435
then make two different 1xN arrays and conditionally assign them to the two ranges.
0

Author Comment

ID: 40295443
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

LVL 46

Expert Comment

ID: 40295458
Don't give up.  Ask questions.

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

Author Comment

ID: 40295466
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

LVL 46

Expert Comment

ID: 40295475
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

Author Comment

ID: 40295506
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

LVL 46

Expert Comment

ID: 40295531
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

Author Comment

ID: 40295548
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

LVL 46

Expert Comment

ID: 40295564
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)
``````
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

Author Comment

ID: 40296070
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

LVL 46

Expert Comment

ID: 40296414
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

Author Comment

ID: 40296591
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

LVL 46

Expert Comment

ID: 40296621
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

Author Comment

ID: 40296661
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.

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

LVL 46

Expert Comment

ID: 40297260
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
``````
D--temp-Realtime-v5.xlsm
0

Author Comment

ID: 40297490
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

LVL 46

Expert Comment

ID: 40297613
I do not know how you intend to use this C13:D13 and down range data.  Why do you need a named range?
0

Author Comment

ID: 40298082
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

LVL 46

Expert Comment

ID: 40298091
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

Author Comment

ID: 40298888
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

LVL 46

Expert Comment

ID: 40298997
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"
``````
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
``````
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

LVL 46

Expert Comment

ID: 40299065
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
``````
0

Author Comment

ID: 40299198
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?

B.

I've attached my latest.

B.
D--temp-Realtime-v71.xlsm
0

LVL 46

Expert Comment

ID: 40299346
``````vUpdateFlags = rng.Offset(-2).Value
``````
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

LVL 46

Expert Comment

ID: 40301659
@B

You still here?
0

Author Comment

ID: 40301675
Sorry.... F11, G11.  That's where the yes/no's are.

B.
0

LVL 46

Expert Comment

ID: 40301721
Change line 45 to
``````vUpdateFlags = wks.range("F11:G11").Value
``````
0

Author Comment

ID: 40301771
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

LVL 46

Expert Comment

ID: 40301780
I was referring to the line numbers in the code snippet I posted earlier
0

Author Comment

ID: 40301802
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

LVL 46

Expert Comment

ID: 40301808
If you are putting the data in row one, why do you need to mess with the named range?
0

Author Comment

ID: 40301815
That line was one of the lines you instructed me to use (in the cleanup).

b.
0

LVL 46

Accepted Solution

aikimark earned 2000 total points
ID: 40301849
I think it was from a comment where I prefaced the code example with
If you insist on a changing named range
0

Author Closing Comment

ID: 40301950
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

LVL 46

Expert Comment

ID: 40301977
you're welcome
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custoâ€¦
###### Suggested Courses
Course of the Month14 days, 19 hours left to enroll

#### 839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.