播放mp3、wav、mid等音樂 ( "mciSendString" 可控制 停止)

播放mp3、wav、mid等音樂
(SOURCE : http://merry05.blog.hexun.com.tw/26987503_d.html )

Private Declare Function mciSendString Lib "winmm.dll" Alias "mciSendStringA" _
  (ByVal lpstrCommand As String, ByVal lpstrReturnString As String, _
   ByVal uReturnLength As Long, ByVal hwndCallback As Long) As Long
Private Sub Form_Load()
Dim musicName As String
musicName = "(全路徑音樂文件名)" '註意：路徑和文件名決不能有空格
mciSendString "open " & musicName & " alias mymusic", 0&, 0, 0
End Sub
Private Sub Command1_Click() '開始播放
mciSendString "play mymusic", "", 0, 0
End Sub
Private Sub Command2_Click() '暫停
mciSendString "pause mymusic", "", 0, 0
End Sub
Private Sub Command3_Click() '繼續
mciSendString "resume mymusic", 0&, 0, 0
End Sub
Private Sub Command4_Click() '停止
mciSendString "stop mymusic", 0&, 0, 0
End Sub
Private Sub Command5_Click() '關閉
mciSendString "close mymusic", 0&, 0, 0
End Sub

----------------------------------
(winmm.dll)API函數mciSendString的詳細資料
(SOURCE : https://sites.google.com/site/syanjiushi/cheng-shi-yu-yan-pai-ming/mci-xiang-xi-zi-liao-vb-ban)

Option Explicit
Private Declare Function mciSendString Lib "winmm.dll" Alias​​ "mciSendStringA" (ByVal lpstrCommand As String, ByVal
lpstrReturnString As String, ByVal uReturnLength As Long, ByVal hwndCallback As Long) As Long

    mciSendString是用來播放多媒體文件的API指令，可以播放MPEG,AVI,WAV,MP3,等等，下面介紹
它的使用方法：

　　該函數有四個參數：
第一個參數：lpstrCommand 要發送的命令字符串。字符串結構是:[命令][設備別名][命令參數].
第二個參數：lpstrReturnString 返回信息的緩衝區,為一指定了大小的字符串變量.
第三個參數：uReturnLength 緩衝區的大小,就是字符變量的長度.
第四個參數：hwndCallback 回調方式，一般設為零
返回值：函數執行成功返回零，否則返回錯誤代碼
一、常用命令
1.打開（Open），格式：Open 設備名[type 設備型式][alias 別名]
Dim mName as string
mName = "f:\\mpeg\\mpeg1.avi"
mciSendString "open mName type MPEGVideo Alias​​ movie parent %u Style %u notify",0&, 0, 0
　其中：
open 操作命令
mName 全路徑文件名
type MPEGVideo 是指打開MPEG,AVI等類型，如果不加這一句，就是打開WAV,MP3等
Alias​​ movie 定義了該操作的別名為movie，後續操作只要指明別名即可
parent %u 源
Style %u 樣式
notify 通知

2.播放（Play），格式：Play 設備名[from 起點][to 終點]
mciSendString "play movie", 0&, 0, 0
mciSendString "play movie fullscreen", 0&, 0, 0 '全屏播放

　　3.暫停（Pause）：
mciSendString "pause movie", 0&, 0, 0

　　4.繼續（Resume）：
mciSendString "resume movie", 0&, 0, 0

　　5.停止（Stop）：
mciSendString "stop movie", 0&, 0, 0

　　6.​​關閉（Colse）：
mciSendString "close movie", 0&, 0, 0

　　7.前進到下一個位置：
mciSendString "step movie", 0&, 0, 0

　　8.後退到上一個位置：
mciSendString "step movie reverse", 0&, 0, 0

9.前進或後退N 個位置(其中N<0 即表示後退)
mciSendString "step movie by " & str(N), 0&, 0, 0

　　10.獲取當前播放位置：
Dim ST As String*64
mciSendString "status movie position", st, len(st), 0

　　11. ​​獲取媒體的總長度：
mciSendString "status movie length", st, len(st), 0
l=val(st) 'L就是所播放文件的長度

　　12.獲取播放信息：
Dim ST As String*64
mciSendString "status movie mode", ST, Len(ST), 0
If Left(ST, 7) = "stopped" Then (處理代碼) '播放完畢

　　13.循環播放：
mciSendString "play movie repeat", 0&, 0, 0


二、控制聲音大小：
Dim V As Long
mciSendString "status movie volume", V, 0, 0 'V是獲取的音量大小值。
V = 50
mciSendString "setaudio movie volume to " & V, &0, 0, 0 'V是設置的音量值


三、設置播放位置.(需事先設定時間格式)，格式：Seek 設備名[to 位置| to start | to end]
Dim P1 as Long, P2 as Long
P1 = 100: P2 = 3000
mciSendString "seek movie to ", P1, 0, 0 'P1是當前起始位置，單位：毫秒
mciSendString "seek movie to start", 0&, 0, 0 '定位到開頭位置
mciSendString "play movie", 0&, 0, 0 '定位後再播放
或者：
mciSendString "play movie FROM P1 to P2",0&, 0, 0 'P1是起始位置，P2是停止位置。單位：毫秒
mciSendString "seek movie to end", 0&, 0, 0 '定位到最後位置


四、在指定控件上播放視頻：
mciSendString "open AVI 文件名parent hWnd style child", 0&, 0, 0
其中，hWnd 是控件的句柄
執行上述命令之後，影片會被放置在控件的左上角，且影片的大小不受控件大小的影響，如果想要改變
影片播放的位置及大小，可以在執行play 指令前先執行put 指令，格式如下：
mcisendString "put AVI 文件名window at XY [Width Height]", 0&, 0, 0
其中：X、Y為影片左上角坐標，Width、Height為影片的寬高度


五、如果播放視頻還可控制亮度
Dim B As Long
mciSendString "status movie brightness", B, 0, 0 'B是獲取的亮度值。
B = 50
mciSendString "setvideo movie brightness to " & B, &0, 0, 0 'B是設置的亮度值


六、錄音設置：
　　錄音前，用以下語句初始化
　　1.設為8位：
mciSendString "set wave bitpersample 8", "", 0, 0
　　2.設為11025Hz
mciSendString "set wave samplespersec 11025", "", 0, 0
　　3.設為立體聲：
mciSendString "set wave channels 2", "", 0, 0
　　4.實現PCM格式（不一定正確）：
MCISENDSTRING "set wave format tag pcm","", 0, 0
　　5.開始錄音：
mciSendString "close movie",0&,0,0
mciSendString "open new type WAVEAudio alias movie",0&,0,0
mciSendString "record movie",0&,0,0
　　6.​​保存錄音到c:\123.wav
mciSendString "stop movie",0&,0,0
mciSendString "save movie C:\\123.wav",0&,0,0
mciSendString "close movie",0&,0,0


七、開關光驅：
mciSendString "set cdaudio door open", "", 0, 0 '打開
mciSendString "set cdaudio door close", "", 0, 0 '關閉


八、其它
　　1.設置設備的各種狀態（Set）
Set alias_name[audio all off][audio all on][time format ms]:
Set命令用來設置設備的各種狀態.如:靜音,有聲音,時間格式為毫秒等.

　　2.取得設備的狀態（Status）
Status alias_name[length][mode][position]:
Status命令用來取得設備的狀態.如:該媒體文件的長度,該媒體文件所處狀態,該媒體文件的當前位置等. 的長度,該媒體文件所處狀態,該
媒體文件的當前位置等.

取出指定 DIRECTORY 內所有 FILENAME

Sub Get_All_Files_in_Dir()
    Dim myFso As Scripting.FileSystemObject
    Dim myFiles As Scripting.Files
    Dim myFile As Scripting.File
    Set myFso = New Scripting.FileSystemObject

              I = 11

    'SET THE FILES NAME
    Set myFiles = myFso.GetFolder(ThisWorkbook.Path).Files
    For Each myFile In myFiles

                   cells(i,1)= myFile.Name
                   I = I + 1

    Next
    Set myFiles = Nothing   'release all
    Set myFso = Nothing
End Sub

註 : 要開用 Microsoft Scripting Runtime 

取得 FILE 的 資訊(VBA)

Sub Get_file_info()
    Dim myFso As Scripting.FileSystemObject
    Dim myStr As String
    Set myFso = New Scripting.FileSystemObject
    With myFso.GetFile(ThisWorkbook.Path & "General.txt")          'FILE FOR CHECKING
    Debug.Print "製作日:" & .DateCreated
    Debug.Print "最後存取日:" & .DateLastAccessed
    Debug.Print "最後更新日:" & .DateLastModified
    Debug.Print "Root磁碟機名:" & .Drive
    Debug.Print "檔案名稱:" & .Name
    Debug.Print "上層資料夾名稱:" & .ParentFolder
    Debug.Print "路徑:" & .Path
    Debug.Print "DOS用短名稱:" & .ShortName
    Debug.Print "DOS用短路徑:" & .ShortPath
    Debug.Print "大小:" & (.Size / 1024) & "KB"
    Debug.Print "類型:" & .Type
    End With
    Set myFso = Nothing                                             'RELEASE ALL
End Sub

VBA 產生隨機數字 (random & randomize 用法)

  (1) 用 "時" "分" "鈔" 做種 , 作為 每次 / 每組 新隨機數字根據
   seed = TimeValue("00:" & Format(Second(Time), "00") & ":" & Format(Minute(Time), "00"))
   Randomize (seed)
   Range("A1") = Int((100) * Rnd + 1)

 (2) 如 ran(0) , ran(-1) or rasn(-2) .... , 會產生和上一次一樣的隨機數字

 (3) Randomize  後面不加任何數值 , 會用電腦系統中的時間做seed


the below is FROM Microsoft WEBSITE

Rnd Function

Returns a Single containing a random number.

Syntax

Rnd[(number)]

The optional number argument is a Single or any valid numeric expression.

Return Values

IF NUMBER IS	RND GENERATES
Less than zero	The same number every time, using number as the seed.
Greater than zero	The next random number in the sequence.
Equal to zero	The most recently generated number.
Not supplied	The next random number in the sequence.

Remarks

The Rnd function returns a value less than 1 but greater than or equal to zero.

The value of number determines how Rnd generates a random number:

For any given initial seed, the same number sequence is generated because each successive call to the Rndfunction uses the previous number as a seed for the next number in the sequence.

Before calling Rnd, use the Randomize statement without an argument to initialize the random-number generator with a seed based on the system timer.

To produce random integers in a given range, use this formula:

Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

      

Here, upperbound is the highest number in the range, and lowerbound is the lowest number in the range.

 NOTE    To repeat sequences of random numbers, call Rnd with a negative argument immediately before usingRandomize with a numeric argument. Using Randomize with the same value for number does not repeat the previous sequence.

Example

 NOTE   Examples that follow demonstrate the use of this function in a Visual Basic for Applications (VBA) module. For more information about working with VBA, select Developer Reference in the drop-down list next toSearch and enter one or more terms in the search box.

This example uses the Rnd function to generate a random integer value from 1 to 6.

Dim MyValue
' Generate random value between 1 and 6.
MyValue = Int((6 * ) + 1)




讓 EXCEL 發聲講英文(VBA)

FROM : http://www.ozgrid.com/forum/archive/index.php/t-163916.html

Sub TEST()
Call SayIt("Hello. This is Microsoft Excel speaking.")
End Sub

Sub SayIt(TheWord As String)
Dim s As Object
Set s = CreateObject("SAPI.SpVoice")
s.Speak TheWord
Set s = Nothing
End Sub

VBA 取得已選取位置的 欄名稱 (COLUMN)

Sub Get_Selected_Column_Address()

Set a = Selection      ' RANGE object
Set b = a.EntireColumn  ' RANGE obejst
 
MsgBox b.Address

End Sub

Write a line in a txt file

Write a line in a txt file
source :from : http://stackoverflow.com/questions/18472596/write-a-line-in-a-txt-file

Sub WriteTextToFile()

    Dim sFile As String, lFile As Long

    sFile = "C:\Test.txt"
    lFile = FreeFile

    Open sFile For Append As lFile
    Print #lFile, "first line of text"
    Close lFile

End Sub

Update To pick the file from a File Open dialog

Sub WriteTextToFile()

    Dim sFile As String, lFile As Long

    sFile = Application.GetOpenFilename("*.txt,*.txt")

    If sFile <> "False" Then
        lFile = FreeFile

        Open sFile For Append As lFile
        Print #lFile, "first line of text"
        Close lFile
    End If

End Sub

In VB you can open files and then manipulate them using FileNum. 

Now in a simple piece of code you could use Filenum 1 (one) manually assigned in the fairly certain knowledge the number isn't being used but in more complicated code this become very risky indeed because if you manually assign the number you may start geting uppredictable results. 

Hence FreeFile() which allocates the next 'unused' number. In the snippet below Filenum is assigned to the next free number by FreeFile and thereafter the file is referred to by the number assigned to it and not the name