Cool RefEdit Alternative - (Made with a standard TextBox !)

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

wsDAH

New Member
Joined
Oct 16, 2019
Messages
11
Hi Jaafar,

I think I have found an interesting bug when the RefEdit Textbox is called from a Modless Form. In the ShowForm code below you will see that the code is checking a Frm.Tag but the Object Being passed wouldn't have the .Tag as this is set only for the form in the CRefEdit because all of the references are ByVal.

If I have interpreted this incorrectly it would be great to know.

Code:
Public Sub ShowForm(ByVal Frm As Object, ByVal Show As Boolean)

    Call SetWindowLong(hwndFrm, GWL_EXSTYLE, GetWindowLong(hwndFrm, GWL_EXSTYLE) Or WS_EX_LAYERED)
    Call SetLayeredWindowAttributes(hwndFrm, 0, IIf(Show = False, 0, 255), LWA_ALPHA)
    Call SetActiveWindow(Application.hwnd)
    Call ShowWindow(hwndFrm, -CLng(Show))
    'If Frm.Tag Then EnableWindow Application.hwnd, 0
    If IsFormModal(Frm) Then EnableWindow Application.hwnd, 0
    
    If Show = False Then
        If hKBhook = 0 Then
            hKBhook = SetWindowsHookEx(WH_KEYBOARD, AddressOf KeyboardProc, _
            GetModuleHandle(vbNullString), GetCurrentThreadId)
        End If
        ActiveWindow.RangeSelection.Select
    Else
        Call UnhookWindowsHookEx(hKBhook)
        hKBhook = 0
    End If


 End Sub
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,846
Office Version
2016
Platform
Windows
In the ShowForm code below you will see that the code is checking a Frm.Tag but the Object Being passed wouldn't have the .Tag as this is set only for the form in the CRefEdit because all of the references are ByVal.
Despite the object being passed ByVal, the argument still points to the same object so the Tag value will still be there.

When passing Objects, ByVal will create a new variable as expected but this new variable will still hold the same memory address of the object being passed.

Code:
Option Explicit

Sub foo_ByRef(ByRef obj As Object)
    Debug.Print "(Passed ByRef ->)  VarPtr : " & VarPtr(obj) & vbTab & "ObjPtr : " & ObjPtr(obj)
End Sub

Sub foo_ByVal(ByVal obj As Object)
    Debug.Print "(Passed ByVal ->)  VarPtr : " & VarPtr(obj) & vbTab & "ObjPtr : " & ObjPtr(obj)
End Sub

Sub test()

    Dim X As Object
    
    Set X = Application
    Debug.Print "(Before function Call ->)  VarPtr : " & VarPtr(X) & vbTab & "ObjPtr : " & ObjPtr(X)
    Call foo_ByRef(X)
    Call foo_ByVal(X)

End Sub
Output :
Code:
(Before function Call ->)  VarPtr : [COLOR=#008000]2590705437584[/COLOR]   ObjPtr : [COLOR=#ff0000][B]2590600453328[/B][/COLOR]
(Passed ByRef ->)  VarPtr : [COLOR=#008000]2590705437584[/COLOR]   ObjPtr :  [COLOR=#ff0000][B]2590600453328[/B][/COLOR] 
(Passed ByVal ->)  VarPtr : [COLOR=#0000ff]2590705437488[/COLOR]   ObjPtr :  [COLOR=#ff0000][B]2590600453328[/B][/COLOR]


I think objects should be passed ByVal to avoid unnecessary issues as follows:
Code:
Option Explicit

Sub foo_ByRef(ByRef obj As Object)
    Set obj = ThisWorkbook
End Sub

Sub test2()

    Dim X As Object
    
    Set X = Application
    Debug.Print TypeName(X)
    Call foo_ByRef(X)
    Debug.Print TypeName(X)

End Sub
in my opinion, ByRef should be used for boolean flags like the Cancel argument in COM event handlers or if the situation arises where we want a function to return more than one value.
 
Last edited:

wsDAH

New Member
Joined
Oct 16, 2019
Messages
11
Hi Jaafar,

Thank you for that bit of explanation. I didn't realize this and it is awesome to learn more. On to the bug that sent my down the path, why does this change seem to fix Excel not being able to be clicked into after closing the RefEdit Window when Modeless:
Was:
Code:
    If Frm.Tag Then EnableWindow Application.hwnd, 0
Is:
Code:
    If IsFormModal(Frm) Then EnableWindow Application.hwnd, 0
It seems like this should give the same True/False Cases.

Thank you,
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,846
Office Version
2016
Platform
Windows
Hi,

When Modeless, I don't experience the issue you are describing. I can click into excel just fine.
In fact, the line : 'If Frm.Tag Then EnableWindow Application.hwnd, 0' can be omitted altogether because the Tag Property returns 0.

When Modal, the above line is there to make sure that the user cannot click into excel after closing the RefEdit which is what one should expect when using a Modal userform.

Regards.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,737
Messages
5,470,444
Members
406,699
Latest member
perfectioncts

This Week's Hot Topics

Top