Passing Function Values

Printable View

Jun 17th, 2012, 11:52 PM
billboy

Passing Function Values

I need a little help with using a Function

I have put this in a module:

Code:

Public Function FontInfo() Application.Dialogs(xlDialogFontProperties).Show Worksheets("Sheet1").Range("AC1").Select EmailForm.TextBox3.Font.name = Selection.Font.name EmailForm.TextBox3.Font.size = Selection.Font.size EmailForm.TextBox3.Font.Bold = Selection.Font.Bold EmailForm.TextBox3.Font.Italic = Selection.Font.Italic EmailForm.TextBox3.ForeColor = Selection.Font.ColorIndex Dim Fcolor As Integer, fsize As String, fstyle As String Fcolor = Selection.Font.ColorIndex fsize = Selection.Font.size fstyle = Selection.Font.name End Function

And trying to get the values here:

Private Sub CommandButton4_Click()

Dim c As Integer, s As Integer, n As String

c = FontInfo(Fcolor)

s = FontInfo(fsize)

n = FontInfo(fname)

MsgBox Str(s)
MsgBox Str(s)
MsgBox (n)

End Sub

The come up empty

Can someone show me what I am doing wrong?
Jun 18th, 2012, 01:28 PM
billboy

Re: Passing Function Values

I think I solved it, at least its working for what I need. If there is abetter/more correct way of doing this please let me know:

Code:

Public Type FontInfo Fcolor As Integer Fsize As Integer Fname As String End Type Public Function FontInfo() As FontInfo FontInfo.Fcolor = EmailForm.TextBox3.ForeColor FontInfo.Fsize = EmailForm.TextBox3.Font.size FontInfo.Fname = EmailForm.TextBox3.Font.Name End Function
Jun 19th, 2012, 05:12 AM
westconn1

Re: Passing Function Values

using a type instead of 3 variables is a very good idea, you could, if needed, also use variables to hold different instances of your type
Jun 19th, 2012, 01:23 PM
billboy

Re: Passing Function Values

Quote:

Originally Posted by westconn1

using a type instead of 3 variables is a very good idea, you could, if needed, also use variables to hold different instances of your type

Thank you it seemed like the best approach but just for my own knowledge can you give me an example using variables?
Jun 19th, 2012, 04:49 PM
westconn1

Re: Passing Function Values
Quote:

Public Type FontInfo
Fcolor As Integer
Fsize As Integer
Fname As String
End Type

Public Function FontInfo() As FontInfo

FontInfo.Fcolor = EmailForm.TextBox3.ForeColor
FontInfo.Fsize = EmailForm.TextBox3.Font.size
FontInfo.Fname = EmailForm.TextBox3.Font.Name

End Function

note there are some issues with your code as posted that i failed to notice before
you should not have a procedure name the same as the name for your type, fcolor may overflow as integer, change to long, the values for fontinfo are not retained, but returned each time fontinfo is referenced, thiat is it always gets the current state

assuming you want to save the vales for future reference, try like
vb Code:
```
Public Type FontInfo
Fcolor As Long
Fsize As Integer
Fname As String
End Type
Public tbfi As FontInfo     ' public is only required if you need to be able to access this variable from any other form or module, else use Dim
 
Public Sub getFontInfo()
 
tbfi.Fcolor = UserForm1.TextBox1.ForeColor
tbfi.Fsize = UserForm1.TextBox1.Font.Size
tbfi.Fname = UserForm1.TextBox1.Font.Name
 
End Sub
Sub anyprocedure()
getFontInfo     ' use this to get the font state at any time, the values are retained 
End Sub
Sub any()
MsgBox tbfi.Fname   ' use like this to return the retained values at any time from any procedure
' or return the textbox to original state
End Sub
```
you can define several variables to retain the font values from different points to return later, just declare them all as type fontinfo as above