Solved

Combining the IF and Range functions

Posted on 2008-06-19
14
1,757 Views
Last Modified: 2011-10-19
Following on from a previous question #23367661.  Two parts to this question:
1) I would like to use the If and Range functions with mulitiple ranges to return differet results.  E.g: In excell if I write:
=if(a1:c1>d1,a2:c2,0)
It will return the value in row 2 of the column that is >d1 in row 1.  I.e. if b1 is >d1, then you get b2.
I tried to do this in VB and it didn't work.  What do i need to do?
2) The second step is to also assign a wav file to each condition.  E.g. if b1 is true i get chimes.wav, if c1 is true i get windows.wav etc....
So you get a different combinations of a sound and a message box for each of the 3 cells in the range a1:c1.
Code attached.
Thanks!

Option Explicit

Private Declare Function PlaySound Lib "winmm.dll" _

  Alias "PlaySoundA" (ByVal lpszName As String, _

  ByVal hModule As Long, ByVal dwFlags As Long) As Long

 

Const SND_SYNC = &H0

Const SND_ASYNC = &H1

Const SND_FILENAME = &H20000

Public StopCode As Boolean

 

Public Sub PlayFile(wFile As String)

    Call PlaySound(wFile, 0&, SND_ASYNC Or SND_FILENAME)

End Sub

Sub test()

If StopCode = True Then Exit Sub
 

'If A1 has the time and A2 has the trade:
 

Application.OnTime Now() + TimeValue("00:00:05"), "Test"

If Range("A1:C1").Value > (Now() - TimeValue("00:00:10")) Then

    AppActivate ("Microsoft Excel non-commercial use - alert")

    PlayFile "c:\windows\media\chimes.wav"

    'change the path between the quotes to the path of the wav file to play

    MsgBox Range("A2:C2").Value

End If

End Sub

Open in new window

0
Comment
Question by:lachstock
  • 7
  • 7
14 Comments
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 21826435
Excel does some work in the background to give you the kind of output you get. Unfortunately, this is not true within a limited development environment. You will individually need to check IF conditions for each cell...
TimeVal = Now() - TimeValue("00:00:10")
If Range("A1").Value > (TimeVal) Then
..
..
End If

If Range("B1").Value > (TimeVal) Then
..
..
End If

If Range("C1").Value > (TimeVal) Then
..
..
End If

IF there are more than 3 and it is not possible to individually write an If condition for each, you may want to use the offset function. In VB you'd do something like this...

Dim Cnt As Integer
For Cnt = 0 To 10
    If Sheet1.Range("A1").Offset(Cnt, 0).Value > TimeVal Then
        MsgBox Sheet1.Range("A1").Offset(Cnt, 1).Value
    End If
Next Cnt

...hope you can recode that in C.

For different sounds for different cells, you may want to have an Array of desired sound filenames and then play them accordingly...
E.g. PlayFile CellSounds(Cnt)

Hope that helps...
0
 

Author Comment

by:lachstock
ID: 21827251
Ok thanks.  1st part works ok.  Presume that works for the first 10 columns?  Had to switch the offset around to (0,Cnt) though.  
As for the second part.  How do i set up the array?  I.e. how do i relate Cnt to 10 different sound files?
Thanks again.
0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 21827500
Oops my bad, yes the column input should have come second... Now, there are two approaches to building up an array list, but the concept for using them remains the same. The variable 'Cnt' holds the column index/number. So if Cnt = 0, then it's the first column, if Cnt = 7, then it's the eighth column, an so on..

So after this line...
        MsgBox Sheet1.Range("A1").Offset(1, Cnt).Value
you can say...
        PlayFile SoundList(Cnt).Value

The SoundList string array will contain the file path to the sounds. So...
SoundList(0) may equal "C:\Music\soundCol1.wav"
SoundList(1) may equal "C:\Music\soundCol2.wav"
...etc.

To populate the SoundList variable...

Approach 1:
In a Text file (.txt), enter the full path of the sounds appropriately in sequence of the columns.
Then...

Private SoundList() As String
Dim FileData As String

Open "C:\MyApp\SoundList.txt" For Binary As #1
FileData = String$(LOF(1), Chr$(0))
Get #1, , FileData
Close #1
SoundList = Split(FileData, vbCrLf)

This approach may be useful when you want to do things on the fly and you may not always know the number of columns. In order to get the last column number, you can use this statement...
LastCol = Sheet1.Range("ZZ1").End(xlToLeft).Column


ELSE - Approach #2

You can simply declare the filenames at design time

SoundList(10) As String

Private Sub Workbook_Open()
SoundList(0) = "C:\Music\file1.wav"
SoundList(1) = "C:\Music\file2.wav"
SoundList(2) = "C:\Music\file3.wav"
SoundList(3) = "C:\Music\file4.wav"
.
.
.
End Sub
0
 

Author Comment

by:lachstock
ID: 21827619
OK, trying to do option one, but getting compile error: Invalid outside procedure on the line:
Open "C:\Soundlist.txt" For Binary As #1  
Have attached code and txt file.  Txt file was created in notepad and is saved in C:\.
THanks,
Option Explicit

Private SoundList() As String

    Dim FileData As String

    Open "C:\Soundlist.txt" For Binary As #1

    FileData = String$(LOF(1), Chr$(0))

    Get #1, , FileData

    Close #1

    SoundList = Split(FileData, vbCrLf)

Public StopCode As Boolean
 

Sub test()

If StopCode = True Then Exit Sub
 

Application.OnTime Now() + TimeValue("00:00:05"), "Test"

Dim Contract As Integer

For Cnt = 0 To 10

If Sheet1.Range("A1").Offset(0, Contract).Value > (Now() - TimeValue("00:00:10")) Then

    AppActivate ("Microsoft Excel non-commercial use - alert")

    MsgBox Sheet1.Range("A2").Offset(0, Contract).Value

    PlayFile SoundList(Contract).Value

    End If

    Next Cnt

End Sub

Open in new window

Soundlist.txt
0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 21827664
The code that I gave you were statements that should have gone before you main sub-routine.

The lines...
    Dim FileData As String
    Open "C:\Soundlist.txt" For Binary As #1
    FileData = String$(LOF(1), Chr$(0))
    Get #1, , FileData
    Close #1
    SoundList = Split(FileData, vbCrLf)

...should probably have come after "Sub test()"... i.e. within the sub routine.

The code is correct, you need to place it at the right place. The Private declaration of the array variable (SoundList()) should be at the beginning as is though.
0
 

Author Comment

by:lachstock
ID: 21827721
ok.  Sorry, but now getting SoundList as an invalid qualifier in the statement:
PlayFile SoundList(Contract).Value

Option Explicit

Private SoundList() As String

 Public StopCode As Boolean
 

Sub test()

If StopCode = True Then Exit Sub
 

Application.OnTime Now() + TimeValue("00:00:05"), "Test"

Dim FileData As String

    Open "C:\Soundlist.txt" For Binary As #1

    FileData = String$(LOF(1), Chr$(0))

    Get #1, , FileData

    Close #1

    SoundList = Split(FileData, vbCrLf)

Dim Contract As Integer

For Contract = 0 To 10

If Sheet1.Range("A1").Offset(0, Contract).Value > (Now() - TimeValue("00:00:10")) Then

    AppActivate ("Microsoft Excel non-commercial use - alert")

    MsgBox Sheet1.Range("A2").Offset(0, Contract).Value

    PlayFile SoundList(Contract).Value

    End If

    Next Cnt

End Sub

Open in new window

0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 21828399
So what is your VB coding experience? Anyway, check your "SoundList.txt" file. If you are going to run a loop of 10 columns, then you better have at least 10 filenames mentioned on each line, nothing less. The original file you uploaded had only 3 lines.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:lachstock
ID: 21828441
experience is limited, hence beginner....
have updated the number of filenames.  Are they written in the correct format?  i have one file name per line, should they be seperated by something else? file uploaded again.  haven't changed code. Is there a typo in my code?
Soundlist.txt
0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 21828613
You need one more... basically 0...10 is actually eleven numbers
0
 

Author Comment

by:lachstock
ID: 21842785
have changed.  But still didn't work......Still getting PlayFile SoundList(Contract).Value as invalid qualifier.
0
 
LVL 9

Accepted Solution

by:
Shahid Thaika earned 200 total points
ID: 21842833
OK I see the problem... you have declared an integer "Contract"...

Dim Contract As Integer

But in your loop you are using "Cnt"...

For Cnt = 0 To 10

If you are going to use Cnt as the looping variable, then your statement should read....

"PlayFile SoundList(Cnt)"

Note, there is no need for ".Value" in the above statement, since it is a string variable.
0
 

Author Comment

by:lachstock
ID: 21842862
ok thanks, have fixed that.  Sorry, But now getting:
Compile error:
Sub or Function not defined?
Full code reattached
Option Explicit

Private SoundList() As String

Public StopCode As Boolean
 

Sub test()

If StopCode = True Then Exit Sub
 

Application.OnTime Now() + TimeValue("00:00:05"), "Test"

Dim FileData As String

    Open "C:\Soundlist.txt" For Binary As #1

    FileData = String$(LOF(1), Chr$(0))

    Get #1, , FileData

    Close #1

    SoundList = Split(FileData, vbCrLf)

Dim Contract As Integer

For Contract = 0 To 2

If Sheet1.Range("A1").Offset(0, Contract).Value > (Now() - TimeValue("00:00:10")) Then

    AppActivate ("Microsoft Excel non-commercial use - alert")

    MsgBox Sheet1.Range("A2").Offset(0, Contract).Value

    PlayFile SoundList(Contract)

    End If

    Next Contract

End Sub

Open in new window

0
 
LVL 9

Expert Comment

by:Shahid Thaika
ID: 21843027
Jeez!! This is more like teaching you VB as opposed to solving a small problem... that too only for less than 200 points!! Anyway, obviously one of your subroutines is either invalid or not present, and I am guessing it is PlayFile. Do you have a PlayFile() sub routine declared. If not check out this link to know how to play sounds in VB...

http://www.a1vbcode.com/snippet-179.asp
0
 

Author Comment

by:lachstock
ID: 21843898
Thanks.  Yes, it looks like i accidently removed the playlist code i had in the initial question/code i sent above.  Working well now, thanks for your help.  Full code attached.  Appologies for my lack of experience, but i guess thats why the questions.....learning every day.
Option Explicit

Private SoundList() As String

    

Private Declare Function PlaySound Lib "winmm.dll" _

    Alias "PlaySoundA" (ByVal lpszName As String, _

    ByVal hModule As Long, ByVal dwFlags As Long) As Long

Const SND_SYNC = &H0

Const SND_ASYNC = &H1

Const SND_FILENAME = &H20000

Public StopCode As Boolean

Public Sub PlayFile(wFile As String)

    Call PlaySound(wFile, 0&, SND_ASYNC Or SND_FILENAME)

End Sub

Sub test()

If StopCode = True Then Exit Sub
 

'If A1 has the time and A2 has the trade:
 

Application.OnTime Now() + TimeValue("00:00:05"), "Test"

Dim FileData As String

    Open "C:\Soundlist.txt" For Binary As #1

    FileData = String$(LOF(1), Chr$(0))

    Get #1, , FileData

    Close #1

    SoundList = Split(FileData, vbCrLf)

Dim Contract As Integer

For Contract = 0 To 2

If Sheet1.Range("A1").Offset(0, Contract).Value > (Now() - TimeValue("00:00:10")) Then

    AppActivate ("Microsoft Excel non-commercial use - alert")

    'PlayFile "c:\windows\media\chimes.wav"

    'change the path between the quotes to the path of the wav file to play

    MsgBox Sheet1.Range("A2").Offset(0, Contract).Value

    PlayFile SoundList(Contract)

    End If

    Next Contract

End Sub

Open in new window

0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

760 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

21 Experts available now in Live!

Get 1:1 Help Now