Pass Custom Object To UDF
Aug 17, 2008
I used to be pretty good with VBA, and I know I am a quite decent programmer in other languages, but I have been working on this for six hours and have no idea what I am doing wrong. Any help would be appreciated.
Here is my class (I know I can do better with access modifiers/ properties in a serious version):
'The class is called ConsCell
Public Car As Variant
Public Cdr As Variant
Private Sub Class_Initialize()
End Sub
Here is the code from the module which tries to call it:
Function MakeCell()
Cell = New ConsCell 'Execution always stops here-- but with no error message
Cell.Car = 15
Cell.Cdr = "NIL"
MakeCell = Cell
End Function
Function PrintCell(c As ConsCell)
PrintCell = c.Car
End Function
The code for a cell in the spreadsheet itself is this:
=PrintCell(MakeCell())
But it gives me the #VALUE! error.
View 9 Replies
ADVERTISEMENT
Mar 10, 2008
I have a simple function defined in one of my worksheets (Sheet1):
Function AddFuel(fuel As String)
MsgBox fuel
End Function
How would I be able to call this from a form button event?
Private Sub CommandButton1_Click()
Sheet1.AddFuel(TextBox1)
End Sub
Everytime I try running this code I receive the error: Run-time error '1004'; Application-defined or, Object-defined error. I've even tried Application.Run("Sheet1.AddFuel", TextBox1) but still no luck. I think this is a pretty common question but I couldn't find any answer to it on the forums.
View 2 Replies
View Related
Dec 22, 2006
I am using excel to stop and start a service on the network and have that part done when using a txtfield to enter in the PC ID. What i want to do is use a list of PCIDs and pass them to my service object to stop and start the service.
Private Sub CommandButton1_Click()
Worksheets("PCIDs"). Range("B2").Select 'my issue is here
Range("B2").Activate 'and here
Do Until IsEmpty(ActiveCell)
Call StopService("ServiceName")
ActiveCell.Offset(1, 0).Select
Loop
Range("B1").Activate
Do Until IsEmpty(ActiveCell)
Call StartService("ServiceName")
ActiveCell.Offset(1, 0).Select
Loop
Worksheets("ServiceName").Select
End Sub
and with this function i need it to pass as a string to txtDeviceID. I have tried just simply setting txtDeviceID as ActiveCell but it didn't like that.
Public Function StopService(ServiceName As String) As Boolean
Dim oSysInfo As New ActiveDs.WinNTSystemInfo
Dim oComp As ActiveDs.IADsComputer
Dim oSvcOp As ActiveDs.IADsServiceOperations
Dim sCompName As String
Dim sSvc As String
Dim lRet As Boolean
View 3 Replies
View Related
Apr 10, 2007
Create Range Object & Pass To A Subroutine
Sub Test(ByRef objRange As Range)
objRange.Value = "Hi"
End Sub
Sub TestTheTestMethod()
With ThisWorkbook. Sheets("Sheet1")
Set objRange = .Range(.Cells(1, 1), .Cells(i - 1, 3))
objRange.Value = "Hi" 'This works fine !
Test (objRange) 'But here... Getting ERROR 424 -- Object Required
End With
End Sub
View 2 Replies
View Related
Jan 14, 2010
I have 5 worksheets each with tables that are formatted the same on each sheet. I have named each table as a named range (ex:filter5tsd15BSF). I have a userdefined function that interpolates values for a single table in a vba module. I can make this function work for a single table by calling the function as =Linterp(filter5tsd15BSF,C12) so I know the function is OK. However, I really rather program this some way that all the tables get passed to the function and then a select case for the conditions for which filter and which TSD (15 or 25) will then select the correct table.
View 2 Replies
View Related
Jun 18, 2014
I'm creating a custom function to cut down on redundant code. However, in converting the code to a function so that it may be used in different instances, I'm getting an unexpected error when the code executes.
The error is:
Code:
Object doesn't support this property or method.
Here is my function:
Code:
Public Function chkClick(checkboxName As String, tabName As String, chartName As String, seriesNumber As Long)
Sheets(tabName).ChartObjects(chartName).Activate
If Sheets(tabName).checkboxName.Value = True Then
ActiveChart.FullSeriesCollection(seriesNumber).Select
[code]....
The function checks the status of the checkbox name passed to it. If it's checked, it will show a chart series. If it's not checked, it will hide the chart series. The error comes in referencing the "checkboxName".
How might I need to alter the code so that I can use the form control name as a variable? This is ultimately one of the key components of the function as each control has it's own name.
View 9 Replies
View Related
Jun 15, 2008
I have a very wide sheet in which I have hidden and saved seven views.I am using it for a very long time but when i did some edit work in the 'full' view,the other views are not showing. In stead I am getting the messages "cannot shift objects off sheet" and "some view settings could not be applied".This is very frustrating since a lot of patience was tested while making this wide data.
View 2 Replies
View Related
Jan 17, 2005
I'm getting the following error:
"Object library invalid or contains references to object definitions that could not be found"
I wasn't getting that error last night and I'm not sure what I may have done to cause this error.
It seems to be cause by code running on one sheet of my workbook, but I'm not really sure about that. I'm still a bit of a novice at VBA.
I'm using Excel 2002 SP3 and I'm running MS XP Home as my OS.
Do you have any ideas what can cause this error and/or how to trace down the offending objects/code?
View 9 Replies
View Related
Feb 15, 2014
I'm trying to make a converter between about 8 various types of values. These are not units like Km or miles or something like that, but rather numbers that represent a specific "hardness value" on a variety of scales (to name a few: HRC, HRA, K)
What I've been doing so far is plotting the two types against eachother and then getting the best trendline I can so that I can use that formula to convert between the two with relative certainty. (for example, when plotting HV vs HRC my fourth order polynomial trendline with an Rsquared of 1 is y=0.0001x4 - 0.0188x3 + 1.0768x2 - 20.709x + 350.69)
My questions comes up where I was hoping to make a window or box of some sort allowing the user to input a numeric value, then selecting the Input units and the hopeful output calculated units, and have the box spit back to the user the conversion.
View 9 Replies
View Related
Sep 2, 2008
I want to be able to create a range of VBA userforms to quickly perform long tedious tasks. I want these userforms to be accessed from a nice tidy toolbar.
I have done this and it looks nice and works well. What I would like to be able to do is have my custom toolbar of userform controlled functions be transferable so that if someone else wants my toolbar and attached functions they can install it easily much the same way you can do with an add in.
Is this sort of thing possible or does it require them to manually install all my userforms, modules and toolbar? If it is possible what sort of things should I be looking at?
View 9 Replies
View Related
Mar 21, 2007
1) i have office 2003 on a laptop. within powerpoint, i can create a 'microsoft excel chart 11' object. to create a link to the excel data source, do i have to go through the odbc sql setup? it works, but i don't want my powerpoint to be dependent on some excel file somewhere. what are the other options to insert/make a functional pivot chart in powerpoint with the data also within powerpoint? the data as sheet option does not result in the chart being a pivot, it's just a plain chart. it has to be a proper object, not an image paste or a chart that updates links with the excel file open.
2) i have office 2007 on my other laptop. i can not find any suitable object to choose from to make a pivot chart in powerpoint. what's the best way to go about in 2007 version?
3) am i going about this the wrong way with the objects? should i be after vba code?
View 4 Replies
View Related
Jan 21, 2007
I want use vba pass array to c++ dll , and return another array back to vba example is below,but I return value always 0 , how should I do? ...
View 9 Replies
View Related
May 11, 2007
Iam trying to pass a date value in a query to fetch data from a excel sheet and write the output in a different sheet.Iam able to get the output if I pass the data directly but unable to store the value and pass it in the string.
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & XLS_Path & _
"PZN-MTD-Opened_Header_Extract.xls;Extended Properties=Excel 8.0;"
CursorLocation = adUseClient
.Open
End With
Set rs = New ADODB.Recordset ........................
View 9 Replies
View Related
Jul 19, 2007
The second subroutine below selects a range of cells, in this case a column. It selects a range that ends with the last cell that has data and starts with either the first cell that has data or the second cell has data (needed for instance if the first cell is just the heading and doesn't contain data). You'll notice I have hardcoded the "includeFirst" option right now. I don't know how to pass this as an argument when calling this subroutine.
In the first subroutine updateAll() I need to call the second subroutine and want to pass an argument to it. Maybe my approach using subs and application.run is the wrong way to do this?
Sub updateAll()
includeHeader = False
Application.Run "SelectColumn"
' need something like:
' Application.Run "SelectColumn(includeHeader)"
' where subroutine below has something like:
' Sub SelectColumn(includeFirst)
End Sub
Sub SelectColumn()
'Select a Column or Row of Cells in a Used Range Quickly
'http://msdn2.microsoft.com/En-US/library/aa155432(office.10).aspx
includeFirst = False
'includeFirst = True
Dim UpBound As Range
Dim LowBound As Range
If ActiveCell.Row > 1 Then
If IsEmpty(ActiveCell.Offset(-1, 0)) Then
Set UpBound = ActiveCell........................
View 3 Replies
View Related
Jul 1, 2014
How can I pass data from a specific cell in one workbook to another worksheet using a hypertext link or command button. For example How would I be able to pass the following data from Workbook 1 R1C1 - Test Data to the same cell in Workbook 2.
View 1 Replies
View Related
Jul 10, 2014
I have tried to pass the worksheets to another workbook by copying the sheets and passing it to the other workbook with Excel's own tool. Whenever I try this I get an error with the API XML request.
View 8 Replies
View Related
Aug 8, 2008
I am calling a sub that I want to do a SaveCopyAs using a variable as the file name but can't get it to work. The file name displays properly in a message box but when I do a SaveCopyAs it does nothing.
This code works:
View 9 Replies
View Related
Jun 11, 2009
I am attempting to call a Function from a cell and pass it a range and an Integer. The Integer works fine, but I cannot get the Range to pass into the function correctly.
View 8 Replies
View Related
Oct 20, 2005
I have code in a userform that shows another userform with a text box. When the user types text there and presses OK, I want that userform to close (which it does fine), and return back to the original userform (which it does fine), but I want the typed text to be stored in a variable that the code in the original userform can use.
I tried declaring a public variable, "Rresponse1", in each userform's procedure, but that doesn't work. I test for the content of the variable afterward in the original userform and it is empty.
In the original userform I have:
SaveList.Show '(this is the second userform)
MsgBox "This is response1: " & Response1 '(to test if the variable is being passed)
In the userform SaveList, in the Private Sub CommandButton1_Click() procedure, I have:
Response1 = SaveList.ListName.Text
Application.ScreenUpdating = True
MsgBox Response1 '(to test the variable, here it shows the text fine)
Unload SaveList
How do I get the Response1 variable to keep its content when control is passed back to the original userform?
View 6 Replies
View Related
Mar 21, 2012
Today's question concerns one sub calling another, having the called sub do some stuff that then effects what the calling sub does.
Essentially
Code:
public sub sub1
dim x as integer
dim countCall as boolean
x = activesheet.cells(1,1).value
countCall = False
call sub2(x, countCall)
[code]....
Obviously the above is a very simplified version of what I'm trying to do, since everything there could of course be handled in a single sub. It is for illustration purposes only. My actual main sub needs to call the secondary sub repeatedly in some stacked loops and its in-feasible to but the second sub's code into the main thread.
The problem remains though, I know how to call a sub and pass variables to it, i just have no inkling on how to pass results back to the calling sub. I've tried a few things I've found around the web but I seem to be missing something important.
View 1 Replies
View Related
Jul 3, 2014
I am trying to pass a string to a function requiring a ParamArray. See below
Code:
Declare Function LibFunc1 Lib "FuncAddin" (ByVal vtSheetName, ParamArray Mylist() As Variant) As Long
Sub testsub()
Year = 2013
Period = December
MyStr = "Year#" & MyYear & """", "Period#" & MyPeriod & """"
LibFunc1("Sheet1", MyStr) 'how to declare string so it can be passed as ParaArray?
End Sub
View 1 Replies
View Related
Aug 15, 2007
I have one worksheet with a macro which calls a macro in another worksheet. I would like to pass a variable from the first worksheet to the second worksheet.
Worksheet1 macro includes the following line of code to call Macro3 in worksheet2:
Application.Run "'Worksheet2'!Macro3"
Worksheet2 has the following code in Macro3:
Do While response 1 And response 2
response = InputBox("Would you like to print your report on one page or two pages?", "Select number of pages", 1)
[rest of code]
I would like to be able to pass a value of '1' to Macro3 and bypass the message box when Macro3 is called from Worksheet1. However, if the user has Worksheet2 open, macro3 should display the message box.
View 9 Replies
View Related
Oct 25, 2007
Is it possible to write a VBA macro that passes keystrokes to another program? I have a spreadsheet with over 650 UPC codes for new products that have to be entered into our inventory system. I have downloaded and tried a few macro recorders that record keystrokes and mouse movements, but find them to be unreliable at best.
When I use the macro recorder to automate a task in Excel, (such as moving data from one sheet to another or importing data from a text file) I always go in after I'm done recording and add additional code to bullet-proof the macro (make sure the focus is set properly, etc.). I need to have this kind of control for this task and a macro recorder doesn't give me that (at least not these lower-end shareware programs I have been trying). I know that there are some higher-end macro recorders that include scripting that you can write and edit, but this our "slow" season business-wise and my supervisor has made it clear that he doesn't want to spend any money right now.
My task is simple (just very repetitive, which is why I don't want to spend all day sitting in front of my PC doing this):
1. Open Excel spreadsheet, go to starting data cell
2. Open 3rd party application and go to the appropriate data entry screen
3. Write a macro that does the following:
- checks to make sure the sheet is the active sheet
- copies contents of the active cell
- moves focus to other app and pastes data from active cell
- moves focus back to spreadsheet, copies the contents of the cell to the right (using ActiveCell.Offset?)
- makes other app active and pastes data
- repeat copy/paste for the next cell to right (in spreadsheet)
- press twice to save record before returning focus to the spreadsheet
- move cursor down one line, repeat entire process until active cell is blank
Is this possible to do? The third party program is not able to listed as a reference in VBA, but I didn't know if it was possible to at least pass keystrokes to it. It is actually a terminal emulator (Anzio Lite). I guess the full-blown version of the program has some scripting capabilities (from the documentation I've read), but the Lite version does not.
View 9 Replies
View Related
Jul 25, 2008
I want to work on a range
range("A1:B100")
The number 100 (in B100) comes from another varibale M
Now how do I write the range so that I don't have to write 100. I want to pass on the variable in the range.
the code should look somewhat like
Range("A1:BM").
I don't know how to pass this variable M onto the range.
View 30 Replies
View Related
Aug 21, 2008
I've written a function to delete the charts on a worksheet: ....
View 9 Replies
View Related
Nov 6, 2008
It is possible to define a variable in a MS Word macro and send it to an Excel spreadsheet?
View 9 Replies
View Related
Dec 11, 2009
I created the following sub to signal when a macro in Module 1 is complete:
Public Sub Done()
Dim complete As Boolean
complete = True
End Sub
I placed this just before the end sub in the macro for which I am trying to detect that it has finished executing:
Call Done
End Sub
In the Sheet 1 Module, the code fails at the statement:
If complete = True Then
The error returned is "Variable not defined." All three subs are declared as Public. Why does the Sheet1 sub not recognize the variable "complete" from the Module 1 macro?
View 9 Replies
View Related
Feb 1, 2010
From Macro1, I want to pass a reference to a sheet. In Macro 2, I want to select that sheet. Here's what i have so far but I'm getting a "subscript out of range" error
Sub Macro1()
Macro2 "Sheet1"
End Sub
Sub Macro2(sheet As String)
Worksheets(sheet).Select
End Sub
View 9 Replies
View Related
Aug 11, 2006
If I have code like this
Private Sub cmdHex_Click()
Dim HexNum As String
HexNum = Me.txtHexVal
'Call a function
ToBinary (HexNum)
'Now do something else
End Sub
And the function ToBinary creates a value called BinVal. How do I pass it back to my subprocedure to do something with it.
View 4 Replies
View Related
Oct 1, 2006
If I have cells that are formatted to a certain decimal width, say, 2 digits, cells that have the value of 3.599 will appear as 3.60. When I reference this cell in VBA, how can I have it pickup the 3.60? Currently, something like Range("A1") or Range("A1").Value will pickup the 3.599.
I feel like theres something thats like Range("A1").XXXX that will get me there, but I can't seem to guess it.
View 6 Replies
View Related