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

Pass TextBox To Custom Function (UDF)

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

Select Cell & Pass Data To Another Object

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

Create Range Object & Pass To A Subroutine

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

Pass Multiple Tables Into Custom Interpolate Function

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

VBA Custom Function - Form Control Name Object Not Supported?

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

Custom View Error. Cannot Shift Object Off Sheet

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

Object Library Invalid Or Contains References To Object Definitions

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

How To Make A Custom Conversion Program / Formula Between Custom Data

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

Custom Transferable Toolbar Of Custom Functions

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

Pivot Chart Object: Find Any Suitable Object To Choose From To Make A Pivot Chart In Powerpoint

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

Use Vba Pass Array To C++ Dll

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

Pass Date Value In Sql

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

Pass Argument To A Sub From Another Sub

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

Pass Data Between Worksheets

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

How To Pass These Worksheets To Another Workbook

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

Pass A File Name As A Variable

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

Get Range To Pass Into Function

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

Pass Variable From One Userform To Another?

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

Pass And Return Variables?

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

Pass String To ParamArray?

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

Pass Value To Macro In Another Worksheet

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

Pass Keystrokes To Another Program

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

Pass The Variable In The Range

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

Macro Sub Pass Parameter

Aug 21, 2008

I've written a function to delete the charts on a worksheet: ....

View 9 Replies View Related

Pass Variable From MS Word

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

Pass Variable Between Modules

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

Pass A Sheet As A Parameter To A Sub

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

Pass Value From Function To Procedure

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

Pass Cell Value To VBA & Round It Off

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







Copyrights 2005-15 www.BigResource.com, All rights reserved