Call Public Function From Class Module

May 25, 2006

I have written a public function inside a class module. Is there anyway I could call the public function in Excel? What is the exact syntax to do it?

View 5 Replies


Call Public Object From Public Sub?

Aug 14, 2012

I am trying to bring up a form after clicking the "ok" button on another form. Both forms are defined in my public sub, so basically, the module creates both forms, calls the first form, and hides the second form. Then the first form hides itself and shows the second form. However, when second form is defined using "dim", the first form can't find it to show it, and when I make the second form "public" in my public sub, I get the error that it is an invalid attribute or function.

Here is the code from my sub that applies to this error:

Public Sub PutInEngine()
Dim InputForm As New FrmInputs


View 3 Replies View Related

Class Module Is Collection Of Other Class

May 24, 2006

I've created two modules, card and pack. Card has three variables (value, name, suit) and pack is made up of an array of 52 cards. Referencing it from a test module (NOT a class module) I expected to be able to use debug.print pack.card(32).suit to return the suit of the 32nd card. Instead I get an error message "Object or With variable not set". What am I doing wrong

Private p_strSuit As String
Private p_strName As String
Private p_iValue As Integer
Public Property Let Suit(strSuit As String)
p_strSuit = strSuit
End Property

Public Property Get Suit() As String
Suit = p_strSuit
End Property

Public Property Let Name(strName As String)
p_strName = strName
End Property......................................

View 7 Replies View Related

Reset All Public Variables In A Module

Aug 6, 2009

Is tere a way to reset all public variables in a module. I have a number of sub routines withing the same module and need to carry a variable from one to another so I have decalared tham as public variables.

This works fine except for an instance when I expect the variable to be empty (because nothing has yet been assigned to it) when in fact it contains the value that was assigned to last time another sub was run. So I suppose what I need is a way to reset all the variables in the module when a particular sub ends

View 3 Replies View Related

Reference Userform Controls In Public Module

Jan 9, 2007

I located combobox on userfom and don't see it in the module.

View 7 Replies View Related

Syntax To Call Public Macro

Jul 8, 2009

I am Having trouble calling an macro that is not in the module that is calling it. I thought that all I had to do was label the Macro as Public instead of private, but it is not working. If I copy and Paste the Macro code into the module that is calling it, everything works just fine, but this is making it difficult to keep things organized.

View 8 Replies View Related

Class Module

Feb 15, 2010

I would like to create a class module that it's the same as a connector shape (for example), but with additional custom properties defined by me. For example, I would like it to have a temperature property.

So, for example if I write in a Sub procedure:

Dim Test1 as EnhancedConnector
Set Test1 = New EnhancedConnector
A new connector appears in the activesheet, but this connector must have my custom defined properties available to VBA Code

View 9 Replies View Related

Call A Private Sub From Another Module

Aug 13, 2009

Is it possible to call a Private Sub from another Module?

View 2 Replies View Related

How To Call A Macro In A Different Module

Sep 11, 2009

I have a macro call psc in one module

in a different module I would like to "call psc"
however, this does not work

View 9 Replies View Related

Call Sub Routine From Another Module

Apr 8, 2014

I am writing a lot of code and have separated each section in different modules For example, I have one module titled OpenR2D2 and another titled StoreName

I want the sub in StoreName to run at the end of OpenR2D2 but I get this compile error "expected variable or procedure, not module"

Here's the end of the code where I put it. What have I not thought of?


Range("B7:G7").Copy 'Sch Actual VLH


Call StoreName
R2D2data.Close Savechanges:=False
Application.ScreenUpdating = True
End Sub

View 4 Replies View Related

Purpose And Definition Of Class Module

Nov 23, 2008

What exactly is a Class Module? I've tried doing some searching on Google and haven't really found a straight answer as to what a class module is and what it is used for. How does it differ from a Normal Module?

View 2 Replies View Related

Sharing Class Module Among Multiple Userforms?

Apr 22, 2014

I have two user forms, each with a checkbox for Jan-Dec. Also, I have a checkbox "All" to check/uncheck the Jan-Dec boxes. I also created a class module so that if any of the Jan-Dec boxes get unchecked, while the "All" box is still checked, it will uncheck the All box. Basically I just don't want the All checkbox to be able to be checked while any single month is unchecked. The only/easiest way I found to do this was with a class module, so I would not need to create individual Checkbox_Jan_Click() events for each month.

Now my issue is, I have 2 userforms like this. I'd like them to share the same class module, but in the class module I need to refer to UserForm1.CheckBox_All. So I need to dynamically refer to whichever userform was active, but as far as I can tell, this is not possible.

View 4 Replies View Related

Creating A Class Module For Dbl Clicking Label

Jul 27, 2009

i would like to do is create a class module so that when the user double clicks on a label a msgbox will open saying the name of the double clicked label. If someone can get me to this point i can modify the code to suit my needs.

Some other things really quick though. I have 80 labels, but i only need this to work for labels 1 through 40 and the labels are on a multipage control within the userform.

View 5 Replies View Related

Class Module Change Event For Controls

Nov 20, 2006

I have a class module (MyCtrlEvents) with a sub (TxtGroup_Change) which I want to handle on a change event for some specific textboxes.

When the form is opening I don't get the correct sum for the textbox "TBSum601". It should be 200 but I get 14464

When I then also change a number in the form for any control like "TB7%", the change trigger event doesn't seem to occur....

View 9 Replies View Related

How To Call A Private Module.Macro From Within ThisWorkbook

Oct 17, 2003

When in a ThisWorkbook macro, I want to call a sub/macro in a Module. Usually you can just do a CALL MACRO1, and it will find it. But my MACRO1 is Private as I don't the users to be able to do a Tools, Macro, Macros and see it. So I don't want to make it a Public macro. So how do I call the private sub/macro from within ThisWorkbook?

Can I proceed the sub/macro's name with the name of the module, kind of like CALL MODULE1.MACRO1? Or do I have to make it public?

View 9 Replies View Related

Call/Run Macro Within Same Module Using Common Variable

Feb 3, 2009

The attached file contains a simplified version of a more complex macro in which a sub routine will be called a number of times to change the colours of cells in different ranges. However, an error is triggered because the a variable and range are not defined in the subroutine although they are in the main part of the macro.

I know I am missing something obvious here, but I'd appreciate any help in knowing how I can define the ranges in the main procedure and then call the subroutine to change values in the different ranges.

View 6 Replies View Related

Lookup Function Call: Vlookup Call In Sheet

May 21, 2006

I have a work book with 3 sheets. Sheet 1 is the main sheet and sheets 2 and 3 will use (I hope vlookup) to update 3 columns from info in sheet 1. my attempt at a vlookup call in sheet 2 is: =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)

my understanding is that: - $b3 is the cell in sheet 2 that will be updated as a result of the vlookup call. - [master_AoJ_2.xls]Sheet1!$B$3;$B$65 refers to range $B$3:$B$65 on sheet1 of master_AoJ_2. it does not work... infact I get nothing at all. when I type the call into cell $b3 of sheet2 excel thinks it is stariaght text. this is how I coded the function call =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)

View 4 Replies View Related

Using Class Module For Handling Events Of Dynamically Created Controls

Mar 4, 2009

I have a userform with a single blank multipage. At runtime additional pages are added, the number of pages depends on input from another userform. Six frames, containing labels, textboxes and comboboxes are then added to each page.

I need to be able to use the textbox and combobox change events of these dynamically created controls to perform lookups and calculations. Although I can name the controls at the time they are created, it is not feasible to write code specifically for each control (I can have over 1,600 text boxes distributed over 9 pages, for example).

Having searched for some time on how to achieve this I believe using a class module is the way to go. However, how to use a class module is just not sinking in I'm afraid.

HTML Private WithEvents mpTextBox As MSForms.TextBox
Private WithEvents mpComboBox As MSForms.ComboBox

Private Sub Class_Initialize()
Set mpTextBox = MSForms.TextBox
Set mpComboBox = MSForms.ComboBox
End Sub

Private Sub mpComboBox_Change()
MsgBox "ComboBox value has been changed."
End Sub...........................................

View 2 Replies View Related

Scoping Function As Public - But Not Available As Worksheet Function

Sep 24, 2012

I have a user defined function in one VBA module that I'd like to call from another module, so I would normaly scope it as "Public".

However, as I don't want it to be available to Excel as a worksheet function, I would also normally scope it as "Private"

Is there a way to have a function that is available to other VBA modules in the same project, but not to Excel as a worksheet function?

View 5 Replies View Related

Call Function By Name

Aug 30, 2007

Just wonder how to call the function if I have it's name in the variable String
for example something like that:

View 11 Replies View Related

How To Call The Function

May 10, 2007

I wrote a function procedure in VBA. Pasted it into a 'VBAProject' sheet in my workbook. But when I try to call the function (by entering the name and arguments into a cell), the sheet displays "#NAME?".

What have I forgotten?


Public Function TotDays( _EndDate As Date, _StartDate As Date _) As Integer
TotDays = EndDate - StartDate
End Function

a5: =totdays(b2,A2)
b2: 4/1/2007
a2: 3/1/2007

View 9 Replies View Related

Call An Add-in Function From VB

Jul 12, 2007

I created a file with one sub and one function. Saved it as an add-in. and followed the promts to insert the Add-in.

The button I assigned to the macro works fine, but when I call the function, I am told "Sub or Function not defined".

Is there some special way to call an add-in function from VB?

View 9 Replies View Related

Defined Function Keeps Interfering With My Other Module

Nov 20, 2007

I have a user defined function that somehow keeps interfering with my other module. I use f8 to step through module 1, but halfway through the process it skips to module 2 where I have the below user defined function...

View 9 Replies View Related

User Defined Function In Another Module

Nov 21, 2007

I am stepping through a sub in module one that for some reason keeps jumping to a user defined function in another module. I've checked and and the function isn't being called.

View 9 Replies View Related

ComboBox To Call Another Function

Sep 24, 2008

How is it possible to take the choice from the Combo Box and send that into a Stored Procedure statement in Excel VBA

I have 3 Combo Boxes, Months, Years, Date.

I'm trying to fillout these into this statement to run my Stored Procedure.

View 10 Replies View Related

How To Call A Procedure / Function

Mar 2, 2012

I have found some code that does what I want- but i do not understand how to use - call it. The programmer says ...

' can be called from a worksheet cell using the formula:
' =GetNetworkIPAddress()

I have put this in a cell but i ger an error #Name?

what should i do ?

View 9 Replies View Related

To Use A Variable In A Call Function

Aug 12, 2008

I'm playing around with the Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) function. I have a list of people in Column D that I'd like to move data around if the user double clicks on a cell in Column D. The list is about 158 rows long, and could grow beyond that. I've gotten lazy typing:

If Intersect(Target, Range("D4") Is Nothing Then
Exit Sub
{My Code Here}
End If

If Intersect(Target, Range("D5") Is Nothing Then
Exit Sub
{My Code Here}
End If
Etc, Etc, Etc

What I was hoping to do is use a variable (t) to cycle through the rows and call the various functions. So what I came up with is:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False
On Error Resume Next
Dim t As Integer
Dim Subtest As String
For t = 4 To 158
Subtest = "D" & t

I've created Sub D4(), Sub D5(), and Sub D6() as a test. This didn't work at all as it's looking for a Subtest(). Also, clicking on D5, D6, didn't do much either. Is there any way to make this cycle work, or should I keep typing away?

View 9 Replies View Related

Function Call To UserForm (VBA)

Aug 23, 2006

I wanted to have that one appear whenever a certain cell in a certain sheet gets a specific value, and depending on the answer of the user the Form should set the value of some other cell. So maybe thereŽs a much simpler solution to this task than what I tried - and IŽll be grateful to learn about it.

Anyway I tried the code which follows below - and it works just fine when I call it from the VBE environment - but inside the UserForm it just stops in between (without any error message) when I operate it from the Excel sheet (but still succeeds with the function). IŽll be very happy if someone could solve this "miracle" for me (even IF thereŽs a better solution altogether). So I just provided a simple Function which does nothing but call the UserForm and is executed whenever the condition is met (some calls to MessageBoxes in between just have been added to see how far the code gets executed): Function:

Public Function Call_my_form()
Call Option_Form.Show
MsgBox "Call executed"
End Function

Call of the function: =If(E11="Go";Call_my_form();"Nothing")

The UserForm just uses two OptionButtons with code as follows (two versions to check whether one of these would be the reason - both do behave the same way):

Private Sub OptionButton1_Click()...........................

View 5 Replies View Related

Call Add-in .xla Function From Workbook

Apr 3, 2008

I have created an Excel add-in (.xla) which contains many custom functions. This add-in is stored as read-only on a network drive. I reference it from various spreadsheets and calls to it work perfectly unless I put a call to it in the Workbook_Open event. When I make a call to a function in the add-in from a spreadsheet's Workbook_Open event, Excel crashes completely and I get the dialog that offers to recover the file and report the problem (i.e. Excel shuts down). I have no doubt that this is being caused by the call to the add-in specifically from the Workbook_Open event as removing this call solves the problem. Also, it is not related to a particular function as I have tried various ones; it is ANY call to the add-in from Workbook_Open that causes it. Is this a bug in Excel or is there something that I need to do before referencing the add-in in Workbook_Open? I have tried Application.CalculateFull and Doevents prior to making the call to the add-in and neither approach worked.

View 2 Replies View Related

Sum Property Of The Worksheet Function Class

Oct 16, 2008

work around the sum error?

Runtime Error '1004'
unable to locate the sum property of the worksheetfunction class

Public Function CalcSheet()
Dim NoSales(0 To 11) As Range
Dim DriveOffs(0 To 11) As Range
Dim Voids(0 To 11) As Range
Dim Shortages(0 To 11) As Range
Dim tNoSales As Integer
Dim tDriveOffs As Currency
Dim tVoids As Currency
Dim tShortages As Currency
Dim X As Integer
Dim NSc As Integer
Dim DOc As Integer
Dim VOc As Integer
Dim SHc As Integer
Dim aNoSales As Integer
Dim aDriveOffs As Currency
Dim aVoids As Currency
Dim aShortages As Currency

View 9 Replies View Related

Copyrights 2005-15, All rights reserved