[發問] 請教將VBA 碼運用在VB內問題。

我想把以下2組VBA 的代碼弄進VB 的按鈕內,讓我按下VB的按鈕就可以執行VBA 的動作,但我在VB開了表單後,再設定了按鈕,同時也選擇了他的Excel Library 16 參考。
但是我在點了按鈕後,要定義他是Excel 的程序,他只是反白了,好像不行,所以想請教大家,如果我需要把VBA的源碼弄進VB的按鈕內應該差什麼,現附上代碼給大家指教一下,非常感謝。

VB內的情況是以下:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApp5
{
public partial class Form1 : Form
{
      public Form1()
      {
         InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {


      }
}
}

我的2組原本VBA 源碼。

組1:

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("Sheet1").UsedRange.Rows.Count
   'MsgBox rowNum


   If (rowNum - 1 >= 1 And Workbooks(BookName).Worksheets("Sheet1").Cells(2, 1).Value <> "") Then
      Workbooks(BookName).Worksheets("Sheet1").Rows("15:" & rowNum).Select
      Selection.Copy
      Windows("Data.xlsx").Activate
      Rows(currentRow).Select
      ActiveSheet.Paste
      currentRow = currentRow + (rowNum - 14)
      Windows(BookName).Activate
   End If
   Next j

End If

Next i

Windows("Data.xlsx").Activate

Range("K2:K14").Value = "1"

Range("L2").Formula = "=J2"

[L2].Copy

With Range("L3:L" & [A65536].End(xlUp).Row).PasteSpecial

Range("M2").Formula = "=J2"

[M2].Copy

With Range("M3:M" & [A65536].End(xlUp).Row).PasteSpecial

End With

End With

End Sub

組2:

Sub 巨集1()
Application.ScreenUpdating = False
LineNo = ActiveSheet.UsedRange.Rows.Count - 1
Sheets.Add.Name = "rr"
Sheets("AA").Rows(1).Copy Sheets("rr").Rows(1)
rrLow = 1
For i = 1 To LineNo
If Sheets("AA").Cells(i + 1, 7) > 1000 Then
n = WorksheetFunction.RoundUp(Sheets("AA").Cells(i + 1, 7) / 1000, 0)
For k = 1 To n
rrLow = rrLow + 1
Sheets("AA").Rows(i + 1).Copy Sheets("rr").Rows(rrLow)
If k <> n Then
Sheets("rr").Cells(rrLow, 7) = 1000
Else
Sheets("rr").Cells(rrLow, 7) = Sheets("AA").Cells(i + 1, 7) - 1000 * (k - 1)
End If
Sheets("rr").Cells(rrLow, 8) = k
Sheets("rr").Cells(rrLow, 9) = n
Next k
Else
rrLow = rrLow + 1
Sheets("AA").Rows(i + 1).Copy Sheets("rr").Rows(rrLow)
End If
Next i
End Sub

樓主熱帖

stephenlee

中學生 Rank: 2

帖子: 63
主題: 32
精華: 0
積分: 125
點名: 0
作業系統: WIn10
軟體版本: OFFICE2013
閱讀權限: 20
註冊時間: 2018-7-19
最後登錄: 2022-8-11

2^#

發表於 2018-9-28 00:03 | 只看該作者

本帖最後由 stephenlee 於 2018-9-28 00:05 編輯

不好意思,不知道是不是我選錯了VB的專案。
所以他的程式碼不知道是不是用錯了。應該是VB的 windowform,但是我設定了 Dim Workbooks和Worksheet 他都說沒有定義,認不出他是Excel 。
如下圖我在網路上找的例子,跟他打了之後,出現問題。謝謝