Solved

macro in excel

Posted on 2016-09-01
3
29 Views
Last Modified: 2016-09-21
Hi
I m trying to compute simulate 500 lines, each line cosists of 260 random points( rand () ) So what I wanna do is generate a graph with 500 paths by using macro in Excel. Can you briefly show me how it works?
thats a graph with 100paths
Regards
Sijin
0
Comment
Question by:sijin wu
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
xtermie earned 400 total points (awarded by participants)
Comment Utility
You can use this macro to create the random numbers
Sub randomnums()
Dim nocols, norows As Integer
Dim ws As Worksheet

norows = 200    'you can change these
nocols = 500    'you can change these
Set ws = Application.ActiveSheet

Application.ScreenUpdating = False

For i = 1 To norows
    For j = 1 To nocols
        ws.Cells(i, j).Activate
        ws.Cells(i, j).Formula = "=Rand()"
    Next j
Next i

Application.ScreenUpdate = True
End Sub

Open in new window

In your Excel file you can have the graph set up to use the random range.
If you want we can write a separate macro (or append the above) to create the line graph
1
 
LVL 35

Assisted Solution

by:Kimputer
Kimputer earned 100 total points (awarded by participants)
Comment Utility
Above code obviously works, but please note the following:

- Excel usually only allows 255 lines, not the 500 you want
- Displaying random numbers, (graph/line/bars or otherwise) is usually of NO USE. IT will be ONE BIG BLOCK OF BLACK DOTS (if not, the random generator is defect). Or in Excel, it's grey, since all the colors fade into each other (lines visiable for around 15 lines, above 50, it's getting like a full block of grey).
0
 
LVL 17

Expert Comment

by:xtermie
Comment Utility
Good comments, code and recommendations provided
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now