Solved

Excel -- Macro to Save XLS # of times

Posted on 2013-12-12
3
259 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

746 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

12 Experts available now in Live!

Get 1:1 Help Now