UserForm.Top值从分配变化 [英] UserForm.Top value changes from assigned
问题描述
我很久以前一直使用这个网站找到我的问题的答案，但是我没有找到有关这个问题的内容。如果我错过了任何事情，请提前道歉。
所以我有一个工作簿（Office 2013，VBA 7.1），我试图使用用户窗体作为一个菜单，将保持固定在页面上，并与工作簿一起移动。我使用了 http://www.cpearson.com/excel/SetParent.aspx 将表单锁定到窗口，并 http://www.oaltd.co。 uk / Excel / Default.htm （FormFun.zip）从表单中删除标题，并阻止其在页面上移动。
代码工作非常好，但我一直遇到一个奇怪的错误，插入的表单.Top值与我在代码中分配的值不同。我也有一个同事运行代码，得到同样的问题。我将列出以下代码的相关部分。
我在模块（Module1）中有以下代码：
Sub CallFormTestA（）
With UserForm1
.Show vbModal = False
.StartUpPosition = 0
.Left = 17
.Top = 147
结束
结束子
我在UserForm（UserForm1）中有以下代码：
Option Explicit
私有声明函数GetWindowLong Libuser32别名GetWindowLongA_
（ByVal hwnd As Long，_
ByVal nIndex As Long）As Long
私有声明函数SetWindowLong Lib user32别名SetWindowLongA_
（ByVal hwnd As Long，_
ByVal nIndex As Long，_
ByVal dwNewLong As Long）As Long
私人声明函数FindWindow Libuser32别名FindWindowA_
（ByVal lpClassName As String，_
ByVal lpWindowName As String）As Long
Private D eclare函数FindWindowEx Libuser32别名FindWindowExA_
（ByVal hWnd1 As Long，_
ByVal hWnd2 As Long，_
ByVal lpsz1 As String，_
ByVal lpsz2 As String）As Long
私有声明函数SetParent Libuser32_
（ByVal hWndChild As Long，_
ByVal hWndNewParent As Long）As Long
Private Const GWL_STYLE As Long =（-16）'窗口样式的偏移
Private Const WS_CAPTION As Long =& HC00000'添加标题栏的样式
Private Sub SetBit（ByRef lStyle As Long，ByVal lBit As Long，ByVal bOn As Boolean）
如果bOn然后
lStyle = lStyle或lBit
Else
lStyle = lStyle而不是lBit
结束如果
End Sub
Private Sub Userform_Initialize（）
Dim MeHWnd，ApphWnd，DeskhWnd，WindowhWnd，Res，lStyle As Long
'获取窗口主Excel应用程序窗口的句柄。
ApphWnd = Application.hwnd
如果ApphWnd> 0然后
'获取Excel桌面的窗口句柄。
DeskhWnd = FindWindowEx（ApphWnd，0&XLDESK，vbNullString）
如果DeskhWnd> 0然后
'获取ActiveWindow的窗口句柄。
WindowhWnd = FindWindowEx（DeskhWnd，0&EXCEL7，ActiveWindow.Caption）
如果WindowhWnd> 0然后
'OK
Else
MsgBox无法获取ActiveWindow的窗口句柄。
End If
Else
MsgBox无法获取Excel桌面的窗口句柄。
End If
Else
MsgBox无法获取Excel应用程序的窗口句柄。
End If
MeHWnd = FindWindow（ThunderDFrame，Me.Caption）
如果MeHWnd = 0然后退出Sub
lStyle = GetWindowLong（MeHWnd ，GWL_STYLE）
SetBit lStyle，WS_CAPTION，False
SetWindowLong MeHWnd，GWL_STYLE，lStyle
If（MeHWnd> 0）And（WindowhWnd> 0）Then
Res = SetParent（MeHWnd，WindowhWnd）
如果Res = 0然后
MsgBox调用SetParent失败。
如果
结束If
End Sub
正如我所说，这段代码正确地创建了表单，但是当我在立即窗口中运行
msgbox userform1.top
时，它返回一个不同的值，在多个尝试中不一致，但通常在250-300，通常小数点为.25，.5或.75。
我不明白我从斯蒂芬使用的大部分代码Bullen和Chip Pearson，但它看起来不像它会影响到userform1.top的价值。任何人都可以识别我正在使用的代码是否有问题会改变userform1.top值？这可能是一个错误？
这是我第一次在这里提出问题，所以请让我知道，如果有任何额外的信息，我应该包括（或谢谢！
编辑1：根据Scott Holtzman的一些反馈，我试过在代码中放置一些debug.print行，以在代码的每个点识别.top的值。我的发现在下面，尽管Scott在跑步时得到了不同的数字。这一切都包含在module1的Sub CallFormTestA（）中。我还发现，如果我第二次运行模块而不重置项目，我会得到不同的结果。如果我第二次再次运行模块，它将保持第二次相同的结果。
使用UserForm1
Debug.Print .Top'返回139.5然后286.5
。显示vbModal = False
.StartUpPosition = 0
.Left = 17
.Top = 147
调试.Print .Top'Returns 286.5 then 286.5
End with
With UserForm1
Debug.Print .Top'139.5 then 286.5
.Show vbModal = False
.StartUpPosition = 0
.Left = 17
.Top = .Top - .Top'更改
Debug.Print .Top'139.5 then 139.5
.Top = 147
Debug.Print .Top'286.5 then 286.5
End with
With UserForm1
Debug.Print .Top'返回139.5然后286.5
。显示vbModal = False
.StartUpPosition = 0
.Left = 17
.Top = -.Top'更改
Debug.Print .Top'返回-372然后-147
.Top = 147
Debug.Print .Top'返回286.5然后286.5
结束With
与UserForm1
Debug.Print .Top'139.5然后286.5
。显示vbModal = False
.StartUpPosition = 0
.Left = 17
.Top = Abs（ - 。Top）'更改
Debug.Print .Top'511.5 then 286.5
.Top = 147
Debug.Print .Top'286.5 then 286.5
结束
与UserForm1
Debug.Print .Top'286.5然后286.5
。显示vbModal = False
.StartUpPosition = 0
.Left = 17
.Top = 0'更改
Debug.Print .Top'139.5 then 139.5
.Top = 147
Debug.Print .Top'286.5 then 286.5
结束使用
Dim n As Long'尝试使用整数来存储.top值
与UserForm1
Debug.Print .Top'139.5然后286.5
。显示vbModal = False
.StartUpPosition = 0
.Left = 17
n = .Top'这将删除小数，但我不在乎。
Debug.Print .Top& ，& n'511.5,512 then 286.5,286
.Top = .Top - n
Debug.Print .Top'138.75 then 140.25
.Top = 147
Debug.Print .Top '286.5然后286.5
结束
编辑2：我已经做了更多的玩，并特别分离代码的某些部分。我发现如果从UserForm1代码中注释掉以下行，则.Top属性设置正确。
If（MeHWnd > 0）And（WindowhWnd> 0）Then
Res = SetParent（MeHWnd，WindowhWnd）
如果Res = 0然后
MsgBox调用SetParent失败。
如果
结束If
要澄清，SetParent函数在这里重复：
私有声明函数SetParent Libuser32_
（ByVal hWndChild As Long，_
ByVal hWndNewParent As Long）As Long
我仍然看不到这些行如何影响form.top财产，但我无法确定问题可能在哪里。我会继续研究这个，但是想要更新这个，以防任何人看这个问题。
编辑3：我能够与这个代码和最终让它做我想要的，但我仍然不知道为什么。我发布了我更新的代码作为答案，但如果任何人可以提供更多的洞察力，我将非常感谢您的输入。
我仍然不明白这里发生的一切，但是我想我应该发布这个答案，以防将来的人有类似的问题。
所有这一切的关键，正如我在第二次编辑中发现的，是SetParent函数：
私有声明函数SetParent Lib user32_
（ByVal hWndChild As Long，_
ByVal hWndNewParent As Long）As Long
这导致Excel添加一个特定的值，说XMod（可以根据用户和硬件的不同而不同）到窗体的.top属性，还有另一个值YMod到.left属性，我在这种情况下没有看到。最终，如果我将.top和.left设置为0，XMod和YMod将导致窗体出现，顶部的顶部与列标题的顶部排列在一起，并且窗体的左侧与行标题左侧。我设置的任何数字将为最终结果添加适当的修饰符。但是，这个数字往往会有一个很小的变化，使其与屏幕分辨率一致，这就是为什么我最初以为是随机的。
然而，这确实引入了另一个问题，每当我设置.top或.left，Excel将添加修改器到.top和.left。意思是，如果我有以下代码：
With UserForm1
.Show vbModal = False
.StartUpPosition = 0
.Left = 17
.Top = 147
结束
Excel将设置 .top = YMod + 147 ， .left = XMod + XMod + 17 。在我的初始代码中，XMod是0，所以我没有注意到它添加了两次。通过将变量设置为YMod，然后在设置.Top后减去该变量，如下所示：
导航
.Top = 0
t = .Top
。显示vbModal = False
.StartUpPosition = 0
.Top = 13 - t
.Left = 19
结束
这给了我需要的正确结果。如果任何人有任何问题让这个工作，我希望这将有所帮助。如果我能看到别人以更有意义的方式回答这个问题，我一定会把他们的标签作为答案。
I've long used this site to find answers to my questions, but I couldn't find anything regarding this question. Apologies in advance if I missed anything.
So I have a workbook (Office 2013, VBA 7.1) where I'm trying to use a userform as a menu which will remain stationary on the page and also move with the workbook. I used a combination of code from http://www.cpearson.com/excel/SetParent.aspx to lock the form to the window and http://www.oaltd.co.uk/Excel/Default.htm (the FormFun.zip) to remove the caption from the form and prevent it from being moved on the page.
This code is working great, but I'm consistently encountering a strange bug, where the inserted forms ".Top" value is different from what I assigned in my code. I also had a co-worker run the code and get the same issue. I'll list the relevant parts of the code below.
I have the following code in a Module (Module1):
Sub CallFormTestA()
With UserForm1
.Show vbModal = False
.StartUpPosition = 0
.Left = 17
.Top = 147
End With
End Sub
And I have the following code in the UserForm (UserForm1):
Option Explicit
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 FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
Private Declare Function SetParent Lib "user32" _
(ByVal hWndChild As Long, _
ByVal hWndNewParent As Long) As Long
Private Const GWL_STYLE As Long = (-16) 'The offset of a window's style
Private Const WS_CAPTION As Long = &HC00000 'Style to add a titlebar
Private Sub SetBit(ByRef lStyle As Long, ByVal lBit As Long, ByVal bOn As Boolean)
If bOn Then
lStyle = lStyle Or lBit
Else
lStyle = lStyle And Not lBit
End If
End Sub
Private Sub Userform_Initialize()
Dim MeHWnd, ApphWnd, DeskhWnd, WindowhWnd, Res, lStyle As Long
'Get the window handle of the main Excel application window.
ApphWnd = Application.hwnd
If ApphWnd > 0 Then
'Get the window handle of the Excel desktop.
DeskhWnd = FindWindowEx(ApphWnd, 0&, "XLDESK", vbNullString)
If DeskhWnd > 0 Then
'Get the window handle of the ActiveWindow.
WindowhWnd = FindWindowEx(DeskhWnd, 0&, "EXCEL7", ActiveWindow.Caption)
If WindowhWnd > 0 Then
'OK
Else
MsgBox "Unable to get the window handle of the ActiveWindow."
End If
Else
MsgBox "Unable to get the window handle of the Excel Desktop."
End If
Else
MsgBox "Unable to get the window handle of the Excel application."
End If
MeHWnd = FindWindow("ThunderDFrame", Me.Caption)
If MeHWnd = 0 Then Exit Sub
lStyle = GetWindowLong(MeHWnd, GWL_STYLE)
SetBit lStyle, WS_CAPTION, False
SetWindowLong MeHWnd, GWL_STYLE, lStyle
If (MeHWnd > 0) And (WindowhWnd > 0) Then
Res = SetParent(MeHWnd, WindowhWnd)
If Res = 0 Then
MsgBox "The call to SetParent failed."
End If
End If
End Sub
As I said, this code creates the form correctly, but when I run msgbox userform1.top in the immediate window, it returns a different value, inconsistent across multiple attempts, but usually in the range of 250-300, often with a decimal point of either .25, .5 or .75.
I don't understand most of this code I'm using from Stephen Bullen and Chip Pearson, but it doesn't look like it could affect the userform1.top value to me. Can anyone identify whether there is an issue with the code I'm using that will change the userform1.top value? Is it possible this is a bug instead?
This is my first time asking a question here, so please let me know if there is any additional information I should include (or leave out).
Thanks!
Edit1: Based on some feedback from Scott Holtzman, I've tried putting some debug.print lines in the code to identify the value of the .top at each point of the code. My findings are below, though Scott did get different numbers when he ran this. This is all contained in the sub CallFormTestA() of module1. I also found that if I ran the module a second time without resetting the project, I would get a different result. If I ran the module again after the second time, it keeps the same result I got the second time.
With UserForm1
Debug.Print .Top 'Returns 139.5 then 286.5
.Show vbModal = False
.StartUpPosition = 0
.Left = 17
.Top = 147
Debug.Print .Top 'Returns 286.5 then 286.5
End With
With UserForm1
Debug.Print .Top '139.5 then 286.5
.Show vbModal = False
.StartUpPosition = 0
.Left = 17
.Top = .Top - .Top 'Changed
Debug.Print .Top '139.5 then 139.5
.Top = 147
Debug.Print .Top '286.5 then 286.5
End With
With UserForm1
Debug.Print .Top 'Returns 139.5 then 286.5
.Show vbModal = False
.StartUpPosition = 0
.Left = 17
.Top = -.Top 'Changed
Debug.Print .Top 'Returns -372 then -147
.Top = 147
Debug.Print .Top 'Returns 286.5 then 286.5
End With
With UserForm1
Debug.Print .Top '139.5 then 286.5
.Show vbModal = False
.StartUpPosition = 0
.Left = 17
.Top = Abs(-.Top) 'Changed
Debug.Print .Top '511.5 then 286.5
.Top = 147
Debug.Print .Top '286.5 then 286.5
End With
With UserForm1
Debug.Print .Top '286.5 then 286.5
.Show vbModal = False
.StartUpPosition = 0
.Left = 17
.Top = 0 'Changed
Debug.Print .Top '139.5 then 139.5
.Top = 147
Debug.Print .Top '286.5 then 286.5
End With
Dim n As Long 'Tried using an integer to store the .top value
With UserForm1
Debug.Print .Top '139.5 then 286.5
.Show vbModal = False
.StartUpPosition = 0
.Left = 17
n = .Top 'This drops the decimal, but I don't care about that.
Debug.Print .Top & ", " & n '511.5, 512 then 286.5, 286
.Top = .Top - n
Debug.Print .Top '138.75 then 140.25
.Top = 147
Debug.Print .Top '286.5 then 286.5
End With
Edit2: I've done some more playing around, and isolating certain parts of the code in particular. I found that if I comment out the following line from the UserForm1 code, the .Top property is set correctly.
If (MeHWnd > 0) And (WindowhWnd > 0) Then
Res = SetParent(MeHWnd, WindowhWnd)
If Res = 0 Then
MsgBox "The call to SetParent failed."
End If
End If
To clarify, the SetParent function is repeated here:
Private Declare Function SetParent Lib "user32" _
(ByVal hWndChild As Long, _
ByVal hWndNewParent As Long) As Long
I still don't see how these lines could affect the form.top property, but I can't figure out where else the problem might be. I'm going to continue to research this, but wanted to update this in case anyone is looking at this question.
Edit3: I was able to wrestle with this code and ultimately get it to do what I want, but I still don't know why. I posted my updated code as an answer, but if anyone can offer any more insight to what happened here, I would greatly appreciate the input.
I still don't understand the entirety of what is happening here, but I thought I should post this answer in case some future person has a similar question.
The key to all of this, as I discovered in my 2nd edit, was the SetParent function:
Private Declare Function SetParent Lib "user32" _
(ByVal hWndChild As Long, _
ByVal hWndNewParent As Long) As Long
This caused Excel to add a particular value, say XMod, (which can vary by user and hardware) to the .top property of the form, but also another value, YMod, to the .left property, which I didn't see in this case. Ultimately, if I set both .top and .left to 0, the XMod and YMod will cause the form to appear with the top of the form lining up with the top of the column header, and the left of the form lining up with the left of the row header. Any number I set will have the appropriate modifier added for the final result. But this number will often have a small variation made to it to align with the screen resolution, which is why I initially thought it was random.
However, this did introduce another problem, as whenever I set either .top or .left, Excel would add the modifier to both .top and .left. Meaning, if I have the following code:
With UserForm1
.Show vbModal = False
.StartUpPosition = 0
.Left = 17
.Top = 147
End With
Excel will set the .top = YMod + 147, and the .left = XMod + XMod + 17. In my initial code, the XMod was 0, so I didn't notice it added it twice. I got around this by setting a variable to the YMod, and then subtracting that variable when setting the .Top later, such as the following:
With Navigation
.Top = 0
t = .Top
.Show vbModal = False
.StartUpPosition = 0
.Top = 13 - t
.Left = 19
End With
This gave me the correct results I needed. If anyone else has any problems getting this to work, I hope this will help. And if I can see someone else answer the question in a way that makes more sense, I'll definitely mark theirs as the answer.
这篇关于UserForm.Top值从分配变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
