Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Combining the IF and Range functions

Posted on 2008-06-19
14
Medium Priority
?
1,778 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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 800 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

670 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