Solved

Combining the IF and Range functions

Posted on 2008-06-19
14
1,760 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
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.

 

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

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

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

27 Experts available now in Live!

Get 1:1 Help Now