Pass Custom Object To UDF
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Pass TextBox To Custom Function (UDF)
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 Replies!
View Related
Built-in Custom Function Not Udf
I am very proficient at Excel/VBA and have a question about custom/user-defined functions that may be a little more advanced. I understand how to write custom functions and access them through the user-defined functions menu, but I would like to be able to include my function in an add-in that users could simply add, and then access the function via the 'Fx' box at the top of Excel, like they would any other built-in function, instead of having to go to the user-defined functions menu. I guess what I am looking for is how to add 'built-in' functions and not user-defined ones. I just want the user to start typing '=customFunction(' and have the parameters pop-up in tool-tip form, like any Excel built-in function would.
View Replies!
View Related
Custom Function, UDF, To Return Nothing Or Empty Text
I am plotting a chart using data from custom functions. On occasion, the formulas return erratic values (due to the underlying data) which I wish to exclude from the chart. If the function does not pick up a value in the code, by default it returns a zero.I would like when this happens to have my function return nothing instead of a value - and I mean absolutely nothing, not a blank string. in this way, the chart line will totally ignore this point.
View Replies!
View Related
Return The Cell Housing UDF In Same Custom Function
I'm trying to create a user-defined function, but the function needs to know what cell it has been placed in. I want to use this cell reference to get some other offset values. I can easily find out offset values based on the ActiveCell, but not for those offset values based on where the UDF is actually placed, which could be almost anywhere on the worksheet.
View Replies!
View Related
Efficiency Of Custom Functions (UDF) Compared To Standard/Native Formulas
Assume I have a sheet consisting of 50 rows with 3 columns of figures. A fourth column contains a formula to calculate values, eg A1+B1+C1, on each line/row. I could enter A1+B1+C1 in the first cell and Copy, Paste to the other 49 cells. Alternatively, I replace A1+B1+C1 with a UDF. Is there any difference in performance with using a UDF compared to using the long-hand/standard approach, or is it just a question of convenience? After all, both are doing the same operation with the same calculation.
View Replies!
View Related
Pass Multiple Tables Into Custom Interpolate Function
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 Replies!
View Related
Select Cell & Pass Data To Another Object
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 Replies!
View Related
Create Range Object & Pass To A Subroutine
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 Replies!
View Related
Custom View Error. Cannot Shift Object Off Sheet
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 Replies!
View Related
UDF #VALUE! When Other Workbooks Open With Same UDF
I have a UDF in a workbook that works fine until I open another workbook which contains the same UDF. When Excel does a full recalculation it not only recalculates the currently selected workbook but also recalculates all other open workbooks. Something from the currently selected workbook appears to interfere with the other open workbooks as these other workbooks show #VALUE! in all cells that use the UDF. Is there some way to make a UDF unique only to the workbook that it resides in such that opening another workbook with the same named UDF won't interfere with it? Function SumRangeLookup(FromCode, ToCode, Database, FromColumn, ToColumn) Dim Code As Range Dim MonthColumns As Integer Dim CalcResult As Double SumRangeLookup = 0 For Each Code In Range(Database) On Error Goto SkipCode If Code >= FromCode And Code <= ToCode Then For MonthColumns = FromColumn To ToColumn CalcResult = CalcResult + Code.Offset(0, MonthColumns) Next MonthColumns End If Next Code SumRangeLookup = CalcResult SkipCode: End Function
View Replies!
View Related
Object Library Invalid Or Contains References To Object Definitions
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 Replies!
View Related
Custom Transferable Toolbar Of Custom Functions
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 Replies!
View Related
Pivot Chart Object: Find Any Suitable Object To Choose From To Make A Pivot Chart In Powerpoint
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 Replies!
View Related
UDF Links
I've been having a small problem with my UDFs. I'm sorry if it's already been asked but english isn't my first language and I don't know the proper search key works to find my problem. Basically, I've been programming functions destined for a single workbook so I don't use addins. If I write a function called J13F and I try to write that in one of my excel sheets, it generally doesn't work. Excel makes me write it like this: ='myfilename.xls!'J13F.J13F(Sheet!F21) However, sometimes when I close then open my file, this won't work and excel will make me use my function normally: J13F(Sheet!F21) I'd like to be able to use the function normally in every sheet in my workbook, how can I do that?
View Replies!
View Related
Optimization For An UDF
Optimize the code below to perform a search function for two variables. Right now it takes a while to execute as it goes through each cell row by row, column by column. It would be nice to optimize it to function a little faster (make that alot faster) . I realize my coding methods are not all best practice habits, so let me know your thoughts, workarounds or adjustments. In its basic form the code pasted in below is being used to locate data at the intersection of a given row and column. And this data can lie anywhere within this array, unsorted. Where the Row to search contains a team members name (Derek, John, etc.) D3:D50 and dates in columns Q3 to IU94. My spreadsheet has a data page with a list of team member names in D3 to D50, and a listing of dates running accross Q3 to IU94. Each members name may appear muliple times through out D3:D50. So for instance "Derek" (cell D6) worked 40 hours (cell Q6) the week of 9/1/08, it is the 40 that I want returned to a summary page and since the name Derek may show up more than once down the list I want to return all occurances where a date and name instersect on my summary sheet. [/quote] Below is my code which is currently used to lookup team member name "Lookup_Value1" for a given date "Lookup_Value2), it indexes the date column to search for data. Function LOOKUP2(Lookup_Value1 As Variant, Lookup_Value2 As Variant, TABLE_ARRAY As Range) Dim nRow As Long Dim nCol As Long nRow = 0 nCol = 0 With TABLE_ARRAY..............
View Replies!
View Related
UDF Range
I've written a UDF that works...but only on the page where the data is located. For example -- if I use my UDF "DRET" on sheet2 I get a VALUE error =DRET(Sheet1!F6:F13,B2,B3)
View Replies!
View Related
UDF's Using Other UDF's
I am trying to input times as 4 digit integers then use the function "TimeDiff" to work out the difference between times. Timediff works fine if used in a spreadsheet cell. I then want to use this function to work out a relationship between sets of different times. This function is called "Findcase". Both are listed below. It seems that the problem lies with the multiple use of TimeDiff within FindCase because the first calculation WsRs = TimeDiff(Ws, Rs) seems to work but the others do not. Perhaps it's just a bug in my code that I just cannot see. Function TimeDiff(BeginTime As Integer, EndTime As Integer) As Single Dim BeginHour As Integer, BeginMinute As Integer Dim EndHour As Integer, EndMinute As Integer If EndTime < BeginTime Then EndTime = EndTime + 2400 BeginHour = Int(BeginTime / 100) BeginMinute = BeginTime - (BeginHour * 100) BeginTime = (BeginHour * 60) + BeginMinute EndHour = Int(EndTime / 100) EndMinute = EndTime - (EndHour * 100) EndTime = (EndHour * 60) + EndMinute TimeDiff = (EndTime - BeginTime) / 60 End Function
View Replies!
View Related
UDF- Debugging
I'm using this as a formula =ReturnClient(A1) for example. I put the msgbox in there just to get some feedback on what was going on in there, and it does correcty show the first two digits of the cell it's referencing, but the select case is not ever hit. Function ReturnClient(refNumCell As Range) As String Dim cell As Range Dim clientInit As String Dim first2Dig As String For Each cell In refNumCell.Cells first2Dig = Mid(cell.Value, 1, 2) MsgBox (first2Dig)
View Replies!
View Related
UDF Function
I think this will be easier to understand if I just set up a scenario for you from scratch. Open a blank sheet. In cells B2:D10 enter random digits between 1 and 10. In B1 type in =SUBTOTAL(9,B2:B10) which should give you the sum of that column. Use autocomplete to fill in columns C and D with the corresponding formula. I want a function to go in column A that will take the average of each row WHICH takes into account hidden columns. When I type in =SUBTOTAL(101,B1:D1) in cell A1, it returns a divide by 0 error. I understand it's because I'm taking a subtotal of a subtotal and that can't work. The formula works for the cells underneath, because rows 2 to 10 are filled with raw data. However, it doens't take into account hidden columns. So far, I've written a macro that finds the average and takes into account hidden rows. This is the code I've written, and I assigned it to a shortcut key.........................
View Replies!
View Related
Udf Value Not Being Used By Macro
I have designed a dialog box to establish certain criteria for pulling data from a large number of spreadsheets (250+) but I am having difficulty getting the macro to use the user inputted value (numbervalue) in it's code. Here is the sample of code For Row = 3 To totalrows Step 1 If Cells(Row, 9).Value > numbervalue Then RowCount = RowCount + 1 ElseIf Cells(Row, 9).Value < numbervalue Then Row = totalrows End If Next Row I've done a msgbox(numbervalue) before, during and after this and it does return the correct value, however the above loop fails to work properly and treats numbervalue as 0 or null. Is this just a syntax problem or do for next loops somehow 'step out of reality'?
View Replies!
View Related
UDF Recalculation And Speed
I'm working on a financial reporting project that should be in Access but unfortunately it must be in Excel. Some of the formula are complex and I have a UDF to calculate these values. I added the line Application.Volatile to each UDF but when I change the current month in a dropdown box, the UDF's do not recalculate. The dropdown box sets a period number on one of the worksheets - this same value is passed to each UDF. I tried using this code in my dropdown box : Sub DropDown4_Change() Application.CalculateFull End Sub but the PC just hangs. I have hundreds (more likely thousands) of formula in the spreadsheet and the recalc is recalcing everything whereas I just want it to recalc the UDF's. I even changed all of the sumproduct formulae to array sum if formulae which sped things up - that is until I forced the full recalc on the drop down change event. So my question is: is it possible to just recalc the UDF's on 3 worksheets when the user selects a different period in a dropdown box? And a supplementary question : if {sum(if(...))} formula are faster than sumproduct formula, would a (well written) UDF perform faster than a {sum(if(...))} formula?
View Replies!
View Related
UDF, For A Long If Stmt
I need to build a udf that simplifies a 10 nested if statement . i could achieve my task by using a if and statement, but it would exceed the 7 nested limit. I have company names in column A, industry code in column b and four varibles in column C,D,E,F . The g column would be a weighted average of columns C,D,E,F. The weighted average score would have the logic of: If the industry code is equal to "TE", then (C2*.7)+(D2*.1)+(E2*0)+(E2*0) if the industry code is equal to "EN", then (C2*.3)+(D2*.3)+(E2*.3)+(E2*.1) if the industry code is equal to "SF", then (C2*.4)+(D2*.6)+(E2*0)+(E2*0) if the industry code is equal to "H", then (C2*.1)+(D2*.1)+(E2*.1)+(E2*.7) if the industry code is equal to "MM", then (C2*.1)+(D2*.2)+(E2*.3)+(E2*.4) if the industry code is equal to "EE", then (C2*0)+(D2*.0)+(E2*.8)+(E2*.1) if the industry code is equal to "ENG", then (C2*0)+(D2*0)+(E2*.5)+(E2*.5) if the industry code is equal to "ETT", then (C2*0)+(D2*.2)+(E2*.3)+(E2*.5) if the industry code is equal to "HH", then (C2*0)+(D2*.9)+(E2*.1)+(E2*0) if the industry code is equal to "HHW", then (C2*.7)+(D2*.3)+(E2*0)+(E2*0)
View Replies!
View Related
UDF To Narrow Down Results
I have made this UDF to narrow down results to more suit my selling environment. In the NHS UK there are many job roles but for me I need to know if they are a doctor (GP), nurse (Nurse), Pharmacist (Retail). There is some broadness in the NHS and it's workings but for me I just to know what general category they fit into. Problem I have is that when I use this my spreadie takes about 10 minutes to update (I can be looking at 15000 records at anyone time). Is there anyway of speeding it up or should I just percivier? Function PHARbase(cell) Select Case cell Case "General Practitioner" PHARbase = "GP" Case "CLINICAL ASSISTANT" PHARbase = "GP" Case "GP Registrar"
View Replies!
View Related
Improving On UDF To Consider More Variables
I have created a UDF with much appreciated help from this forum but, now I have to consider another set of variables in this UDF which I am not too sure how to put it into the codes. I hope to get some help with this. Previously I have a set of variables classified under Codes that ranges from "AI to "II" now I have to introduce another set named "Status" they are "Accepted" "For Acceptance" "Rejected" Another request if possible is the UDF can incorporate the "If funtion" formula I have used together with this UDF. The formula basically says, if Agreed Column is "blank" use the value in "Estimate". Function AwardImpact(Code As String, Amount As Single) Select Case Code Case Is = "AI", "ai" 'Transfer from DD Provisional Sum (-) to Award Value(+) AwardImpact = Amount Case Is = "BI", "bi" 'Transfer from Award Value(-) to DD Provisional Sum (+) AwardImpact = -Amount Case Is = "CI", "ci" 'Transfer from Other Packages (-) to Award Value(+) AwardImpact = Amount Case Is = "DI", "di" 'Transfer from Award Value (-) to Other Packages Value(+) AwardImpact = -Amount Case Is = "HI", "hi" 'Tranfer from Provisional Sum (-) to Award Value (+) AwardImpact = Amount Case Else AwardImpact = 0 End Select End Function
View Replies!
View Related
UDF Return Previous Value
is there a way for a udf to return the cell's original value instead of an error. ie. the UDF in the cell returned a value from a database. now if i move the database and reopen this sheet, excel will try to recalculate and if it doesn't find the database it will return an error. instead of that i just want excel to return whatever was in the cell, before it had to be recalculated.
View Replies!
View Related
Converting Formula To UDF
creating a formula that would convert a string like 63008 (Mdyy format) to a format that Excel can read as a date =IF(LEN(I2)=5,DATE(20&RIGHT(I2,2),LEFT(I2,1),MID(I2,2,2)),DATE(20&RIGHT(I2,2),LEFT(I2,2),MID(I2,2,2))) In other words IF(the length of the cell value is 5, parse the date this way, otherwise parse it another way) If I2=63008, this would output 6/30/2008 I want to convert this to a function since that formula is rather tedious to type out, but I am new to Excel programming
View Replies!
View Related
Using A UDF In An Array Formula
I have a custom UDF (user defined function) that I have been using for about 2 years with success. I am now trying to use it in an array formula and getting a #Value! error. The UDF looks like this... =MyUDF(A1,B1) ...where A1 is a string to be evaluated and B1 is a number. Basically, the function returns another number depending on the relationship of the two inputs. I have not had any issues in the past using it as shown above. Now, I would like to use this function on a range of cells and sum the results. Here is what I have now... {=SUM(MyUDF(A1,B1:B100))} Does anyone know why this returns a #Value! error?
View Replies!
View Related
UDF Match Function
I have the below code sent in by a MR Excel forum user Public Function BigMatch(rngIndexColumn As Range, varMatchValue As Variant, rngMatchRange As Range) As Variant Dim lngCol As Long Dim rngCell As Range lngCol = rngIndexColumn.Column For Each rngCell In rngMatchRange If rngCell.Value = varMatchValue Then BigMatch = Cells(rngCell.Row, lngCol).Value End If Next rngCell End Function when using the BigMatch Function = BigMatch(any cell in the column you want to pull from, the value you want to match, where you want to find that value) I am using 2 different sheets, Technology & Lessons learnt: when I try the BigMatch Function =BigMatch(Technology!C17,'Lessons Learnt'!A46,Technology!W17:AD27) Instead of returning 'Technology'!C17 its returning the value from Lessons Learnt C17,
View Replies!
View Related
UDF Recalculating Prematurely
With the assistance of SHG, Parsnip, Dave & Daddylonglegs I have manage to put together a little app that assist our staff with scheduling dates in a calendar to avoid a specific trend. Code provided by SHG has worked perfectly; see below: Public DaysOff As Range Function datNext(datLast As Date, DaysOff As Range) As Date Dim datBeg As Date, datEnd As Date Dim iLastPd As Integer Dim iDay As Long, nDay As Long Dim iYr As Integer, iMo As Integer Though the built in DAY function on the calendar is affecting the dates that are calculated cos the function PD also references this. Whenever I change the year or month on the calendar; it changes the UDF, which is not supposed to be. If the recommended dates are going change all the time; as a result of this, it makes this app useless. How do I get around this? Also is this what is causing the UDF to recalculate?
View Replies!
View Related
Udf Stopped Updating
I have made a series of of udf's and used them without a problem in my worksheet. One of the variables used by udf's, " commission " is a named range on the "vrs" sheet in my book. All was working fine, all the udfs updated whenever i changed the value of "commission" by directly entering a new value in the cell referenced by "commission" I then wrote sub to call an input box wich collected a value for commission and put it in the ranged cell. Since then, of the 4 columns of data that commission acts on only 3 now update automatically. The recalcitrant 4th column will only update if I select the cell, place the cursor anywhere in the formula and press enter.
View Replies!
View Related
UDF Always Returning False?
I am including the entire code. It does a regression analysis on data for a second degree polynomial. In doing so, it creates a worksheet called Regression. So I test first to see if this sheet already exists within the workbook and then ask the user to delete it if it does. I've stepped through the function and the function is always returning False whether Regression exists or not.
View Replies!
View Related
Different Results Requested From The Same UDF
Is there a "simple" way to "kill two birds with one shot"!? In the attached WB I wrote a small UDF in order to Multiply A1*B1 whenever it is used in a cell at an ODD row number, - and Add A1+B1 when used in a cell at an EVEN row number. As far as I know it was given as an exercise to a class of students in a European University. Upon opening the WB - the UDF presents the correct results, BUT when A1, or B1 is changed the results get corrupted. If you press [F2] and hit [Enter] in each colored cell - the results get back into order. My question to you is very simple: a) Where did I go wrong ? b) Is there a different UDF that can provide the requested results and being Volatile at the same time ?
View Replies!
View Related
UDF To Return Value To Cell
In formula bar I will be having my own custom function (for eg: =yieldCurveGetData(RTD(".......")) which is invoking VBA macro,In the macro I will be having the below code which is should display the value '123' or the string value.But foreach statement is not executing it's is terminated after " Range(rCell.AddressLocal) = 123 'arrayData ". Function yieldCurveGetData(arrayData As String) As Variant Dim rCell As Range Dim msgVal As String For Each rCell In Selection Range(rCell.AddressLocal) = 123 'arrayData msgVal = msgVal & vbCrLf & rCell.AddressLocal & " = " & rCell Next rCell MsgBox msgVal End Function Iam not sure what's going wrong in it.Because the same function I triggered thru a button It's working. In the above function I checked for "Selection.Count" and it's returning seletion count correctly.
View Replies!
View Related
Identify Cells Using Particular UDF
I have written a UDF to do some calculations i a workbook. I would like to run a sub, checking the cells in the spreadsheet to see if my formula is present. and if so, then add or change an argument in the formula. My problem is: How do I write the code so that it will recognize my formula ? Is it possible to attach a tag to a formula which Excel can search for ? I cannot search for the formula including arguments from specific cells, relativ to the formula cell, as the user is free to input the arguments with reference from anywhere in the workbook.
View Replies!
View Related
Writing Udf For If Color Then Else
I am trying to write a UDF that will go like this: IF A1 is Purple then B1=A1 Else B1=A2. This is what I have written so far and I cannot figure out how to get it to work: Function SumIfPurple( Range As Variant, Range1, Range2) Dim SumAnswer If Range.Interior.ColorIndex = 39 Then Range1 = SumAnswer Else: Range2 = SumAnswer End If SumIfPurple = SumAnswer End Function
View Replies!
View Related
UDF For Moving Average
I have obtained a function (from this site at Exponential Moving Average) which is supposed to help calculate simple mathematical values but it's not working on spreadsheet. assist with taking a look at this as I have attached the spreadsheet?
View Replies!
View Related
Where Do I Paste A Copied Udf
I have copied a colorindex UDF from here and went to VBE, inserted a new module in my Personal project, pasted the UDF and expected to be able to use it in any excel file. It's not working though. I have used this particular UDF before, but on a prevous computer that is not around for me to look at. My goal is to use =IF(colorindex(J2)=colorindex($K$1),J2,"").
View Replies!
View Related
FLOOR Function In UDF
I am trying to ask if a truncated number is divisable by 9. e.g. 1234 truncated would be 123. To truncate the number i've tried to devide it by ten and then round it down using the floor or rounddown function. However i get the "Complie Error Sub Or Function Not Defined" error in my User Defined Function If Floor((i / 10), 1) Mod 9 = 0 Then Do my groove thang End If The compiler is IDing Floor (or rounddown) as the problem.
View Replies!
View Related
UDF Not Updating As Expected
I have a written the function below, but when ever I use it, and for example drag it across lots of cells, they all come up with the same value, and I have to manual click on each one and pres enter to get it to show the right value. I have tried searching but without much luck as I am not sure what I should be searching for. Using application.volatile doesn't. Option Explicit Public Function FirstLinePickUp(inputrow As Variant) As Variant Dim n As Integer Dim testcell As Variant n = 0 testcell = "" Do Until testcell <> "" Or ActiveCell.Column - n <= 0 testcell = Cells(inputrow.row, ActiveCell.Column - n) n = n + 1 Loop FirstLinePickUp = testcell n = 0 testcell = "" End Function
View Replies!
View Related
Cumulative MAX Formula UDF Help
Is it possible to arrive at the formula result in cell (C9) using only the cells in column A and bypassing the need to have a column B? I originally tried =MIN(A1:A9)-MAX(A1:A9) as a way to bypass column B and have a single formula but it did not give me the same result. I was thinkging I might need to have a (UDF) but not sure. Thanks Again. Column A ------------------------Column B-------------------------Column C A1=2.53 ------------------B1=A1-Max(A$1:A1) A2=3.52 ------------------B2=A2-Max(A$1:A2) A3=5.47 ------------------B3=A3-Max(A$1:A3) A4=6.87 ------------------B4=A4-Max(A$1:A4) A5=7.89 ------------------B5=A5-Max(A$1:A5) A6=4.14 ------------------B6=A6-Max(A$1:A6) A7=3.23 ------------------B7=A7-Max(A$1:A7) A8=2.10 ------------------B8=A8-Max(A$1:A8) A9=12.21 ----------------B9=A9-Max(A$1:A9) -------------C9=MIN(B1:B9)
View Replies!
View Related
UDF Causes #value Error Whenever Copying Cells
1) The following code works well except for 1 wierd circumstance. This UDF code resides in a Cell in workbook 'A'. If I have another workbook open (call it workbook 'B'), and I do ANYTHING in workbook 'B' (like copy a cell to another cell within workbook 'B', or perform a calculation within workbook 'B', or even copy a cell from 'A' to 'B'), then the cell that this UDF resides in (workboook 'A') will change from a good number to a #VALUE error. If I work ONLY within 'A', then this UDF functions properly (giving a good result). 2) If I hit the F9 button (i.e. re-calc), with workbook 'A' active, then the #VALUE goes away, and the good number comes back. To clarify, all workbooks have AUTO CALC enabled. I guess the 'Application.Volatile' line causes this code to run when I hit the F9 button (thereby clearing the #VALUE error). If I hit F9 with 'B' active, then the #VALUE error (back in 'A') goes NOT clear. 3) NOTE #1: If I put a BREAKPOINT inside this code, and then repeat a "copy from cell to cell" within workbook 'B', I find that the following code DOES NOT execute, as it does NOT "stop" the code at the BREAKPOINT line. But, when I hit F9, then it DOES stop at the BREAKPOINT. 4) NOTE #2: If I "comment out" the Application.Volatile line, it still does the SAME thing. Except worse, then when I hit F9, it doesn't correct the #VALUE error. I really need the Application.Volatile step with this code, as these functions will not automatically update if their referenced cells are changed. 5) NOTE #3: The variables Row_Num & Column_Num are declared at the Module Level. If I use unique variables and declare these at the Procedure Level, it makes NO difference. I also tried putting a "DoEvents" line after Application.Volatile, but NO difference. 6) It seems I need this code to execute whenever there is a "change" on ANY workbook. Or else, do something to keep these cell values from changing anytime there is "action" on another workbook. Or, is there better code to do the samething? ... Do you have any CLUE how to fix this SNAG?
View Replies!
View Related
Array UDF Returning #VALUE! Error
I have come up with the following array UDF to help me split a large list of values into list of smaller groups. The function takes the 3rd input argument to to understand how many smaller lists it needs to split the bigger range. The 2nd argument simply specifies a seperator, either "," or ";" On using the same on a worksheet I'm getting a #VALUE! error and for the life of me am not able to pinpoint the problem area. Public Function CBOList(Rng As Range, App As String, OutRng As Range) As Variant Dim Func As WorksheetFunction, UniqueVals As New Collection Dim cnt, who Dim ctr As Long, lst As String Dim myarr() As Variant Set Func = Application.WorksheetFunction CBOList = "" Application.Volatile...........................
View Replies!
View Related
Harlan Groves PULL UDF
I am trying to use Harlan Groves PULL UDF. I have inserted a module in my work book and added the code verbatim as posted on his site. My indirect =INDIRECT("'[FFPSTotal "& YEAR &".xls]" &$R9&"'!B$13") This works of course only when FFPS is open. How do I modify the formula to incorporate PULL. I have tried PULL("'[FFPSTotal "& YEAR &".xls]" &$R9&"'!B$13"), and get #REF!
View Replies!
View Related
|
TRACKER
