標題: [發問] 關於VBA 用於VB按鈕問題。 [打印本頁]

---

作者: stephenlee    時間: 2018-10-4 09:41     標題: 關於VBA 用於VB按鈕問題。

想請教大家如何將VBA的源碼搬進VB的按鈕內,當我按下VB的按鈕後他會執行相關的
動作,想請教以下兩大段VBA 源碼應如何修改至VB內,感謝。

Sub Job_No()

Dim a As Integer

Dim b As Integer

Dim rowNum As Integer

Dim Book As Integer

Dim BookName As String





BookNum = Workbooks.Count


'MsgBox BookNum


For a = 1 To BookNum

BookName = Workbooks(a).Name

Workbooks(BookName).Activate



Range("V:AE").Select

Selection.Delete Shift:=xlToLeft


x = 0
Range("b5").Select

Do
If ActiveCell.Value <> "" Then
x = 0
ActiveCell.Offset(1, 0).Select
Else
Selection.EntireRow.Delete
x = x + 1
End If
Loop Until x > 300
Range("A1").Select


Range("U4") = "#"

Range("U5").Formula = "=LEFT(G1,5)&U4&MID(G1,6,6)"

Range("U5").Select

Selection.Copy

Range("U5").PasteSpecial xlPasteValuesAndNumberFormats


[U5].Copy


With Range("U6:U" & [A65536].End(xlUp).Row)
    .PasteSpecial xlPasteFormulas
    .Value = .Value



End With

Next a
End Sub

Sub unite_data()

Dim BookNum As Integer, rowNum As Integer, currentRow As Integer
Dim i As Integer
Dim j As Integer
Dim BookName As String



currentRow = 2
BookNum = Workbooks.Count
'MsgBox BookNum

For i = 1 To BookNum
BookName = Workbooks(i).Name
If BookName <> "Data.xlsx" Then

     For j = 1 To 1
      Workbooks(BookName).Activate
      
      rowNum = Workbooks(BookName).Worksheets("result").UsedRange.Rows.Count
      'MsgBox rowNum
         
      
      If (rowNum - 1 >= 1 And Workbooks(BookName).Worksheets("result").Cells(5, 1).Value <> "") Then
        Workbooks(BookName).Worksheets("result").Rows("5:" & rowNum).Select
        Selection.Copy
        Windows("Data.xlsx").Activate
        Rows(currentRow).Select
        ActiveSheet.Paste
        currentRow = currentRow + (rowNum - 4)
        Windows(BookName).Activate
      End If
     Next j

End If
   
Next i



Windows("Data.xlsx").Activate

Range("V2:V500").Value = "1"


Range("W2").Formula = "=P2"

[W2].Copy

With Range("W3:W" & [A65536].End(xlUp).Row).PasteSpecial



Range("X2").Formula = "=P2"

[X2].Copy

With Range("X3:X" & [A65536].End(xlUp).Row).PasteSpecial


Range("Y2").Formula = "=U2"

[Y2].Copy

With Range("Y3:Y" & [A65536].End(xlUp).Row).PasteSpecial

Range("V2:V500").Select

With Selection.font

.Size = 15

Range("X2:X500").Select

With Selection.font

.Size = 15




Range("V2:V500").Select

With Selection.font

.Size = 15


Range("W2:W500").Select

With Selection.font

.Size = 15


Range("Y2:Y500").Select

With Selection.font

.Size = 15





End With

End With

End With

End With


End With

End With

End With

End With

End Sub

---

作者: faye59    時間: 2018-10-4 21:47

回復 1# stephenlee


    大致上並無差異，
Ranger我會用Cells來寫，
VB.net去算Excel的儲存格位置，
在位置上的變化性高又簡單計算。

不能執行的部分應該是End(xlUp)
在VB.net中要在Form特別宣告Excel計算值，
只要在最上面加入這個就跟VBA算法一樣了。

1. Const xlUp = -4162
   
2. Const xlDown = -4121

複製代碼

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)