I'm trying to calculate a gaussian random number given a mean and standard deviation. I then want to throw away any numbers beyond the min or max, and replace it with a new one that fits. I have made the macro below:
Public Function RANDGAUSS(mean As Double, stndv As Double, min As Double, max As Double) As Double
Dim x As Double
x = NormInv(rand(), mean, stndv)
Do While x < min Or x > max
x = NormInv(rand(), mean, stndv)
Loop
RANDGAUSS = x
End Function
I'm new to macros, but what I did was simply tools>macros>visual basic editor, and pasted the above code in.
When I enter the formula into a cell, it brings up the Visual Basic Editor and gives a warning:
"Complie Error: Sub or Function not defined" and the first rand is highlighted.
when you type in a builtin function in the formula bar such as =DAY(), a small callout box appears below telling you the syntax - in this case "DAY(serial_number)". Is it possible to achieve this for a user defined function?
I'm using this macro with no problems in another workbook. However after inserting the same code into another workbook, I get an errorfor sub/function not definded. Looking at the code it seems its highlighting what's in red below.
I've inserted the top section of the code, where I'm getting the error.
I am getting "Sub or Function not defined" error in the below place of my coding. I have highiglided the code which system did and this was given by one of our commite member only
[Code start here] Windows(Fname).Activate If Not WorksheetExists(Date1) Then GoTo ABC Else
I have a sheet called 'frontsheet' where users complete a number of fields and a second sheet called 'table'
When the frontsheet is complete, users press a button to submit the data to the table, it checks the fields are complete and submits the data - all this works fine.
However I would like to add a mechanism to check the first entered piece of data (asset number) against all the asset numbers already entered on the 'table' sheet.
I thought I could use a dcount to do this but after trying a multitude of methods I get the same error "sub or function not defined"
Code: Private Sub CommandButton1_Click() 'Dim ta As Worksheet 'Set ta = Worksheets("Table")
'Dim fs As Worksheet 'Set fs = Worksheets("Frontsheet")
[Code] ........
As you can see I've tried a few ways and have a few 'remmed'* out lines. I'm starting to suspect the problem lies elsewhere, should I have the dcount happen without the need to click a button and how would I do that?
this returns a "sub or function not defined" on the printout line
Sub print_All_recaps() Set ws = Worksheets("Employees") If UCase(InputBox("Enter y to print ALL the records")) <> "Y" Then Exit Sub For n = 2 To ws. Range("A65536").End(xlUp).Row Cells(2, 2) = ws.Cells(n, 1) PrintOut Copies:=1 Next End Sub
If I remove the PrintOut line I do not get an error.
Have two worksheets in same workbook. First worksheet is "ReArrangedAddr" Which basically has a command button to click to run a "Sub" behind the second worksheet "Orig SH Register". When I click on button on first worksheet, I get error "Compile Error: Sub or Function not defined"
Function Allocation(LOB As Integer, CostCenter as Integer) As String If LOB = 00 And CostCenter <> 8300 Then Allocation = "Yes" ElseIf LOB = 1, 2, 3, 4, 5 Then Allocation = "No"
I have created a custom add-in with custom functions and am trying to make it user friendly for other users. By running the following macro I can add functions to different categories, but I was wondering if there was a way to do this automatically so that anyone that adds this add-in will have the functions show up in the proper category without having to manually run this macro.
I have a two set of Macros in a workbook. One is to create a command button on a sheet and other macro will run onece the created commond button is clicked.
Macro runs fine. Command button gets created, but when I click the command button to run another macro it gives error "Compile Error: Sub or Function not defined" highlighting the code "Call Add" at the code entered for sheet. This code is added by macro in the sheet1. I am attaching a sample file as well as codes.
Sub CreateButton() Dim Obj As Object Dim Code As String Sheets("Sheet1").Select 'create button Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _ Link:=False, DisplayAsIcon:=False, Left:=600, Top:=30, Width:=100, Height:=35) Obj.Name = "InsertInvoiceButton" 'buttonn text
I have a user defined function that somehow keeps interfering with my other module. I use f8 to step through module 1, but halfway through the process it skips to module 2 where I have the below user defined function...
I am stepping through a sub in module one that for some reason keeps jumping to a user defined function in another module. I've checked and and the function isn't being called.
I just hit F5 and use it in Excel with no problem. This time a person who developed the function had me install it as Add-In. I do not know if it is supposed to give any advantages (versus simply typing function in VBE window + activating it by hitting F5 key), but the function works extremely slowly. Is this Add-In
I'm using the following UDF to extract URLs from hyperlinks on a sheet, so that people can click the hyperlinks, but the URLs for all the links also appear (in the cells with formula =GetUrl), but in a place where they're accessible but out of view:
I have created the following defined function in excel VBA but i get the complie error:-
'Block if without end if'
This this the code I have written for the function:-
Public Function Rule_45(Current, Previous, Result) ' RED: 'Decreasing Trend' OR 'Same' AMBER:'Increasing Trend' GREEN:'>83%' If Result = "-" Then Rule_45 = "-"
I have a function in VBA of the type. Function MyFunc(Indx As Integer, k As Long, Rho As Range, A As Range) As Variant .... End Function
which is called as a user-defined function from within the Excel worksheet. When called with the last two arguments being a range (i.e. Result = MyFunc(1,98,A1:A2, B1:B2)) it works fine. However, when I try to directly use an array constant instead of a range (i.e. Result = MyFunc(1,98,{10,11}, {20,30}), it returns a #VALUE error.
I thought I could fix it by redefining the last two arguments as arrays of type double, but this didn't work either (i.e. Function MyFunc(Indx As Integer, k As Long, Rho() As Double, A() As Double) As Variant .... End Function ).
I created an addin that puts a formula in a cell to make use of a UDF in the addin. It works fine.
But when I make changes to my addin, save it as new name, load the new addin (same as the old but with new code for other things) and then open the workbook that had the UDF working, it now changes from
=customUDFfunction(1,2,3) to ='C:Documents and SettingshomeuserApplication DataMicrosoftAddInsAddinV123.xla'!customUDFfunction(1,2,3)
The code I'm using to create the formula in the cell is
Cells(x, y).Formula = "=customUDFfunction(1,2,3)"
I'm assuming that when I create the formula in the cell it gets tagged somehow with the addin name and location. How to just put the formula with keeping it specific to the addin that created it?
A client has a registration workbook for each student which contains 3 types of worksheets, A) Transcript B) Registration and C) Grade sheets. Each file has only 1 Transcript sheet and 1 to many pairs of Registration and Grade sheets.
I have a GPA user defined function which obviously can only be used on a Grade sheet. I would like to return an Error if the user trys to use the udf on a non Grade sheet.
Currently I am just returning 1/0 but the smart tag returns #Name. Can I control the text displayed somehow?
The below macro(found in VBA and MACROS for Excel) works well but I need a few modifications
Option Explicit
Function SortConcat(Rng As Range) As Variant 'Rng —The range of data to be sorted and concatenated. Dim MySum As String, arr1() As String Dim j As Integer, i As Integer Dim cl As Range Dim concat As Variant On Error GoTo FuncFail: 'initialize output SortConcat = 0# 'avoid user issues If Rng.Count = 0 Then Exit Function 'get range into variant variable holding array ReDim arr1(1 To Rng.Count) 'fill array i = 1
1)My array is 128 cells wide(AT3:FQ3) and i get a type mismatch--seems to work well for 36 cells
2) These cells contain dates and numbes---all i want to sort and/or display is the numbers Note: some numbers are in parenthesis and other are either single or double digits. Example: numbers are (8/8),8,10,(HG6),4,6,etc. And Dates are 04/06 format (no year, and no parenthesis)
3) All empty cells in the array show up in the above SortConcat function as zero, and are un-necessary
4) The final output should be no more than 6 concatenated numbers, so column width is not an issue, and the bubble sort is really not necessary but would be a nice feature
Participants has 1 chance to win but can achieve the win in any of the 2 months.
I have attached a sample for a better explanation.
My issue is that I need to track only 1 win from either month 1 or month 2 from cells f & g. My formula works like so: If a YES is in cells f5: f8 it will calculate 45 days out from cells b5:b8 into cells g5:g8 or if a YES is in cells h5:h8 it will calculate 75 days out from cells b5:b8 into cells i5:i8. Right now I have the cells in g5:g8 & i5:i8 formatted to show just month and year with the column header titled " pay on the 15th of ", because I can't figure out how to have excel properly calcuate the 15th of each month from cells b5:b8 due to the various days in each month. If someone can help with this that will be OK too.
But my real problem is when there is a YES in cells f5:f8 I need cells h5:h8 to say "N/A" and cells i5:i8 to say "No Pay", because they can only have a win one time. However if cells f5:f8 say "NO" then cells g5:g8 will say " No Pay". Next cells h5:h8 will be evaulated for a "YES" and if a YES is in cells h5:h8 it will calculate 75 days out from cells b5:b8 into cells i5:i8. Last column j5;j8 will evaluate a yes from either cells f5:f8 or h5:h8 and if a "Yes" is found j5:j8 = "$25.00", if "No or N/A" is found j5:j8 = " N/A"
I am presently trying to debug a large and repetitive piece of code and find that I keep ending up in a function within the same module. I assume this is because the function is being triggered as I manipulate data. If this is the case, does anyone know how to suspend the function while I debug? I guess I could comment it out as I do not need the data it is returning but during normal running, this is not possible and I expect if it is fired off then it will slow things down considerably if my code keeps getting interrupted by interreupts from the function.
