Excel VBA 確立解決方向和設計的問題

excel vba

hkhk789123 2022-07-20 16:00:59 ‧ 1077 瀏覽

分享至

公司最近使用新的格式表，新的沒有了Format。
前任的程式使用format作為條件來抓每列的數據(因format下是全是重複值NO)，確保不會抓錯。
後續那個format也可有可無，因此前任直接在出結果時直接將那行刪除，基本上唯一作用就是確保拿到準確位置，不會拿到標題，總數等不符合的東西。
那我需要更改程式來令新和舊都可以用，但我目前出現瓶頸因此重新思考是不是想法出現錯誤，希望大家給點意見。

那程式有一個開始按鈕運行所有程式並且給出所需的資料
(圖中是示意圖)

1.其中一個想法是弄一個按鈕打開檔案直接更新format，更新後才按開始
但我需要由當前活頁簿更改其他活頁簿並且插入一行名為format欄的東西

2.另一種在前任的編碼上改，他是將其他活頁簿複製過來在當前活頁簿篩選。那如果我更改的話我應該需要拿什麼抓每列的數據。有嘗試使用設定日期格式，但因程式開始時會設定表格格式，因此更改後會說型態不符合。而且更改的話可能要重新設定大部分的編碼，我怕自已改後可能出更多的error，改完一個多了兩個出來。

目前就是苦惱於方向的問題，因我更改別人編碼的經驗不多，嘗試後發現牽連太多，一環扣一環，因此停下來檢討自已出現那些問題。請問各位看完有什麼建議幫助小弟，我表達不好請多多包容。

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

海綿寶寶

iT邦大神 1 級 ‧ 2022-07-20 19:35:22

最佳解答

我會選第2種改法「判斷日期」

前任的程式使用format作為條件來抓每列的數據(因format下是全是重複值NO)

從這句話我猜程式可能類似這樣

If Range("C" & nRow) = "No" Then  '第三欄的值是 No
   '抓資料
End If

如果是的話，就改成

If IsDate(Range("A" & nRow)) Then  '第一欄的資料型態是 Date
   '抓資料
End If

回應 5
分享
檢舉

看更多先前的回應...收起先前的回應...

paicheng0111 iT邦大師 5 級 ‧ 2022-07-21 09:49:07 檢舉

我的經驗是，
IsDate要改成Application.IsDate

hkhk789123 iT邦新手 5 級 ‧ 2022-07-21 10:45:06 檢舉

你好，海綿寶寶，paicheng0111。
前任的程式在複製其他活頁簿前有設定當前活頁簿格式


'This function should set the worksheet format
Sub SetWorksheetsWithFormat(WorkSheetName As String)
    With ThisWorkbook.Worksheets(WorkSheetName)
        .Columns("A").NumberFormat = "dd/mm/yyyy"   'creation date
        .Columns("B").NumberFormat = "@"            'account number
        .Columns("C").NumberFormat = "@"            'format
        With .Columns("D")                          'address
       ...

然後會call這個Sub 先設定格式

'This function should open the data files and save the data
'in the worksheet SourceData and the variable SourceData
Sub OpenDataFiles()
    ThisWorkbook.Worksheets("SourceData").Cells.Clear
    Call SetWorksheetsWithFormat("SourceData")

    'get filename from user
    Form = Application _
        .GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Select File To Be Opened(Data File)", MultiSelect:=True)
    ....

但尷尬的是他使用Yes/No 來判斷

For RowIndex = 1 To FileRows
                Dim ColumnCValue As String
                ColumnCValue = FileData.Cells(RowIndex, 3)
                
                If (ColumnCValue = "Yes" Or ColumnCValue = "No") Then  'for old format
                'If (RowIndex > 4 And Not IsEmpty(ColumnCValue)) Then 嘗試不用yes no format 
                    Dim ColumnAValue As Date
                    ColumnAValue = DateValue(FileData.Cells(RowIndex, 1).Value)
                    
                    If (DataStartPeriod <= ColumnAValue And ColumnAValue <= DataEndPeriod) Then
                        Worksheets("SourceData").Cells(TotalNumOfRecord, 1).Value _
                            = ColumnAValue

我有嘗試日期來做篩選條件，但這樣改需要更改SetWorksheetsWithFormat這個子程序，但這個子程序又再牽連其他，反而令我感到苦惱，但如果以我目前的方法是不是只能這樣做?

你好，海綿寶寶，paicheng0111。
前任的程式在複製其他活頁簿前有設定當前活頁簿格式
```

'This function should set the worksheet format
Sub SetWorksheetsWithFormat(WorkSheetName As String)
    With ThisWorkbook.Worksheets(WorkSheetName)
        .Columns("A").NumberFormat = "dd/mm/yyyy"   'creation date
        .Columns("B").NumberFormat = "@"            'account number
        .Columns("C").NumberFormat = "@"            'format
        With .Columns("D")                          'address
       ...
```

![https://ithelp.ithome.com.tw/upload/images/20220721/20150584rxl62qWHlw.png](https://ithelp.ithome.com.tw/upload/images/20220721/20150584rxl62qWHlw.png)
然後會call這個Sub 先設定格式
```
'This function should open the data files and save the data
'in the worksheet SourceData and the variable SourceData
Sub OpenDataFiles()
    ThisWorkbook.Worksheets("SourceData").Cells.Clear
    Call SetWorksheetsWithFormat("SourceData")

'get filename from user
    Form = Application _
        .GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Select File To Be Opened(Data File)", MultiSelect:=True)
    ....
```

但尷尬的是他使用Yes/No 來判斷
```
For RowIndex = 1 To FileRows
                Dim ColumnCValue As String
                ColumnCValue = FileData.Cells(RowIndex, 3)
                
                If (ColumnCValue = "Yes" Or ColumnCValue = "No") Then  'for old format
                'If (RowIndex > 4 And Not IsEmpty(ColumnCValue)) Then 嘗試不用yes no format 
                    Dim ColumnAValue As Date
                    ColumnAValue = DateValue(FileData.Cells(RowIndex, 1).Value)
                    
                    If (DataStartPeriod <= ColumnAValue And ColumnAValue <= DataEndPeriod) Then
                        Worksheets("SourceData").Cells(TotalNumOfRecord, 1).Value _
                            = ColumnAValue
```
我有嘗試日期來做篩選條件，但這樣改需要更改SetWorksheetsWithFormat這個子程序，但這個子程序又再牽連其他，反而令我感到苦惱，但如果以我目前的方法是不是只能這樣做?

修改

海綿寶寶 iT邦大神 1 級 ‧ 2022-07-21 16:08:26 檢舉

我的意思是只要改一列

把

   If (ColumnCValue = "Yes" Or ColumnCValue = "No") Then  'for old format

改成

   If IsDate(FileData.Cells(RowIndex, 1)) Then

其他通通不用改
維持原狀

海綿寶寶 iT邦大神 1 級 ‧ 2022-07-21 16:10:32 檢舉

以下是我寫的測試程式及測試結果供參考

可以看出
IsDate 不僅判斷該儲存格是日期「格式」
還會判斷是合法日期「值」才會認為是 True

Sub Macro1()
    With ThisWorkbook.Worksheets("Sheet1")
        .Columns("A").NumberFormat = "dd/mm/yyyy"   'creation date
        .Columns("B").NumberFormat = "@"            'account number
        .Columns("C").NumberFormat = "@"            'format
    End With
    
    For nRow = 1 To 12
        Call CheckDate("A" & nRow)
    Next
End Sub
Sub CheckDate(ByVal rng As String)
    Debug.Print rng & " is " & IsDate(Range(rng))
End Sub