+ Reply to Thread
Results 1 to 3 of 3

How to create user defined function that would sound an alarm once conditions are met.

  1. #1
    Registered User
    Join Date
    01-19-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    2

    How to create user defined function that would sound an alarm once conditions are met.

    Hello,
    I have an excel spreadsheet with a streaming temperatures data in a multiple cells.
    I'm trying to create a user defined function that would sound an alarm when a temperature rises above specified value.This value is a parameter of this function.
    So far I have this code...

    Function BeepOnce(rng As Range, alarm_temperature As Single = 50)

    If (rng.Cells.Value >= alarm_temperature) Then
    BeepOnce = "Temperature is above specified vaule!"
    Beep
    Exit Function

    End If
    End Function



    The problem is that I need to sound an alarm only once for every cell. Only when temperature rise above this value.

    example:
    temperature 1 in a cell A1 is 50. Once the temperature in a cell A1 rises above 60 (for example to 65) an alarm would sound(only once). When the temperature then rises again to (for example to 70), there would be no alarm.

    temperature 2 in a cell A2 is 40 Once the temperature in a cell A2 rises above 50 (for example to 55) an alarm would sound(only once). When the temperature then rises again to (for example to 60), there would be no alarm.

    Once the temperature decrease below specified value, alarm then rest itself.
    .
    .
    .

    Thank you for your help.

    Sid

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: How to create user defined function that would sound an alarm once conditions are met.

    Interesting.

    I am not sure that the attached solution will work in practice.

    I have used two sheet Specific Macros.

    The problem is knowing if they will be triggered in your enviroment.

    You may have to move the macros to a normal macro and trigger the main macro using the OnTime event.

    Let me know how you get on.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    01-19-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    2

    Re: How to create user defined function that would sound an alarm once conditions are met.

    Thank you for your help. It works with some small flaws...
    In order to make it work every time I start this workbook I need to manually switch to sheet2 and then back to sheet1 to "activate it".
    The other related problem is: let's say in a cell A1 and A2 is a value 100(alarms has already sounded for both cells), but then when I switch to sheet2 and back to sheet1, then alarms would sound for both cells if the temperature rises to 105 (or fall to 95).
    I like this concept but would it be possible to somehow achieve this with a function?
    like BeepOnce(monitored cell, critical temperature)?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] User Defined Function for Summing a range based on multiple conditions
    By judeprem in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-29-2020, 03:39 AM
  2. [SOLVED] VBA - How to create a user defined function?
    By TheN in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-19-2016, 07:38 PM
  3. Alarm function with four different conditions
    By gdclyde in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-02-2015, 01:49 PM
  4. Help to create a user defined function with IF and AND
    By Elomaldo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-16-2014, 11:00 AM
  5. Replies: 8
    Last Post: 08-31-2013, 03:20 AM
  6. Use formula to create a User Defined Function
    By lindomsm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-29-2013, 06:54 AM
  7. How can I create a user defined function in excel?
    By Martinj in forum Excel General
    Replies: 4
    Last Post: 08-20-2005, 02:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1