Call Public Function From Class Module
May 25, 2006I 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
ADVERTISEMENT
Call Public Object From Public Sub?
Aug 14, 2012I 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:
Code:
Public Sub PutInEngine()
Dim InputForm As New FrmInputs
InputForm.Show
[Code].....
Class Module Is Collection Of Other Class
May 24, 2006I'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......................................
Reset All Public Variables In A Module
Aug 6, 2009Is 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
Reference Userform Controls In Public Module
Jan 9, 2007I located combobox on userfom and don't see it in the module.
View 7 Replies View RelatedSyntax To Call Public Macro
Jul 8, 2009I 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 RelatedClass Module
Feb 15, 2010I 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
Call A Private Sub From Another Module
Aug 13, 2009Is it possible to call a Private Sub from another Module?
View 2 Replies View RelatedHow To Call A Macro In A Different Module
Sep 11, 2009I have a macro call psc in one module
in a different module I would like to "call psc"
however, this does not work
Call Sub Routine From Another Module
Apr 8, 2014I 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?
ThisWorkbook.Activate
Range("B9:G9").PasteSpecial
R2D2data.Activate
Range("B7:G7").Copy 'Sch Actual VLH
ThisWorkbook.Activate
Range("B12:G12").PasteSpecial
Call StoreName
R2D2data.Close Savechanges:=False
Application.ScreenUpdating = True
End Sub
Purpose And Definition Of Class Module
Nov 23, 2008What 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 RelatedSharing Class Module Among Multiple Userforms?
Apr 22, 2014I 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.
Creating A Class Module For Dbl Clicking Label
Jul 27, 2009i 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.
Class Module Change Event For Controls
Nov 20, 2006I 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....
How To Call A Private Module.Macro From Within ThisWorkbook
Oct 17, 2003When 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?
Call/Run Macro Within Same Module Using Common Variable
Feb 3, 2009The 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.
Lookup Function Call: Vlookup Call In Sheet
May 21, 2006I 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)
Using Class Module For Handling Events Of Dynamically Created Controls
Mar 4, 2009I 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...........................................
Scoping Function As Public - But Not Available As Worksheet Function
Sep 24, 2012I 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?
Call Function By Name
Aug 30, 2007Just wonder how to call the function if I have it's name in the variable String
for example something like that:
How To Call The Function
May 10, 2007I 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?
example:
Public Function TotDays( _EndDate As Date, _StartDate As Date _) As Integer
TotDays = EndDate - StartDate
End Function
cell
a5: =totdays(b2,A2)
b2: 4/1/2007
a2: 3/1/2007
Call An Add-in Function From VB
Jul 12, 2007I 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?
Defined Function Keeps Interfering With My Other Module
Nov 20, 2007I 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 RelatedUser Defined Function In Another Module
Nov 21, 2007I 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 RelatedComboBox To Call Another Function
Sep 24, 2008How 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.
How To Call A Procedure / Function
Mar 2, 2012I have found some code that does what I want- but i do not understand how to use - call it. The programmer says ...
GetNetworkIPAddress()
' 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 ?
To Use A Variable In A Call Function
Aug 12, 2008I'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
Else
{My Code Here}
End If
If Intersect(Target, Range("D5") Is Nothing Then
Exit Sub
Else
{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?
Function Call To UserForm (VBA)
Aug 23, 2006I 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()...........................
Call Add-in .xla Function From Workbook
Apr 3, 2008I 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 RelatedSum Property Of The Worksheet Function Class
Oct 16, 2008work 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