VBA & Macro Problems

How to Calculate the Percentage of Time an Excel Cell Is TRUE

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants to calculate the percentage of time a live OPC value in a specific cell equals 1 (TRUE) over a 24-hour period by sampling the cell continuously.

Product: Excel
Device & OS: not provided
Scenario: Monitoring a live data feed (such as an OPC server) in a spreadsheet and analyzing the active state duration of a specific variable over time.
Observed behavior: Excel needs a mechanism to sample the cell automatically at regular intervals, track the occurrences of 1s and 0s, and output the final calculated percentage.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that macros are enabled in your application's Trust Center settings.

Solution 1Recommended

Use a Timed VBA Procedure to Sample the Target Cell

Create a VBA macro using Application.OnTime to sample the live cell at set intervals, tracking the results to calculate the percentage.

By using 'StartIt' and 'StopIt' procedures, you can control a continuous loop that evaluates the target cell. The macro increments counters based on whether the cell reads 1 or 0, and calculates the total percentage at the end of the measurement period.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications (VBA) Editor. Insert a new Module from the Insert menu.

Declare Public Variables

At the top of the module, declare public variables to hold the true count, false count, and the next scheduled run time for the timer.

Create the StartIt Procedure

Write a 'StartIt' sub that initializes your counters to zero and triggers the Application.OnTime method to schedule the sampling macro (e.g., to run every 15 seconds).

Write the Sampling Macro

Create the main sampling sub that evaluates cell E2 on your target worksheet. If the value is 1, increment the true counter; if 0, increment the false counter. Calculate the percentage as TrueCount / (TrueCount + FalseCount).

Output Results and Loop

Program the macro to write the calculated results to columns G and H. Include a call to Application.OnTime at the end of this sub to trigger itself again after the interval.

Create the StopIt Procedure

Write a 'StopIt' sub that uses Application.OnTime with the Schedule parameter set to False to cancel the timed loop after your 24-hour measurement period.

Worksheet and Output Requirements: Confirm that the worksheet is explicitly named 'Data' and that columns G and H are available for the script to record results. The procedure must run for the full measurement period before generating final outputs.

Advanced Data Tracking

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Office provides robust built-in support for VBA macros, allowing you to run automated timed procedures, sample live data, and perform complex calculations just like you would in Microsoft Excel.

1. Open WPS Spreadsheet: Launch WPS Spreadsheet and open your macro-enabled workbook containing the live data.
2. Access the Developer Tab: Navigate to the Developer tab on the top ribbon and click on 'VBA Editor'.
3. Insert and Modify the Script: Paste your 'StartIt', 'StopIt', and sampling procedures into a new module, adjusting the interval to match your required accuracy.
4. Run the Macro: Click 'Macros', select the 'StartIt' procedure, and click 'Run' to begin automatically sampling your live cell data.

Fully compatible with Microsoft Excel VBA macro scriptsLightweight software that runs automated background tasks quicklyEasily process live data feeds and custom formulasFree to download and use for professional data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why is the macro reporting 100% with no results in columns G or H?

This occurs if the script evaluates an empty or static state prematurely. Verify that the worksheet is named 'Data', columns G and H are free, and that the procedure has run for the full 24-hour interval before expecting the recorded result.

Can I change the sampling interval from 15 seconds?

Yes, you can adjust the TimeValue parameter in your Application.OnTime method to sample at any interval required for your specific accuracy needs (e.g., every 5 seconds or every minute).

Will the sampling continue if the spreadsheet is closed?

No, timed VBA macros require the spreadsheet application to remain open and active. Ensure your computer does not go to sleep and the file remains open for the full 24-hour period.

Excel & Data Problems