We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
COURSE of the MONTH
14 days, 13 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Store results in memory instead of each time writing results in sheet(cell).
Posted on 2012-12-27
Dear,
I'm including some accuracy control mechanism in a simulation Tool I build to re-rate made mobile calls.
The tool has a "control modus". If selected, the result (in this case the duration of the call ) is written in the current selected row offset by 18 colums.
When rerating +100K rows, and even with screenupdating and calc disabled, it runs very slow.
Is there a possibility to keep results in the memory (array), and only write the result once, once all rerating has been done.
Thank you for your help
/Xtophe
I'm including some accuracy control mechanism in a simulation Tool I build to re-rate made mobile calls.
The tool has a "control modus". If selected, the result (in this case the duration of the call ) is written in the current selected row offset by 18 colums.
When rerating +100K rows, and even with screenupdating and calc disabled, it runs very slow.
Is there a possibility to keep results in the memory (array), and only write the result once, once all rerating has been done.
Select Case ControlModus
Case Is = "YES", "Yes", "yes"
ActiveCell.Offset(0, 18).Value = CdrSeconds ' uncomment this in case you want to write control figures next to CDR's
Case Else
End Select
Thank you for your help
/Xtophe
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3 Comments
Well... putting values on spreadsheet is, virtually, maintaining values in an array (since these values are in memory). With +100k rows... and understanding how Excel maintains row data in memory... you're probably better off using the spreadsheet method versus memory arrays.
But, depending on how you use this data in your application... you may want to write your data to a sheet like a table (1st row contains headers, all other rows contain data) and access your data on the sheet by opening it like a table and query your data using SQL type statements.
Scott C
But, depending on how you use this data in your application... you may want to write your data to a sheet like a table (1st row contains headers, all other rows contain data) and access your data on the sheet by opening it like a table and query your data using SQL type statements.
Scott C
Active today
Writing 100k cells one by one will be very very slow, so yes you would be far better off writing the results to an array and then writing that to the sheet in one go using a simple:
syntax
Range("Y1").Resize(ubound(myarray, 1), ubound(myarray, 2)).Value = myArray
syntax
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
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 use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Suggested Courses
Course of the Month14 days, 13 hours left to enroll
770 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.