Solved

Excel -- Macro to Save XLS # of times

Posted on 2013-12-12
3
260 Views
Last Modified: 2013-12-12
Experts:

In Excel, I've have a graph with a data series linked to a RANDBETWEEN function (cell range).

As designed, each time I click, e.g., the "Save" icon, the random function regenerates its value set and therefore the graph is updated.    This works great.

I now would like to add the following elements to the spreadsheet.
1. Have a cell (e.g., A1) that contains a drop-down box (values of, e.g., 10, 20, 30, etc.)
2. Add a command button that calls a macro
3. Macro's purpose is to save the Excel spreadsheet n time of times (as specified in the drop-down menu).  So, if cell value in A1=10, then clicking on the macro will save/re-save the XLS 10 times.    Therefore, the linked graph gives the appearance of being animated.

The execution of the save should be driven by a time factor.   For example, based on another cell value, I want the save/re-save to occur every second or every two seconds.   (Otherwise, if the Excel would execute in mili-seconds, it probably would defeat the purpose.)

I'm flexible to other suggestions... as long as the envisioned process allows to specify, e.g, the refresh of the random function to occur every # of seconds.

Any recommendations how to achieve this?

EEH
0
Comment
Question by:ExpExchHelp
  • 2
3 Comments
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39714244
You can just call recalculate instead of save. Probably a bit healthier for your computer.

Recalculate can be called manually by hitting:
F9

Open in new window


The VBA call is:
Application.Calculate

Open in new window

0
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 500 total points
ID: 39714260
This seems to run fairly well :)
Sub MyMacro()
    Dim i, loops As Integer
    loops = ActiveSheet.Range("A1")
    
    Dim t As Date
    t = Now()
    
    For i = 0 To loops
        t = DateAdd("s", 1, t)
        Application.Wait (t)
        Application.Calculate
    Next
End Sub

Open in new window


For bonus points, set the number of seconds to pause in B1 :)
Sub MyMacro()
    Dim i, loops, interval As Integer
    loops = ActiveSheet.Range("A1")
    interval = ActiveSheet.Range("B1")
    
    Dim t As Date
    t = Now()
    
    For i = 0 To loops
        t = DateAdd("s", interval, t)
        Application.Wait (t)
        Application.Calculate
    Next
End Sub

Open in new window

0
 

Author Closing Comment

by:ExpExchHelp
ID: 39714355
That worked for me!   Thanks.

EEH
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

863 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

28 Experts available now in Live!

Get 1:1 Help Now