River's Footmar And Note: 2024

2024年12月30日星期一

營業利益有用論之2024 回顧 PART 14

營業利益有用論之2024 回顧 PART 13

4XX3

2024 86

🌅🧧✨ #冬日溫暖 #新年快樂

集保庫存之，你也太巧合了吧

某間神字輩的老牌公司，我爸還買過他們早年代理的GSM 手機

周股價隨千張大戶一路向...........................大家不喜歡的方向

VBA：FORM 具備縮小、關閉按鈕

故事是這樣開始的，小編一開始是透過.show跟.hide方式來控制表單，發現太消耗記憶體了，

所以在想如果表單可以像office一樣可以放大縮小收斂就好了。

圖1.vba表單

與chatgpt討論多次跟測試後，得到以下：

#If VBA7 Then

Private Declare PtrSafe Function GetWindowLongPtr Lib "user32" Alias "GetWindowLongPtrA" (ByVal hWnd As LongPtr, ByVal nIndex As Long) As LongPtr

Private Declare PtrSafe Function SetWindowLongPtr Lib "user32" Alias "SetWindowLongPtrA" (ByVal hWnd As LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr

Private Declare PtrSafe Function DrawMenuBar Lib "user32" (ByVal hWnd As LongPtr) As LongPtr

Private Declare PtrSafe Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr

Private Declare PtrSafe Function ShowWindow Lib "user32" (ByVal hWnd As LongPtr, ByVal nCmdShow As Long) As Long

#Else

' 32 位元系統使用這些聲明

Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long

Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

Private Declare Function DrawMenuBar Lib "user32" (ByVal hWnd As Long) As Long

Private Declare Function FindWindowA Lib "user32" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function ShowWindow Lib "user32" (ByVal hWnd As Long, ByVal nCmdShow As Long) As Long

#End If

Private Const GWL_STYLE As Long = -16

Private Const WS_MINIMIZEBOX As Long = &H20000

Private Const WS_MAXIMIZEBOX As Long = &H10000

Private Const WS_SYSMENU As Long = &H80000

Private Sub UserForm_Initialize()

Dim hWnd As LongPtr

Dim lStyle As LongPtr

' 獲取 UserForm 的窗口句柄

hWnd = FindWindowA("ThunderDFrame", Me.Caption)

' 獲取窗口的當前樣式

lStyle = GetWindowLongPtr(hWnd, GWL_STYLE)

' 添加最小化和最大化按鈕

lStyle = lStyle Or WS_MINIMIZEBOX Or WS_MAXIMIZEBOX Or WS_SYSMENU

SetWindowLongPtr hWnd, GWL_STYLE, lStyle

' 刷新菜單欄

DrawMenuBar hWnd

End Sub

API 函數聲明區塊

GetWindowLongPtr 和 GetWindowLong：用於獲取窗口的屬性。
SetWindowLongPtr 和 SetWindowLong：用於設置窗口的屬性。
DrawMenuBar：用於刷新表單欄以應用新的窗口屬性。
FindWindowA：用於查找特定窗口的句柄。
ShowWindow：用於顯示或隱藏窗口。

常數聲明

GWL_STYLE：用於指定窗口的樣式屬性。
WS_MINIMIZEBOX、WS_MAXIMIZEBOX 和 WS_SYSMENU：分別表示最小化按鈕、最大化按鈕和系統表單。

UserForm 初始化過程

UserForm_Initialize 子程序：當 UserForm 被初始化時調用該子程序來添加最小化和最大化按鈕。
首先，通過 FindWindowA 找到 UserForm 的窗口句柄。
接著，通過 GetWindowLongPtr 獲取當前的窗口樣式。
然後，將最小化和最大化按鈕及系統菜單的樣式添加到窗口樣式中。

最後，使用 SetWindowLongPtr 設置新的窗口樣式，並通過 DrawMenuBar 刷新表單欄以應用更改。

vba：畫垂直線解說版

新改好的：

Sub 巨集3V2(Name, rngXValues, 垂直線)

If InStr(Name, "Chart") > 0 Then

ActiveSheet.ChartObjects(Name).Activate

ActiveChart.PlotArea.Select

Application.CutCopyMode = False

ActiveChart.SeriesCollection.NewSeries

ActiveChart.FullSeriesCollection(ActiveChart.SeriesCollection.Count).Values = "=" & ActiveSheet.Name & "!" & 垂直線.Address

ActiveChart.FullSeriesCollection(ActiveChart.SeriesCollection.Count).Name = "=""垂直線"""

ActiveChart.FullSeriesCollection(ActiveChart.SeriesCollection.Count).XValues = "=" & ActiveSheet.Name & "!" & rngXValues.Address

ActiveChart.Axes(xlValue).MajorGridlines.Select

ActiveChart.ChartArea.Select

ActiveChart.ChartType = xlColumnClustered

ActiveChart.FullSeriesCollection(ActiveChart.SeriesCollection.Count - 1).ChartType = xlLine

ActiveChart.FullSeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnClustered

ActiveChart.FullSeriesCollection(ActiveChart.SeriesCollection.Count).AxisGroup = 2

End If

End Sub

好的，讓我們逐一分別來解說這段 VBA 代碼。這段代碼的作用是：

主要功能是在 Excel 中的一個圖表裡添加一條垂直線。

這個子程序有三個參數：

Sub 巨集3V2(Name, rngXValues, 垂直線)

Name：圖表名稱
rngXValues：X 軸數據範圍
垂直線：垂直線的數據範圍

檢查圖表名稱：

If InStr(Name, "Chart") > 0 Then
如果圖表名稱包含“Chart”，則繼續執行。

選擇並啟用指定的圖表：

ActiveSheet.ChartObjects(Name).Activate
ActiveChart.PlotArea.Select
Application.CutCopyMode = False

增加新數據：

ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(ActiveChart.SeriesCollection.Count).Values = "=" & ActiveSheet.Name & "!" & 垂直線.Address
ActiveChart.FullSeriesCollection(ActiveChart.SeriesCollection.Count).Name = "=""垂直線"""
ActiveChart.FullSeriesCollection(ActiveChart.SeriesCollection.Count).XValues = "=" & ActiveSheet.Name & "!" & rngXValues.Address
新增一個數據系列，並設置它的數據範圍為垂直線。
設置這個數據系列的名稱為 “垂直線”。
設置 X 軸數據範圍為 rngXValues。

設定圖表類型：

ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.ChartArea.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(ActiveChart.SeriesCollection.Count - 1).ChartType = xlLine
ActiveChart.FullSeriesCollection(ActiveChart.SeriesCollection.Count).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(ActiveChart.SeriesCollection.Count).AxisGroup = 2