passing a frame name to a subroutine

medic5678

New Member
Joined
Nov 13, 2018
Messages
48
I'm trying to create a subroutine (actually two)

The first finds the coordinates of the frame and stores the value, then sets the values I need to explode the frame size.

Sub displayframe(frm As msforms.Frame)


With frm
ht = .Height
wdth = .Width
Tp = .Top
lft = .Left


.Height = 300
.Width = 550
.Top = 140
.Left = 50



frm.Visible = True

End With


End Sub



And the second restores the original coordinates:


Sub restoreframe(frm As msforms.Frame)
With frm
.Height = ht
.Width = wdth
.Top = Tp
.Left = lft

End Sub

I'm calling the subroutines with:
displayframe (Me.Neuroframe)
restoreframe (Me.Neuroframe)


I'm getting the error "type mismatch"

What am I doing wrong here? I'm creating subroutine typed for a frame and I'm passing it a frame. Thanks to all who reply!
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,666
Office Version
365
Platform
Windows
It is probably caused by how or where you are declaring your variables
- declare above the first procedure in the module as type Double

This works for me

Code:
Option Explicit

Private ht As Double, wdth As Double, tp As Double, lft As Double

Sub displayframe(frm As msforms.Frame)
    With frm
        ht = .Height
        wdth = .Width
        tp = .Top
        lft = .Left
    
        .Height = 300
        .Width = 550
        .Top = 140
        .Left = 50
    
        frm.Visible = True
    End With
End Sub

Sub restoreFrame(frm As msforms.Frame)
    With frm
        .Height = ht
        .Width = wdth
        .Top = tp
        .Left = lft
    End With
End Sub

Private Sub CommandButton1_Click()
    Call displayframe(xxx)
End Sub
Private Sub CommandButton2_Click()
    Call restoreFrame(xxx)
End Sub
 

medic5678

New Member
Joined
Nov 13, 2018
Messages
48
Thanks for your help. What fixed it was using the word "Call". Not entirely sure when you need than and when you don't.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
When you use Call, you must enclose the arguments in parens. When you don't use Call, you typically do not use parens; it has a specific meaning when you do (you can ask if you're curious), and you can't enclose an object variable in them.
 

medic5678

New Member
Joined
Nov 13, 2018
Messages
48
When you use Call, you must enclose the arguments in parens. When you don't use Call, you typically do not use parens; it has a specific meaning when you do (you can ask if you're curious), and you can't enclose an object variable in them.
Okay, I'm curious. what does it mean when you do, when should you? I'm a newbie sorting through this, but I'd like to learn to do things "the right way" if possible.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
When you invoke a Sub with Call, you must use parens around the arguments:

Code:
Call MySub(arg1, arg2)
When you assign the result of a function to a variable, you must use parens around the arguments:

Code:
myResult = MyFunc(arg1, arg2)
If you want to pass arguments ByVal (by value, a copy of the variable) or ByRef (by reference, a pointer to the variable), according to the procedure's signature, you don't include parens:

Code:
MySub arg1, arg2
MyFunc arg1, arg2
If you want to pass arguments ByVal, irrespective of the procedure’s signature (which won't work for a procedure expecting an array (because arrays are always passed by reference) or an object), you enclose the argument in parens, or an extra set of parens. In the examples below, arg1 is passed by value, and arg2 according to the procedure’s signature:

Code:
Call MySub((arg1), arg2)
myResult = MyFunc((arg1), arg2)
MySub (arg1), arg2
MyFunc (arg1), arg2
That’s because when you add parens around an argument that doesn't require them, the argument is evaluated, and the result of the evaluation is passed to the procedure. It means the argument is passed by value regardless of how the procedure requested it.

That also explains why you can't use parens around two or more arguments when they are not required; the evaluation of (arg1) may be meaningful, but the evaluation of (arg1, arg2) is not. That's why this generates an error:

Code:
Msgbox (Prompt:=arg1, Title:=arg2, Buttons:=arg3)
That also explains why if you call a procedure expecting a range with parens where none are required, you get a type mismatch error; because the evaluation of a range is a Variant, or Variant/Array.

You can't pass a Variant ByRef as a static type (Long, String, ...).

You can pass a statically-typed variable ByRef as a Variant, and the called procedure can modify its value with data of the appropriate (or coercible) type.

So: Parens means completely different things according to context. VBA might have used some other bookends (curly braces, square brackets, whatever) to indicate enclosing a list of arguments versus a call for evaluation, but it didn't.

Since objects other than Range objects are always passed ByRef, you can never put unnecessary parens around an object variable. Chip Pearson (http://www.cpearson.com/excel/byrefbyval.aspx) explains the meaning of ByRef and ByVal for an object variable:

The ByRef and ByVal modifiers indicate how the reference is passed to the called procedure. When you pass an object type variable to a procedure, the reference or address to the object is passed -- you never really pass the object itself. When you pass an object ByRef, the reference is passed by reference and the called procedure can change the object to which that reference refers to. When an object is passed ByVal, a copy of the reference (address) of the object is passed.
 
Last edited:

medic5678

New Member
Joined
Nov 13, 2018
Messages
48
Thank you for that detailed explanation. I will reread this several times until it sinks into my brain :).
 

Watch MrExcel Video

Forum statistics

Threads
1,095,985
Messages
5,447,704
Members
405,464
Latest member
milindighe

This Week's Hot Topics

Top