Excel 2010 :: Call Function In Another AddIn (XLAM)
Jan 10, 2013
I try to call a function/procedur in an Excel2010-AddIn (.xlam file) from another Excel2010-AddIn (.xlam file):
Code:
Application.Run (filename.xlam!functionname)
Both AddIn-files are in the same directory (the default AddIn directory).
This call causes the error message "runtime error 91: object variable or with block variable not set".
This problems came up with providing an existing Excel2003-AddIn for Excel 2010.
The same code runs in following cases:
1. call a function in an Excel2003-AddIn (.xla) from another Excel2003-AddIn (.xla)
2. call a function in an Excel2003-AddIn (.xla) from a Excel2010-AddIn (.xlam)
View 1 Replies
ADVERTISEMENT
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
Dec 30, 2008
I have an addin in a shared folder which was created by someone else. (it is a user form). I need to create a macro which will go to its existing folder and then save it in the users personal addin folder also picking up their user id. There will be about 100 users so easier this way than going round doing it for them.
So I suppose the query is three fold.
1, pick up the addin and save it in a personal addin folder.
2, Find the username as part of the path file. For instance my personal would be:
"C:Documents and SettingsO033116Application DataMicrosoftAddIns"
The "O033116" is where I will need a search completed to obtain the user id.
The addin is called ServiceCredt.xla
3, Activate the installed addin.
The reason I want it this way is so the user is unable to access any of the code and we are in control.
View 8 Replies
View Related
May 23, 2012
I have an Excel Addin that I deploy as a read-only addin on a server share. I instruct my co-workers to add the Add-In as they would any other Add-In, but I tell the to answer NO to the Windows question "Copy 'AddinName' to the Addins folder for John Doe?".
That way, everyone will be working with the server version of the addin, and any time I want to roll out an updated version I just place a new addin file on the server. The next time the users restart Excel, the new version is loaded.
Except users never follow directions and some of them click the default 'Yes' when asked and get a local copy of the addin and never receive updated versions of the addin.
How do I disable the "Copy Addin" question completely? I don't want to depend on the user. I know this is possible because I have this 3rd party addin that we use that is installed from c:Program Files and it just stays there and you're never asked to copy the addin when you install it in Excel.
View 9 Replies
View Related
Mar 20, 2008
I have found an addin (http://www.quantdec.com/Excel/smoothing.htm) that adds a worksheet function smoothing data. It operates with 2 arguments:
- a range for the input data to smooth
- a parameter detailing how to do the smoothing.
It outputs a matrix of smoothed results, and therefore requires the Ctrl-Shift-Enter on a spreadsheet. Until there no problem, the function works properly. However I need to repeat the process about 100 times. Therefore I extended my ranges to duplicate the calculations, and that also worked fine. But because I don't need the intermediate data (100 columns by 1000+ rows) and I don't like to wait 2 minutes for the calculations to finish each time I open the saved file again, I thought I could just use VBA and only output the final results.
This is how the working syntax is in a cell:
=SMOOTH(B3:B1032, "5RSSH,5RSSH,>,5RSSH")
And there the nightmare starts :
- I cannot apply it even once: I get a type mismatch whatever I do to calculate the range and put it as an argument of the function. I used a string to store the range name and use it as is (B3:B1032 or even "B3:B1032") or with the Range function Range("B3:B1032"). I even tried to put manually "B3:B1032" with the same result. First major issue
- Because the worksheet function requires a range, I cannot use an array, or at least I tried without success. Therefore I need to output the result on the worksheet each time it is processed, and reread it through the formula, for n times the loop is running for... Kind of inconvenient... So I tried to modify the original smooth() function to create a new one that would accept an array as argument instead of range, but with the same consequence: Type mismatch when I feed the new function with an array (which I checked contains the data to process).
I have tried an example I found somewhere using a built-in Excel Worksheet function MMult and it worked with ranges:
Sub SomeSub()
Dim xArray As Variant, yArray As Variant, zArray As Variant
Dim Fn As Object
Set Fn = Application.WorksheetFunction
xArray = Range("A1:B2").Value
yArray = Range("D1:E2").Value
zArray = Fn.MMult(xArray, yArray)
ActiveCell.Resize(2, 2).Value = zArray
End Sub
View 9 Replies
View Related
Apr 8, 2014
I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
View 14 Replies
View Related
Feb 6, 2014
When running the following code for NSLookup in a module within the current workbook, it runs fine but when I put it in a XLAM file and try and use it as an addin I only get #NAME? as a result. I believe that that addin is installed properly because I have a second module with a sub routine in it (GetIPStatus) that is working, and when entering the command =NSLookup(A2) in a cell it shows up properly like it's an available function, but it does not seem to execute. Do I have to do anything special for a function to run as an addin?
View 4 Replies
View Related
Apr 10, 2013
I have built a custom .xlam file that I"m using as an excel add-in.
I'd like to write some VBA code which takes ranges from any random worksheet (into an array), and get a range from the xlam (into array).
I can't seem to figure out what combination of This.Worksheet and Active.Worksheet I should use.
View 1 Replies
View Related
Jan 24, 2014
The situation I have on my hands is over 200 users, were the majority can't use Excel's built in function to create PDF's.
Yes, I've tried to teach them, but I'm probably not a good teacher.
So, I've resorted to create a button at the top of the worksheet, that says "Create PDF". I thought this was gonna be very easy to program, but alas.
Is there a way to start the built-in Excel dialog that happens when you click "Create PDF/XPS" in "Save & Send"?
View 4 Replies
View Related
Dec 21, 2011
Having problems with the code below on a 2010 Excel spreadsheet. The function flags me at first line and highlights Mid. I get a "compile error, cannot find project or library". I'm trying to set GetSheetName = m_sSheet but the GetSheetName function is not declared as returning any type.
Code:
Function GetSheetName(ByVal m_sFormula As String) As String
Dim m_sSheet As String
m_sSheet = Mid(m_sFormula, 2, InStr(m_sFormula, "!") - 2)
If InStr(m_sSheet, "'") Then
m_sSheet = Mid(m_sSheet, 2, Len(m_sSheet) - 2)
End If
GetSheetName = m_sSheet
End Function
View 1 Replies
View Related
Jan 5, 2012
I am trying to set up a spreadsheet that will have a calculator where variables can be modified in order to properly see staffing requirements using the Erlang C formula. I have all of the known variables, but I can't seem to figure out how to calculate the formula to get the numbers I'm expecting.
[URL] is the website I'm using currently, and I'm stuck at the actual Erlang C formula. I'm not very good at math, but here is how I am reading the formula in English (I'm using the given numbers on the site for the variables so I can check my work).
Erlang C = calls per second to the power of agents staffed divided by the factorial of agents staffed. The result is then divided by calls per second to the power of agents staffed divided by the factorial of agents staffed again, that result is added to 1 minus occupancy, that result is multiplied by the summation of 0 to 54 (54 being the result of 55 agents staffed minus 1), which is then multiplied by calls per second to the power of "k" (i haven't yet figured out what k should equal, i assumed zero from the summation) divided by the factorial "k".
The result I get is not even close to what the website shows as the answer for Erlang, which should be .239. Am I making faulty assumptions anywhere? It's entirely possible I'm not computing the formula properly, it's been about 12 years since I've taken a math class, but since I know all the values for the variables (except for "k"), I should be able to write out a simple Excel formula to compute this.
View 9 Replies
View Related
Jul 22, 2012
I'm currently working on my masters dissertation and am using excel 2010.
Basically, I'm trying to generate a list of say, 1000 numbers within a range. I can already do this using the rand() function. However, I need excel to take each random number, apply it into a formula, and then list the answers in a seperate location. This would be easy to do if there was one simple function (which i could just insert into any adjacent cell). The problem is that each random number value is input into a table. The table, which has about 10 columns and 200+ rows, gives one final answer.
I'm sure there has to be a faster way than creating 1000 tables for each random number generated.
Let's say I have a list of 1000 random numbers in sheet 1. the function table is in sheet 2 and produces the answer in the same sheet, in a single cell. I'd like excel to use each random number generated, input it into the function table, attain the answer from the table, and list it in the cell next to the random number.
View 2 Replies
View Related
Dec 28, 2011
Im trying to write a code in vba in Excel 2010 that would incorporate the vlookup function in excel.
My Data Looks like this:
List 1Weight 1List 2Weight 2List 3Weight 3List 4Weight 4a2.00%j20.00%a14.00%p2.00%b4.00%k32.00%d2.00%y3.00%
c7.50%e10.00%g14.00%u6.00%d8.00%d15.00%h20.00%h7.00%e9.00%q13.00%y45.00%f8.00%f20.00%r5.00%q5.00%
d9.00%g35.00%n5.00%t10.00%h10.00%r14.00%i4.50%w15.00%b0.18Z0.04S0.04Total100.00%Total100.00%Total100.00%100.00%
Where I have X number of Lists, each composed of two columns. The 1st column has the name of each item in each list, and the 2nd column has the value for that item.
What I'd like to do is create a function that would let me choose two lists, and tell me the amount of items in List X and what their values are in List Y, and then total them. Also, I would like it to work the opposite way, and tell me the amount of items in List Y and what their values are in List X, and then total them.
So for example, If I wanted to look at lists 1 and 2, the function would calculate that for List 1, Items D & E are found in List 2 and have a total value of 25%. For List 2, Items D&E are found in List 1 and have a total value of 17%. For all items not in both lists, it would return values of 0.
The code I came up with so far looks like this:
Function AK_Overlap_Go(x, y)
Dim Temp(1 To 2, 2 To 1)
Dim x As Integer
[Code]...
One of the problems I'm having is that the lists contain a different # of items, and so I think I need to loop the vlookup for each row. However, I'm not sure how to do that and get the cumulative values for each list.
View 3 Replies
View Related
Apr 18, 2012
The problem is that whenever I have any browser open, IE, Chrome, Firefox, etc... the Cut & Paste, Copy & Paste function does not work correctly in Excel 2010.
When I Cut or Copy the blinking marquee around my selection briefly appears and then disappears. When I try to paste, I only have two options under the paste special function: Unicode Text and Text, same with cut and paste, however, the text doesn't actually cut, it only copies.
As soon as I close down any of the mentioned browsers, the full functionality of the cut/copy & paste functions are restored, no need to restart excel.
I need to have open a browser most of the time for work as our system is web based, so closing and re-opening is more than just an annoyance.
View 2 Replies
View Related
Jul 22, 2012
I'm currently working on my masters dissertation and am using excel 2010.
Basically, I'm trying to generate a list of say, 1000 numbers within a range. I can already do this using the rand() function. However, I need excel to take each random number, apply it into a formula, and then list the answers in a seperate location. This would be easy to do if there was one simple function (which i could just insert into any adjacent cell). The problem is that each random number value is input into a table. The table, which has about 10 columns and 200+ rows, gives one final answer.
I'm sure there has to be a faster way than creating 1000 tables for each random number generated.
Let's say I have a list of 1000 random numbers in sheet 1. the function table is in sheet 2 and produces the answer in the same sheet, in a single cell. I'd like excel to use each random number generated, input it into the function table, attain the answer from the table, and list it in the cell next to the random number.
View 1 Replies
View Related
Sep 16, 2012
Using Excel 2010 Pro.
I have a formula to create a string to create a lable for a Trial Balance report
=LEFT(I5&" "&REPT(".",95),101)
But the decimal portion of the string is distracting. I would like to make it a grey instaed of a black so that the text portion of the label "pops".
I don't believe I can do it with conditional formatting and I do not want to do it manually.I was thinking some kind of function or UDF like:
=LEFT(I5&" "&RGB(REPT(".",95),(191,191,191)),101)
View 3 Replies
View Related
Jan 4, 2014
I am working with Excel 2010 and I have a problem that I can't seem to figure out. I am trying to find the first nonzero cell and begin an average function from that cell and down through 29 rows (a total of 30 rows).
Sub average()
FinalRow = Cells(Rows.Count, 3).End(xlUp).Row
For I = 3 To FinalRow
If Cells(I,3).Value 0 Then
'I use the above to identify the first nonzero cell
ActiveCell.FormulaR1C1 = "average(........)
This is where I am stuck, I don't know how to code the first nonzero (which can change with different data) cell and average rows below it.
View 7 Replies
View Related
Jul 17, 2014
I think I need some version of a lookup for match function. Here is essentially what I need: I have a column of data points. I need to output every position at which the data point changes to a different value (going from top to bottom). See below for an example.
Here is some sample data (with column headers)
Position
Data Pt
1
-7.5
2
-8.0
[Code] .......
Here is the result I need (I want to put a consistent formula into the second column below that returns the results shown in the second column below):
split 1 position
2
split 2 position
4
[Code] ........
Is what I am describing possible? Because I am inserting this into a template, I ideally need to do this outside of any sort of VBA work.
View 2 Replies
View Related
Apr 29, 2014
I am looking for a function or a formula that performs the same process in Excel 2010 as:
Data Tab -> Sort & Filter -> Advanced -> *radio button* Copy to another location -> List range: A1:A100 -> Copy to: B:B -> *radio button* Unique Records only.
View 2 Replies
View Related
Apr 4, 2014
I get syntax error on return statement. I am using Excel 2010.
View 2 Replies
View Related
Feb 14, 2012
What I wanted was to a function, say, "=LastModifiedDateOf(CELL)", where CELL, is a parameter that indicates the cell I want to monitor. If the value of such cell ever gets changed, the cell containing the function has its value updated to the current date.
E.g.
A1 = "AA"
A2 = "=LastModifiedDateOf(A1)" -> "10/03/2011 10:30:32"
-- Make an update:
A1 = "BB"
A2 = "=LastModifiedDateOf(A1)" -> "14/02/2012 12:15:11"
I'm not quite a super user in Excel, but this function would be very very useful for me.
ps. I'm using Office 2010
View 4 Replies
View Related
Feb 20, 2013
I'm using Excel 2010. I have three columns. Column A contains the date, Column B contains the time, and Column C contains the date and time. Column C is the result of a formula, which adds Columns A and B (ie. =A2+B2). So I have the following...
Date
Time
Date and Time
1/15/13
9:00:00 AM
1/15/13 9:00:00 AM
1/15/13
9:00:05 AM
1/15/13 9:00:05 AM
[Code] ....
I'm using a defined name to store a date and time, which I'm going to use as a lookup value in a Match function. I've defined MyVal as follows...
1/15/13 9:00:20 AM
Excel automatically converts this to the following decimal number...
=41289.3752314815
When I first run either of the following two macros, they correctly return 5...
Code:
Sub test1()
Dim x As Double
x = [MyVal]
Debug.Print Application.Match(x, Range("C2:C10"), 0)
End Sub
Sub test2()
Dim x As Date
x = [MyVal]
Debug.Print Application.Match(CDbl(x), Range("C2:C10"), 0)
End Sub
However, when I save and close the workbook, and then re-open it, these macros both return an error. Is this some sort of bug in Excel?
View 9 Replies
View Related
Oct 16, 2013
I'm trying to get the Median (and min/max later) of certain values on a different sheet. For example, I'm operating in one sheet and pulling the values from the sheet "Form Responses". I want a median for numbers when the values in column B are "Aransas" and the values in column D are Yes. The values are in Column F. My formula is:
=MEDIAN(IF('Form Responses'!B:B="Aransas",IF('Form Responses'!D:D="Yes",'Form Responses'!F:F,""),""))
It's giving me the median for ALL of the numbers in F and I can't figure out why.
Following that, I'll want a median for all the numbers in F when column B value is "Aransas" (in other words, dropping the Column D condition).
Excel 2010, Windows 7
View 1 Replies
View Related
May 6, 2014
Using Excel 2010.
The background to this question is that I'm trying to replicate conditional formatting using a user VBA function, because I require a thick border around the cells (and the conditional formatting within Excel only has thin borders)
In the final function, there will be 9 combinations of formatting {Red, Amber, Green} interior with {Red, Amber, Green} thick borders.
The formatting is determined by a number in another cell (the "target" cell), which returns a value 0,...,8
I've only got as far as filling in the interior for the first combination, but the function returns an error "Application-defined or object-defined error".
VB:
Function VBA000_003_SetRAG(strTargetRange As String) 'strTargetRange is the reference for the target cell that contains the value 0,...,8
On Error Goto handler [code]....
There is not a problem with the target range, if I remove the two lines relating to rngCaller then the function works OKExecuting the rngCaller.Interior.Color = RGB(255, 0, 0) command through the Immediates window works OKI've tried calling a subroutine & passing the range across
Same errorChanging the range in the subroutine to an absolute range (eg. Sheet1.range("A1")) also causes the
same errorExecuting the subroutine on its own (with the absolute range) works OK
So my guess is that it's a bug/limitation with Excel VBA when trying to execute commands from inside a user function
View 3 Replies
View Related
Apr 8, 2014
I have two sheets. One is named sheet1 where all my data is and sheet2 where all my values are. I want to do a find a replace on column N (sheet1) using the data in sheet2. Column A on Sheet2 has all the values that are found in column N and column B on Sheet2 has what the data should change to.
So for example:
Sheet1 says the following on column N:
cat
dog
lion
bear
Sheet2 says:
Column A
A1: Cat
A2: Dog
A3: Bear
Column B
B1: 2
B2: 8
B3:15
I want the values on column N to be replaced with 2, 8, 15 and so forth. I use excel 2010.
View 1 Replies
View Related
Feb 14, 2014
I am using Excel 2010.
My objective is to pull specific values from an external file corresponding to the correct name and year of my choosing. The first way I thought would be best is to use an INDEX/MATCH function. The problem is the external files change names so I would need a method to easily change the source file name from one cell. I then stumbled across the INDIRECT function, but the INDIRECT function will only work when the source file is also opened. I then came across Harlan Grove's pull function which allows you to import data from closed excel files.
So, this is what my formula looks like: =INDEX(pull("'"&G12&"");MATCH(C15;pull("'"&G14&"");0);MATCH(D15;pull("'"&G16&"");0))
Cells G12,G14, and G16 contain the file paths for the ranges. C15 is name and D15 is year.
The problem I have though is that when I try to execute the function, Excel gets stuck. if the code cannot handle large amounts of data. I tested the code with a simple SUM function for a small range from an external file and it worked just fine.
This is the code I am using:
[Code] ...
View 3 Replies
View Related
Jan 22, 2013
I want to use the find function on a range and search for a "," character. If I get a "," I want to return a True otherwise false.
I tried just recording a macro using the find function but no code shows up.
how to use the find feature in a macro, can this be done?
excel 2010
View 4 Replies
View Related
Feb 24, 2014
I have four cells c1 thru c4. The SUM of these cells must equal 1. I want to display an error message "sum of cells not equal to 1.0" but only when data has been entered in all four cells.
For instance =SUM(C1:C4, "<>1") should display the error message. Is there a way to write the formula with an IF statement and still use data validation with error message box?
Assumptions:
-Will ignore blanks
-Error message "Sum of cells are not equal to 1.0" until values in all four cells have been entered, not after a value is entered in a cell. (validate expression after all four cells have values or blank)
Scenarios:
c1 = .25, c2 is blank, c3 = .25, c4 = .50 TRUE no error message should displays
c1 = .25, c2 = .25, c3 = .25, c4 = .50 TRUE no error message should displays
c1 = .25, c2 = .30, c3 = .25, c4 = .50 FALSE error message should display after value is entered in c4
etc
What I don't want is for the error message box to display after .25 is entered in c1 because the user is still in the process of adding values. Can this be done using Data Validation in Excel 2010?
View 2 Replies
View Related
Sep 11, 2013
I have researched a problem I am experiencing in Excel 2010 and have had problems in the past with previous versions. It seems that occasionally I run across a link that just refuses to break. During my research I have found some reports that this may be linked to the "Name" function. !!!Bing!!! the light comes on. When I copied my template to my new folder and tried to cut and paste data into it I got a message saying something like "A formula or sheet you want to move or copy contains the name "...", which already exists on the destination worksheet." and I opted to replace the existing name with the new one.
Apparently I created an external link at that point and one that cannot now be broken unless I delete my named range and start over the naming process. I mention this here because I see lots of people having this problem and the stock answer is to goto the Edit Links menu and attempt to break the link. Try as they and I might, this doesn't work. I also saw a post about it being a phantom validation problem and that too panned out although I can see how the validation could be linked externally and create this problem.
Does this accurately describes the issue and is there a remedy besides re-creating the "Names"?
View 1 Replies
View Related
Jun 11, 2014
I've recently been making a macro in visual basic that loops through all my excel files and replaces an old company name to a new company name and It's working great, well except for one thing... It always misses one string in the file and it leaves me with 90% of the file corrected. I think that the string might be a part of a merged range so Range.Find is not able to find it. I was suggested to use this code:
[Code] ......
Because that's the code that is generated when using the replace function in MS Excel 2010, but I keep getting syntax errors?
View 2 Replies
View Related
