I want to be able to call a procedure and pas 2 variables that it will need to it.
The code looks like this:
Private Sub UpdateScreen(StartLine As Byte, EndLine As Byte)
End Sub
UpdateScreen(104 , 140)
For some odd reason I get an "Expected: = "error when I enter the last line. I works if I send only one variable, but not when I send 2.
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........................
This error occured me, and i clicked "Debug" to see where was the error and i found a strange thing that i don't know how to solve it:
The line has the following
I am calling a procedure from a commandbar button using on action.
With oButton
.Caption = "&Progress Report"
.OnAction = 'this bit please
.BeginGroup = True
.FaceId = 576
End With
Private Sub ProcessingRequest(ByVal shtRequest As String)
End Sub
I have some VBA that appears to work perfectly on the first run, but I get the following error if I run it again or any alternate runs;
Title: Error (App_WorkbookBeforeClose)
Number: 5
Message: Invalid procedure call or argument
The only way my code currently works is if Excel has just been launched. The VBA creates, saves and modify's files so I imagine it has something to do with the ThisWorkbook object when a file is being saved or closed. I have googled the error but didn't really get anywhere as it is pretty generic and seems to be returned for a number of Excel products.
When I step-through my code below, it always opens the first file in the directory "C:Pyramid Files", but when it comes back to the Pyramid Files sub after fully processing the first file via various other subs, the VB Editor apparently doesn't like something about this line: StrFile = Dir(), since it quits after "snapping-back" to the previous sub Initialize(). I have also tried StrFile = Dir, but that doesn't work either. I did Dim Strfile in the General Declarations. When I set Watches for Dir and Dir(), I get the value "Invalid procedure call or argument" for both, as if the directory function lost the value. I can't determine why this is happening.
VB:
Dim WSM As Worksheet, WSB As Worksheet, WS1 As Worksheet, [U]StrFile As String[/U], StrDirectory As String, ClientCode As String
Dim Filename As String, LastRowb As Long, LastColB As Integer, LastRow1 As Integer, NextRowC As Integer, x As Integer, y As Integer
[Code] .......
This error message in line vpp:
Invalid procedure call or argument
Code:
Function fn1(ByVal a, ByVal i, ByVal e, ByVal N, ByVal w, ByVal ta)
Pi = Application.WorksheetFunction.Pi
mhu = 398600
vpp = (mhu / Math.Sqr(mhu * a * (1 - e ^ 2))) * (-Math.Sin(ta * Pi / 180))
fn1 = 2 * vpp
End Function
Code:
Dim LR As Long Dim Dash As Long, _
Whole As Double
Dim pi ', WorkSheets, Range
WorkSheets("Sheet3").Select
LR = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row
[Code] .........
I am using a procedure to loop through all files in a folder and am encountering an
Invalid procedure call or argument, error on the ChDrive line:
mypath = "\Eo1
_iSurveysNALCReturns"
ChDrive mypath
Is it something to do with the mypath syntax? Recently our drives have been reorganised. I used to just use "G:Surveys..." which always worked. Does anyone have any thoughts? I hope I've explained properly - you can probably tell I don't know much about this sort of thing.
I would like to apply different conditional formatting at different times with a click of a button. I setup a dummy and turned on the recorder and recorded this
Range("A7:N7").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$C7=1"
Selection.FormatConditions(1).Interior.ColorIndex = 37
Range("A1").Select
End Sub
I tried changing to this
'/Conditional Format - OTHER EXPENSE B/L
Set rngConditional = wsData.UsedRange
With rngConditional...............
I have the following formula which works fine in this form:
View 4 Replies View RelatedI am coding a spreadsheet that makes extensive use of the excel dropdown list boxes. So I have codes such all over and it is not a neat way to code.
Code:
With Target.Validation
.Delete
.Add xlValidateList, 1, 1, Formula1:="1, 2, 3"
.InCellDropdown = True
.ShowInput = True
End With
As many of the dropdown list boxes are similar in nature, with the only exception that the list content is different, I wanted to code a sub routine to include the code above.
My subroutine looks like this now:
Code:
Sub listbox(cellref As Range)
Set Target = Cells(cellref)
With Target.Validation
.Delete
.Add xlValidateList, 1, 1, Formula1:="1, 2, 3"
.InCellDropdown = True
.ShowInput = True
End With
End Sub
The problem is that when I call the subroutine with a
Call listbox (10,10)
It kept giving me a compile error.
I would like to create a listbox at cell row 10, column 10 of the worksheet.
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.
I have a userform that has a number of cascading comboboxes, however i wish to use the same cascading feature in a number of other userforms. So what i am trying to achive is calling the cascading element from a public macro, to do this i need to pass the userform name to the procedure. The snippet of code i am using is:
Form:
Private Sub ComboBox1_Change()
If ComboBox1.Value <> "" Then
myform = Me.Name
Call Cascade1(myform)
End If
End Sub
Module:
ub Cascade1(myform)
myform.Label2.Visible = True
myform.ComboBox2.Visible = True
myform.ComboBox2.RowSource = Range(myform.ComboBox1.RowSource).Cells(myform. ComboBox1.ListIndex + 1, 1)
End Sub
I have a procedure that I want to pass two arrays to. When I pass a single array it works fine but as soon as I try to pass two arrays I get a syntax error. Both arrays are declared exactly the same way and are used in the same way. Am I limited to passing only one array per procedure?
View 8 Replies View RelatedI want to put some colour in the cells B2, B3, B4, B5, etc and a put black the edge of every box. For do that i make a subrutime that put some color in the range that i want (only if i write "B2" or "B2:B5"), but if i want to put a black edge in all the box i can't do "B2:B5". Then i tried to do something like:
For i=2 To 5 Step 1
Call Boxcute(ActiveSheet.Cells(i,2))
Next i
Sub Boxcute (Box As String)
Range(Box).Selection
etc.
End Sub
But Excel gives me a error with Box and Range.
Is it possible to call a stored proc from excel and pass through a paramter?
If it is would anyone have a clue how to do this?
I need to pass range information (eg. WorkSheets("abc").Range("A1") as text or ??) from a cell (ie the above text is in a cell on some worksheet, say "XYZ") to a VBA procedure or could directly use it in the procedure, similar to...
Sub MyProc(RngInfo as string [or?])
dim RRng as range
Set RRng= RngInfo ??? it is this part I'm really not sure how to do
where RngInfo is a worksheet that has a "named" cell that contains the above cell with the indicated range in it.
eg. RngInfo is worksheets("XYZ").Range("D1") where D1 contains the text (or ?)WorkSheets("abc").Range("A1")
I've only indicated these as text items because I'm not sure what/how you would do this. The end purpose of this is to pass variable Range information from a cell on a WS either into (or use within directly) a VBA Sub. I guess I could pass the WS and Range Address as a single string separated by a "Char" and use Split to separate them and then recombine using Worksheets(Parm(0)).Range(Parm(1)) but it seems like it should be easier than that.
Have a spreadsheet for creating employee schedules (any number of employees, 3 rows per employee, 5 columns for each day). I want to move all employees' info for one day (5 columns and XX rows) to another area and then clear the employees' data for that day. I have the code working for Monday but I don't want to duplicate it 6 more times for Tue -Sun. I have set up named ranges (i.e. MonFT, TueFT, etc.
I would like to call one subprocedure to do this and pass parameters for each day so the ranges can be changed to reflect the desired day?
I am doing this for holidays; i.e. if there is a Wed. holiday, store all of Wednesday's data and clear Wed. Then restore all Wed data when preparing the schedule for the following week.
im having trouble with an IF argument as you can see from example the data sheet is where i need the result,the first formula i have done in B24 but cant understand why the 2nd argument will not go in and i keep getting a value error as shown in C24.
i need say If in the sunday tab G8="A1"then insert G32.Then do it for "A2"=G32 and so on.all in Data B24
Is there a shorter way as i have to go up to A9 B9 C9 D9.
I have a sheet that has 2 fields that i need a yes/no answer from. Basically if b = "P1" then a should be less than or equal to 4 to reply good otherwise it should reply no. but if b isnt "P1" but instead is P2 P3 or P4 then it should => 10, 30, 40 respectively.
View 8 Replies View Relatedhow to create a function argument containing my own defined formula?
View 9 Replies View Relateddoes IF in excel formula take 27 Arg and ( AND, OR) in IF ststement take the same Arg
and does SumProduct Take the same Arg .
Is it possible to create an IF argument based on the filename?
Example - A2=IF(Filename="template",A1,Filename)
Reason - A1 is the result of a MAX function on the template WB but once template is saved as a different file name I want A2 to = the new filename?
formula to calculate the commission based on 9 cases each case is subjected to 4 differant arguments.
here is the formula for the first case (the answer if true 1.4) & the last case( if all the casses are fails it should show 0.6) u will find it in cell K5
=IF(AND(E5>=20,H5=1,I5>=80,J5>=80),1.4,0.6)
this is only 1 case HOW can i calculate it basd on 9 casses??
I'm fairly new to VBA (exactly 8 hours of paid experience ) and I've stumbled upon the following.
Say I wanted to select a certain range of cells (which later have to be merged, don't ask why, they just have to. It's in the job description and it's not in my power to change things around here).
When I try a MATCH function, I substituted the range through the ADDRESS function. But, it returns #VALUE error. When I manually typed the same range address, it produces the results. I've been behind this more than 8 hours still I can resist the heat on my cool head! But, I thought you coolest head guys need some heat to warm up for to-day's challenge.
View 9 Replies View Relatedi have a calendar with the dates in a row
I want to count instances of an entry under the dates using 'countif' from 01JAN until today.
My problem is that today changes value [up by one] each day so the 'countif' function will need to extend its range to the right by one cell each day.
I am trying to write a macro that has multiple if arguments.
An example is as follows:
If cell b11 had fill colour red, then colour fill cell B8 green,
If cell b11 had fill colour green, then colour fill cell B8 red,
If cell b11 had fill colour yellow, then colour fill cell B8 blue,
If cell b11 had fill colour blue, then colour fill cell B8 yellow,
end if.
There are 100 loans, each one of them need to be tested on 11 different tests, the results are either "pass" or "fail". All the test results are stored in the Boolean array. g_sel_tag(). For instance, if g_sel_tag(2,7) = true that means the 2nd loan has failed on the 7th test.
But I got stock with the following code, I think there should be a loop for the public variable rep_num in order to loop through the 11 test results for each loan. I have searched the entire project but did not find any value was assigned to the variable rep_num. The following code is part of a huge project, I can't test run it to see if the following code is valid at this point.
I am new to VBA therefore wondering if it is valid with no specific value assigned to rep_num. (I think there should be something like "for rep_num = 1 to 11" in order to get it running).
There are several codes in the project with similar structure where they just use rep_num without assigning any specific values.
Code:
Dim rep_num As Interger
Sub test_results_reporter (rep_num As Interger)
Dim i As Interger
Dim g_sel_tag(1 to 100, 1 to 11) as Boolean
For i = 1 to 100
If g_sel_tag(i,rep_num) Then Msgbox "Loan" & i & "has failed on test" & rep_num
Next
End sub