Passing Boolean Variable To A Function
Nov 25, 2008This might be a dumb question but is it possible to pass a boolean variable as a parameter to another function?
I have the following code that produces some compile error ("expected ="):
ADVERTISEMENT
Type Mismatch '13' On Passing Function Result To Variable
Aug 11, 2006I have the following codes in my spreadsheet, they are pretty much the same and both used to work perfectly. However, now the first code returns a Type Mismatch '13' on line beginning mynum= and the second one works perfectly.
Sub addtasks()
Application.DisplayAlerts = False
myrow = Cells. Find(" Total P&C Estimate").Row - 3
mycell = Cells(myrow, 2)
mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
.EntireRow.Copy
.EntireRow.insert Shift:=xlDown
End With
Application.CutCopyMode = False
Cells(myrow + 3, 2) = "Task#" & mynum
Application.DisplayAlerts = True
End Sub
Boolean Variable Initial State
Jul 15, 2009Are Boolean variables ALWAYS initialised as FALSE when they are created?
I am wanting to leave a 'marker' in a function so that I know whether it is the first time it has been used or not. Something similar to this:
Excel 2007 :: How To Pass Boolean Variable Between Two Macros
Feb 10, 2012I am attempting to pass a boolean variable between two macros. The SUBMIT macro calls the STATUS macro to do some verifications and the STATUS macro should return the boolean variable RDY back to the SUBMIT macro, but does not.
I am using Excel 2007. Both macros are in the same Module. The STATUS macro is called from several places in the code.
Public Rdy As Boolean
Sub Submit()
[code]
Call Status
If Rdy=True then msgbox("You have completed your form") else msgbox ("Not ready")
[code]
end sub
[Code]....
Boolean Function To Return Whether PC Is Connected To Internet?
Jun 6, 2013I want a boolean function to return whether the PC is connected to the Internet. How to start creating this.
Reason:
I have code that checks an online database and downloads data after the user presses a button.If the host computer is not currently connected to the net, I would like to display a message of my choice instead of trying to run the code.To determine whether to run my message or not, I need a function that will return TRUE/FALSE if the host PC is net connected or not.
Passing Variable To Two Macros?
Jul 25, 2012I have the following code in 'ThisWorkbook':
VB:
Private Sub Workbook_Open()
Call Meetdata
End Sub
It calls this macro which is in a standard module:
VB:
Public firstvariablename As String
Public secondvariablename As String
Sub Meetdata()
regionname = InputBox("Enter the name of the Region.", "Region Name: North, South, East, West")
meetdate = InputBox("Enter the date of the Meet.", "Date of Meet")
End Sub
This set-up should make the variables available to all the other macros in the workbook. I have two other macros that need to use the values stored in these two variables that are entered into the two 'InputBox' statements. These two macros are in the same module and follow the 'Meetdata' sub. When I run the first macro, it recognizes the variables. When I run the second macro for some reason the variables are not recognized. To test the values returned by the variables, I placed the following code at the end of the first macro and again at the beginning of the second macro .
VB:
MsgBox(firstvariablename & " " & secondvariablename)
At the end of the first macro, the MsgBox returns the correct values stored in the variables which means that when the macro completes its run, the variables still hold their values. When I run the second macro with the MsgBox at the beginning, the Msgbox returns a blank. Somehow, the variables have been re-set to a null value. I can't figure out why the variables have been re-set to null. [URL]
Passing Variable From Userform
Jan 30, 2010I am using the following code to open a userform. Once the userform is open, the user has the option to choose a continue button or a cancel button. If the user is choosing continue, the code is not working (it always skips to the "else" portion of my "if" statement. I can't figure out why it is doing this.
View 4 Replies View RelatedPassing Value Of Of EndXLUp To A Variable?
Mar 11, 2013Dim lngrow As Long.
i want the above variable to hold the value of endxlup . so for example :
lngrow = Cells(.Rows.Count, "A").End(xlUp)
Why doesnt it hold the # of the last cell with data in column A ?
Passing A Variable To A Subroutine
Dec 23, 2007My code defines a variable: newrow. Then I call a subroutine using the Call command.
The subroutine does not recognize newrow.
It says it has value zero. How can I pass the value of newrow on to the subroutine.
Passing A Variable Between Workbooks
Oct 22, 2006I have two open workbooks, each having a single worksheet which includes a command button. The VBA code for each button performs tasks on its worksheet, then activates the other (inactive) workbook. I want to set the value of a variable in the button_click code in Workbook1 before activating Workbook2, and then use the value of the variable in the button_click code in Workbook2.
Declaring the variable as 'Public' doesn't work because the variable scope remains within its own workbook project. I must use two workbooks rather than two worksheets in one workbook.
Userform Not Passing Through 1 Particular Public Variable?
Aug 20, 2014I have a userform that is called within a sub in module. I declared a public string, "divisonb", in the module. When the userform's ok button is clicked, I define the public string through a "select case" method.
At the end of the private sub for the ok button click I have this:
[Code].....
a message box comes up with the correct string for divisonb. After the sub ends and it returns to the module I have the following:
[Code] ....
When this message box pops up, it is blank. Somehow, divisonb was redifined as blank within that 2 lines of code. All my other public strings are returned to the module with their correct values.
Passing A Private Variable To Another Module
Nov 25, 2008I have some code in which I need to pass the value of "j" from one module to another. "j" is declared in Sheet1 (Data) under the Microsoft Excel Objects.
View 8 Replies View RelatedPassing Variable Value From Private Sub To Module
Aug 12, 2009I am trying to pass a variable called "Filter" from a Private Sub to a Module but keeping coming up with a zero value in the Module. I tried to make the variable Global but that didn't seem to work.
Private Sub Code as follows:
Passing A Variable Into A Different Piece Of Code
Mar 6, 2009I have a spreadsheet that has several buttons on that run code using the cells around the code for parts of the data. eg
ColumnA1 ColumnB1 ColumnC1 Button1
ColumnA2 ColumnB2 ColumnC2 Button2
I want the buttons to run a seperate piece of code to start, but then all buttons run a similar piece of code at the end. I am hoping to be able to pass a variable from the first piece of code each button runs into the section of code that all share (which I have done as a seperate macro)
Passing A Variable On OnChange-event
Aug 8, 2006I've been trying to pass a variable from the OnChange- event on one sheet to a macro located in module one, but it seems I cant (or havent figured out how anyway). Anyone knows how to do it? The code here give me the error "RefreshSetup(WhatSheet) cannot be found"
Alternativly, maybe someone can come up with a better solution on my entire problem. 3 sheets with webquery. All should be handled in the same way (copy-pasting only), but source and destination-sheet differs. My big problem is if two queries are done at the same time, they conflict with eachother, and data from one query is copied on to the wrong sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Running Then
RunWhen = Now + TimeSerial(0, 0, 1)
WhatSheet = "HtmlTmp"
Application.OnTime RunWhen, "Module1.RefreshSetup(WhatSheet)"
Else
RunWhen = Now + TimeSerial(0, 0, 3)
WhatSheet = "HtmlTmp"
Application.OnTime RunWhen, "Module1.RefreshSetup(WhatSheet)"
End If
End Sub
Module1:................
Passing A Range Of Strings To Vba Variable
Apr 3, 2007I'm trying to pass a vector with in Strings (words) to a VBA variable, but something is not working.
That's how I'm writing: ...
Run Time Error 13 Passing Cell Value To Variable
Nov 28, 2006I am having a problem with a run time 13 error on a variable assignment. The variable is being assigned a value from a cell that contains a formula, and I am suspicous that perhaps the mismatch is coming thru this. The de-bugger stops on:
Area = ActiveSheet.Cells(count + 41, 8). Here is the complete code (with all my poor coding skills!)
Sub Reservoir()
Dim Inflow As Single
Dim Withdrawal As Single
Dim Evap As Single
Dim Area As Single
Dim Spill As Single
Dim Stor_1 As Single
Dim Stor_2 As Single
Dim count As Integer
Dim max As Integer
max = ActiveSheet.Cells(9, 4)
For count = 1 To max * 12
Stor_1 = ActiveSheet.Cells(count + 40, 11)
Inflow = ActiveSheet.Cells(count + 41, 4)
Withdrawal = ActiveSheet.Cells(count + 41, 6)..........................
Calculation Error After Passing Values To Variable
Jan 30, 2008I currently have a userform which inputs data into two cells, the third then multiplies these two cells together, which is all honky dory. It works, 2 x 2 = 4.
But when i put 0.6 for example it really doesnt work. 0.6 x 25 = 25. WRONG. My code in the macro is fairly long winded but will paste anyway.
If ComboBox2.Text = "Pounds ()" Then
Sheets("Claims").Range("G16") = TextBox2.Text
Else
Sheets("Claims").Range("F16") = TextBox2.Text
End If
Sheets("Claims").Range("B16") = ComboBox1.Text
Sheets("Claims").Range("C16") = TextBox1.Text
Sheets("Claims").Range("B16") = TextBox3.Text
Sheets("Claims").Range("D16") = TextBox4.Text
Sheets("Claims").Range("E16") = TextBox5.Text
Dim a As Long
Dim b As Long
Dim answer As Long
a = Sheets("Claims").[E16]
b = Sheets("Claims").[F16]
answer = (a * b)
Sheets("Claims").[G16] = answer
Passing Word Variable To Excel In Office 2010
Apr 2, 2013I'm trying to pass a variable from Word to Excel. Basically I have a Word document with a plain text content control in it. I'll have users populate this field. I know how to create a reference to that content contol in Word VBA that'll tell me what's in that content control (eg.
Code:
MyWordVar = ActiveDocument.ContentControls(1).Range.Text
).
What I can't figure out is how to pass the value of "MyWordVar" to a variable in Excel.
Retain Leading Zeros Passing Variable To Cell
Mar 14, 2008I want to know how to index a drop down list to be used for calculations.
Up until now I was using
Range("B1").Select 'the cell where the drop down list is
ActiveCell.Formula = SiteID 'site id
But for whatever reason it trims a trailing zero from SiteID when setting it in the second line. SiteID is a VBA string.
Indexing it would work, as would preserving the zero so help on either one is appreciated. Auto Merged Post Until 24 Hrs Passes;Correction, I lied. I was using this line to set it.
Range("B1").Select 'the cell where the drop down list is
ActiveCell.FormulaR1C1 = SiteID 'site id
Passing Range To VBA Function?
Feb 7, 2014It seems to work. Are there any problems with it? Is there a better way?
Code:
Function myRangePassExample(RangeA As Range) As Double
Dim ArrayA As Variant, ArrayB As Variant
Dim iLo As Integer, iHi As Integer, i As Integer
Dim jLo As Integer, jHi As Integer, j As Integer
Dim Sum As Single
ArrayA = RangeA.Value
iLo = LBound(ArrayA, 1)
iHi = UBound(ArrayA, 1)
[code].....
Passing Cell Value Into VBA Function?
May 6, 2014I want to select a cells value from a Data Validation Box ( Achieved ) then based off of that selection ( value ) pass it into a slicercache, as follows :
Select a value from a drop down list in cell A1 Based on selection update slicercache by passing the value of A1 to it
So far i have : The bold is the part where i need to replace with whatever is selected from cell A1. Must be possible ...
With ActiveWorkbook.SlicerCaches("Slicer_Contract_15")
.SlicerItems("BRUTE").Selected = True
End With
Would be best if this code ran on cell A1 click ...
Passing Multidimensional Array To Function
Jul 15, 2014I'm trying to pass a multidimensional array to a function that I have defined, but I receive an error about an object mismatch. Here is what I have in regards to the array and function. What should I change?
Code:
Dim diffArray() As Integer
Dim countArray() As Integer
Redim countArray(count,2)
diffArray = getRunningSum(countArray)
End Sub
Public Function getRunningSum(ByRef countArray() As Integer) As Integer()
'Code here......
End Function
Passing Worksheet Name To Function (Run-time Error '42')
May 13, 2009I have a function which i am using as part of a macro. The macro itself works fine and locates a search string i type into an input box across ALL worksheets in an excel doc. However, i have a function which takes the results and prints it to the front page, but when i try to use the worksheet name which has been passed to the function, i get the error: Run-time error '42': object require. this happens when i get to a line of code which says
View 2 Replies View RelatedPassing An Entire Dimension Of An Array To A Function ..
Jul 9, 2009Passing an entire dimension of an array to a function....
View 13 Replies View RelatedObject Required Passing Range To Function
Sep 28, 2007I will post my code and point out where the error occurs.
Sub GetProd()
'Averages Daily Production over the month
Dim WellRange, MonthRange As Range
Dim Month_ As Integer
TotWells = Sheets("R").Cells(1, 2)
TotMonths = Sheets("R").Cells(1, 4)
Month_ = Sheets("R").Cells(2, 4)
For Flag = 1 To TotWells
Set WellRange = DefineWellRange(Flag)
WellRange.Select <-----Used this To check If Range gets passed back properly.
For c = 1 To TotMonths
Set MonthRange = DefineMonthRange(Month_, (WellRange)) <---Error Here
Next c
Next Flag
End Sub ...
So the error occurs in the main "GetProd" Sub at the point when it calls the DefineMonthRange Function, its a 424 "Object Required" Error. Just before this is called I have a "WellRange.Select" which I was using to make sure that WellRange is in fact a range, and it does select the appropriate area.
So My question is of course, why I get an object error even though I am passing a range to a function which is expecting a range?
Different Interpretations Of Zeroes And Blanks After Passing To Extra Function
Feb 13, 2014see the attached. The array formulas in B2:C3 are operating on the table in E2:F5. These are quite simple formulas as you'll see.
The formula in B3 is identical to that in B2 apart from the fact that the first array is wrapped in an IFERROR (there are actually no errors in the array, but that's not the point).
As you can see, the formula in B2 evaluates E3 as ="", and so the corresponding entry in F3 is ignored in the sum. Quite normal.
However, the identical formula, but with an IFERROR first acting on Array1 now evaluates the same cell as <>"", and so F3 is this time included in the sum.
What action has the IFERROR had on this array? Clearly, E3 is not an error, so a "blank" should still be passed, unchanged, to the function.
I have included identical versions of the formulas, but with Named Ranges replacing the actual range references. The reason is that, with these versions, if you step through the formulas with Evaluate Formula, for example with the formula in C2, you can see that Excel "temporarily interprets" the blank in E3 as a zero (this is nothing new, though I've never fully understood why it can't display a "" here as well) before then equating this "zero" as being a "blank".
Perhaps somehow, after being passed to the IFERROR, this "zero" is processed so that, when passed on, even though it still “displays” as a zero, for some reason it is no longer considered equivalent to "".
Passing Named Range Into User Defined Function?
Dec 4, 2012Passing Named Range into User Defined Function
MrExcel.com | Excel Resources | Excel Seminars | Excel Products mcm91201
Depending on time of day and computer I am sitting in front of I am using:
WinXP Pro SP2 with Excel 2003
Win7 Pro SP2 Excel 2007
Win7 Pro SP2 Excel 2010 on PC
Win7 Pro SP2 Excel 2010 on Mac Mini running Boot Camp
OSX Excel for Mac 2011
I have only tried this on Win7 Pro SP2 Excel 2007 but need it to work on all.
I enter the values 0, 1, 2 ... 89, 90 in cells A1 to A91
I select A1:A91 and name the range 'angle'
I create a user defined function:
Public function sindeg(value As Double) as Double
sindeg = sin(worksheetfunction.radians(value))
end
I want 'value' for the function in a cell to be replaced by the corresponding value in the same row (or column) in the named range 'angle'. For example (using commas as column separators). This works for Excel functions like sin, cos, radians, etc.
********** Worksheet Contents **********
A1 = 00, B1 = sin(radians(0)), C1 = sin(radians(A1)), D1 = sin(radians(angle)), E1 = sindeg(0), F1 = sindeg(A1), G1 = sindeg(angle)
A2 = 01, B2 = sin(radians(1)), C2 = sin(radians(A2)), D2 = sin(radians(angle)), E2 = sindeg(1), F2 = sindeg(A2), G2 = sindeg(angle)
A3 = 02, B3 = sin(radians(2)), C3 = sin(radians(A3)), D3 = sin(radians(angle)), E3 = sindeg(2), F3 = sindeg(A3), G3 = sindeg(angle)
......
A91 = 90, B91 = sin(radians(90)), C91 = sin(radians(A91)), D91 = sin(radians(angle)), E91 = sindeg(90), F91 = sindeg(A91), G91 = sindeg(angle)
Column A = input. Columns B, C, D, E and F all calculate the same value by row. Column G fails with a #VALUE. In row 1 the value of angle[1] = 0 therefore column D = C = B = sin(0) = 0. In row 2 angle[2] = 1 therefore B = C = D = 0.017452
How can I get the user defined function sindeg(value) in column G to accept the named range variable 'angle' like the Excel function radians(value) accepted it in column D?
This functionality should work horizontally as well as vertically. For example enter 'angle' A1 to CM1 then have sindeg(angle) filled from A2 to CM2. It should also work in the case where the named range 'angle' is a single cell.
I am sure that this is a simple variable type definition problem in my user defined function: should the input variable be defined as type Range? Or something more exotic?
The brute force approach is to have the function determine the input value by passing in the named range, working out dimensions, calculating offset between the cell the function is in and top (left) of named range, then counting down (right) to pick the correct value. However I cannot see adding all that code to EVERY function. Occam's Razor says there has to be an easier way since Excel built in functions seem to do it readily.
Passing A Range To Be Read As An Array To A Custom Function
Jul 25, 2006code to pass a range say (A1:A3) as an arguement among other arguements to a custom function and then reading this as an array inside the custom funtion
View 4 Replies View RelatedPassing Cell References From A Cell's Text To LINEST Function And A Graph
Mar 2, 2009I'm using Excel 2003 and I'm trying to make my LINEST function and the source cells for a scatter graph use the cell range specified in another cell in the document. I figured out that it was simple to do the following:
I250 contains: 10, shows: 10
I251 contains: ="I"&I250, shows I10
But I can't figure out how to do this in a larger function. I've tried a few things but none of them work. Here is an example where I want it to do a LINEST with y values in cells Ja - Jb and x values in cells Ia - Ib (where a and b are integers specified in cells I250 and J250 respectively):
