Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1781
  • Last Modified:

Combining the IF and Range functions

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
lachstock
Asked:
lachstock
  • 7
  • 7
1 Solution
 
Shahid ThaikaCommented:
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
 
lachstockAuthor Commented:
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
 
Shahid ThaikaCommented:
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!

 
lachstockAuthor Commented:
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
 
Shahid ThaikaCommented:
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
 
lachstockAuthor Commented:
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
 
Shahid ThaikaCommented:
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
 
lachstockAuthor Commented:
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
 
Shahid ThaikaCommented:
You need one more... basically 0...10 is actually eleven numbers
0
 
lachstockAuthor Commented:
have changed.  But still didn't work......Still getting PlayFile SoundList(Contract).Value as invalid qualifier.
0
 
Shahid ThaikaCommented:
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
 
lachstockAuthor Commented:
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
 
Shahid ThaikaCommented:
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
 
lachstockAuthor Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now