I have a problem with the overreaction of my VBA function procedures. Basically, I have an input section with 1000 rows and 10 columns and an output section with 1000 rows and 40 columns. Each cell in the output section is with fomula:
View 3 Replies
In an Excel sheet, in A1 I have written B1 and in A2 I have written D20.
I have a data panel in B1:D20.
I would like to call a vba function my_Fun that acts on my data panel and returns a double in cell A3.
Instead of writing my_Fun(B1:D20) in A3 I would like to be able to write my_Fun(A1:A2), where the entries in A1 and A2 are called, rather than A1 and A2 being used to define the range themselves.
I need to get 5 small subs into 1 sub. (run the whole program by pressing once.
Please see attached file with codes : ServiceDriftMaaling.rar
My question is about passing arguments to UserForm procedures. I've created a Main procedure. Main calls a sub procedure and returns an array. Obviously Main can now use the array. But then Main calls a sub procedure to open my userform.
All of my userform sub procedures need to use the array I created in Main. Do I have to pass the array into all of my userform procedures? Into the UserForm_Show procedure? And then again into UserForm_Initialize? And then again into ComboBox_Change? I don't understand how to give my UserForm procedures the array from my Main procedure.
MS Query can only hold 16 joins and some other restrictive requirements. Has utilized Stored Procedures to execute a query and have the results returned to Excel? Query in Excel seems very limited.
View 2 Replies View RelatedI have a workbook with no data sheets showing with all actions being taken by the use of Userforms.
To make the workbook look consistent I would like to use a Userform rather than the standard MsgBox, the problem I cant seem to work around is passing the result of a Command Button back to the original proceedure.
I am trying to work along the lines of this but I am not sure where I am going wrong.
This procedure would call the Userform and then provide the next action dependant on the users choice:
Public Sub Testing()
Call TestMe
If Ans = Yes Then
Sheets("Sheet1").Range("A1").Value = "Yes"
Exit Sub
End If
If Ans = No Then
Sheets("Sheet1").Range("A1").Value = "No"
Exit Sub
End If
If Ans = Cancel Then........................
I've seen this done a lot while reading. I've also had help with some code by a few members that have suggested doing this.
View 8 Replies View RelatedHave a bit of code to list all procedures in project to a sheet. Idea from Chip Pearson, adapted with help from Norie. List procedures in module/project Reference is set to MS VBA Extensibility. Thought solved, but trying to use now only lists one procedure in the project Namely the procedure in the module identified here :
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("mdl_comp_pl_ProcedureList").CodeModule
I tried many variations by stopping at
Set VBCodeMod = ThisWorkbook.VBProject
But receive error message
Objest doesn't support property
Option Explicit
Sub raw_pl_ProcedureList()
'Uses reference to Microsoft VBA extensibility
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim Msg As String
Dim ProcName As String
Dim wbBook As Workbook
Dim wsList As Worksheet
Dim rngProcedure As Range
Dim intCounter As Integer
intCounter = 1
With Application
.DisplayAlerts = False
.Calculation = xlCalculationManual
. ScreenUpdating = False.........................
I received a suspicious email from <Excel Help/VBA Help: [forum@ozgrid.com]>. It may be nothing but I am wanting to inquiry about it's origin and content, how would I do this? I of course have received other emails from this site.
I need a formula to take away a percentage according the amount (number) ot procedures.
For example:
- procedure - price for one - 5 of it - 10% discount - final price
The idea is, for example, if I type amount of 5 procedures, automaticly to make 10% discount, if I type 10 procedure - make 15% discount etc.
I am running this code it is rather long, but I need to show it all ?
Code:
Public Sub Monday()
Dim mName() As Variant
Dim lName() As Variant
Dim fName As String
Call lower(CStr(mName(q)), fName, CStr(lName(q)))
[Code] .........
How to do is to pass the value of convert to the other procedures that are being called? As you can see I know how to pass variables down to "Sub-routines" but how would I pass the variable up?
I have a procedure in Access, MySub, stored in Module1.
To call it from Excel, I use the following:
Code:
Dim AccessApp As Access.Application
Set AccessApp = New Access.Application
With AccessApp
.OpenCurrentDatabase Filepath:="C:My location", Exclusive:=True
.Run Procedure:="MySub"
End With
Supposing for argument sake, there is another procedure also called MySub saved in Module2 in Access.
The above code will no longer work as it won't know which MySub to call.
How can I specify the correct one to choose? Of course I can rename the procedure in Module2 to something else.
I am trying to determine if it's possible to control the order of like events. In other words, if I have set up two Worksheet_Change events, can I control which one fires first? Or, as a second best, can I determine which one will fire first?
I am using Office XP on Windows XP, but I'm looking for a generic solution if possible.
If I'm starting in Sub #1 and I have turned screenupdating off, then later in the procedure I call a function that also has screenupdating off (and also back on at the end of the function), will I get a slight flicker here? I'm noticing a slight one, and I can only imagine that it's where the screenupdating is being triggered again. (removing the updating from the function isn't an option, because another procedure calls the same function & needs it turned off)
View 2 Replies View Relatedpassing variables within procedures on a worksheet. i have:
Private Sub lblGoToMaterials_Click()
'email to:
'----------------
'grab current position
myRow = ActiveCell.Row
mycol = ActiveCell.Column
and i would like to pass the values gotten in myRow and mycol to:
Private Sub cmdGoBackTo_Click()
Application.Goto Reference:="R" & myRow & "C" & mycol
what do people meen when they say target and how is it used from what i can gather its somthing to do with the active cell?
View 9 Replies View RelatedI've searched the web and OzGrid all day, and still have this question: what is the equivalent for .OnAction when using a ComboBox as shown in the code below. Everything else works as desired.
Loopcntr = 1
Set xyz = ActiveSheet. OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, _
Left:=Target.Left, Top:=Target.Top, Width:=Target.Width, Height:=Target.Height)
'''??? xyz.OnAction = "TDListing"
xyz.Object.BackColor = RGB(204, 255, 204)
Do While Loopcntr <= TaskListArraySizeHolder
DDholdName = TaskListArray(Loopcntr)
xyz.Object.AddItem DDholdName
Loopcntr = Loopcntr + 1
Loop
I have a value in a cell named subNo. whatever that value is I want that routine to be called. How do I concatenate Sub & the number?
Sub WhichSub()
Dim varSubNo As Integer
Dim mysub As String
varSubNo = Sheets("Main").Range("subNo").Value
Call Sub & varsubNo 'how do I concatenate this?
End Sub
When working in an userform, and you call another sub routine within the userform, will all variables passed automatically be byref (no way to do byval?)?
View 3 Replies View Relatedhow is the best method to link up forms to "talk" to my modules? (aka set variables inside my module script)
the way i understand it with my C++ background, it seems like separate forms and modules act much like private declarations do inside class calls.
I know that in C++ i can declare a friend class to grant access to communication between classes... and i'm wondering if such a procedure is possible in VBA as well...
or to break it down for those not familiar with C++, quite simply, is there a method of code i can implement to have different user forms and modules to talk to eachother? as it seems that any variable declaration is all inclusive to that one object.
The only way i can think of passing variables is by having my code create a worksheet, paste variables one by one into cells, bounce to the destination module, and import those declarations one by one through the cell contents, and remove the sheet after use...
I know i can do it that way, but i really dont want to as that is pretty darn sloppy, and a mickey mouse way to tie my program together.
I am trying to figure out a method for calling the ith number in an array that was defined in another function. The mean function is working and the result is (1 x variables) array. Then, I want to use that array in the sdev function. I am having trouble pulling the ith number from the mean function. Also, is there a way to make variables constant so that they do not need to be declared for every function.
Sub stats()
periods = Range("periods")
variables = Range("variables")
Redim X(periods, variables) As Double
Redim uX(variables) As Double
Redim sdX(variables) As Double
'Load Data
For i = 1 To variables
For t = 1 To periods
X(t, i) = Cells(4 + t, 2 + i)
Next
Next
'Calculate Mean (run 'mean' function')
uX() = mean(X)................................
I have been trying to use a multi-select listbox as the argument for a subroutine. For some reason, I keep getting a run time error, type mismatch. I'm baffled because when I define the specific listbox, it works fine, but when I attempt to pass the listbox I get an error.
Public Sub OtherDirectTotal(Expense As ComboBox, CostCat As ComboBox, _
Cost As TextBox, Years As Msforms.listbox)
Dim IDC As Double
Dim IDCt As Double
IDC = Val(UserForm2.txtIDC) / 100
If Years.Selected(0) = True Then
If Expense = "Sponsored" Then
If UserForm2.ChkODC = False Then
Select Case CostCat......................
I have CheckBox1 (.Value = FALSE) on the UserForm and when I run my sript ... this change Value to TRUE and run the subrutine Private Sub CheckBox1_Click. I need block this step with using VBA code.
View 4 Replies View RelatedI want to display a string to users. Normally I would do that using message boxes. But that would not give me enough control over the font size, and I want to display using font size 48.
I could have used global variables, but that is not a good coding way. The other way of doing that is using property procedures. I could pass a string variable to a Label control on the form and then could format it.
I have a userform with a combobox in it that's rowsource is a column of company names in a worksheet. When a company name is chosen, a combobox change private sub runs and many userform textboxes are populated with information about the company that was chosen from the combobox dropdown. This information is stored on a worksheet. I then want to edit any of the information in these textboxes. Once my edits are made I have a CommandButton that is pressed to save the edit changes. This CommandButton runs a private sub that disables the combobox (thinking this would prevent the combobox private sub from running), deletes the row that the information originated from, and then SHOULD make a new row of values based on the contents of the textboxes following the edits. The problem is that the CommandButton coding that deletes the row causes the combobox change private sub to run because the company that had been selected is now the missing from the rowsource; this causes an error.
View 8 Replies View RelatedLet's say I have a dataset with blank or X in column A, and a dollar value in column B. I want to sum the dollar amounts for column B only if there is a corresponding X in that row in column A. The way I currently do that is by creating a new column C, making a if statement to display the value of B only if there is an entry in A, and then doing operations on that new range. I figure there has to be an easier way. I don't know how to use VLOOKUP, despite trying to read the helpme a bunch of times. Is that relevant?
View 8 Replies View RelatedWhat I need to do is sort certain entries in longer list (column A, it is in .csv format and needs to be in it so coordinates and names and ID, all sorted with commas) and I have another list (column C) which is shorter list of certain IDs. I googled and tried and got some results for the basic structure but the fuction seems to fail. It doesn't matter how I get that third list done, but there is only one criteria: since the list in column A is really long and those entries need to keep the .csv formatting, the function should copy that info what is in the matching cells.
Let me try to put it simple: .csv cells from column A that have matching ID from column C should be copied to column B (or N).
the function I'm working with right now is
=IF(ISERROR(FIND($C:$C; A1))=TRUE;"0";A1)
I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
I have a userform that should return a global varable but when the global varable is used back in the program...it is 0 and not the number in the userform.
The varable I speak of is ForcedCurrent
Global declairation
Private Type TempData
LotNumber() As String
Site() As String
Macro() As String
ChainName() As String
ResistancePerContact() As Double
Index As Integer
End Type
Private Type Lot
Product As String
ParaOrDeft As String
ThermalOrStress As String
Hours() As String
Data() As TempData
Index As Integer
End Type
Dim LotData As Lot
Dim TestNameArray() As String
Dim NumberOfContacts() As Double
Dim ReedholmUnits() As String
Dim ForcedCurrent As Double ...
I have a spreadsheet that i update weekly. i need is a formula that will just sum the last four weeks. Example column A rows 1-52 will be updated each week in row 53 i just want the last four weeks to be summed.
View 5 Replies View Related
