To Use A Variable In A Call Function

Aug 12, 2008

I'm playing around with the Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) function. I have a list of people in Column D that I'd like to move data around if the user double clicks on a cell in Column D. The list is about 158 rows long, and could grow beyond that. I've gotten lazy typing:


If Intersect(Target, Range("D4") Is Nothing Then
Exit Sub
Else
{My Code Here}
End If

If Intersect(Target, Range("D5") Is Nothing Then
Exit Sub
Else
{My Code Here}
End If
Etc, Etc, Etc

What I was hoping to do is use a variable (t) to cycle through the rows and call the various functions. So what I came up with is:


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False
On Error Resume Next
Dim t As Integer
Dim Subtest As String
For t = 4 To 158
Subtest = "D" & t

I've created Sub D4(), Sub D5(), and Sub D6() as a test. This didn't work at all as it's looking for a Subtest(). Also, clicking on D5, D6, didn't do much either. Is there any way to make this cycle work, or should I keep typing away?

View 9 Replies


ADVERTISEMENT

Lookup Function Call: Vlookup Call In Sheet

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

String Variable To Call Defined Range Variable

Nov 10, 2006

Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?

I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.

View 5 Replies View Related

Call Macro Using Variable As Name

Feb 7, 2014

I am trying to run different macros by clicking various different buttons on the sheet, I then want different data to load into the user form depending which button was pressed. So I have buttons named "SV_1" and another named "SV_2". when either button is pressed then it runs a common macro that gets the name of the calling item. then I want to add "Macro" to the beginning of the calling item name and then call that macro. here is the code that I am working with, when using a watch i can see the value of the variable is "MacroSV_1" when button 1 is pressed but I cannot get it to run the Sub.

VB:
Public ClkBtn As String
Public CallMacro As String

Sub ItemCall()

[Code] ......

View 5 Replies View Related

Use A Variable To Run Or Call A Procedure By Name

Sep 8, 2007

I have 46 drop downs that I need to call from a main sub. I want to create one loop that will call each one to perform its function. Below is a simple example of what I am trying to do, but where I need help is with the 'Call Y' line. I am not sure what character to use to tell excel to look at what is in the value of Y, not 'Y' itself

Sub testitout()
Dim y As String

For X = 1 To 2
Sheets(" Lookup"). Range("E3").Value = X + 4
y = "dropdown" & X & "_change()" 'this will return "dropdown1_change()" for the first loop
Call y 'i want to call dropdown1_change() below
Next

View 9 Replies View Related

Excel 2007 :: Call Sub Using Variable?

Feb 23, 2013

I'm trying to call another sub with a variable string. I'm using excel 2007. I get the error "Compile Error: Expected Sub, Function, or Property" on the "Call" code. The want the code to run multiple different subs based on user input. For example if they typed "this" it would run the t sub, then h sub, then I sub and finally the s sub.

Code example

Dim Uncvrtdtxt as string
Dim SubName as string
Dim i as integer
Uncvrtdtxt = "this"
For i =1 to Len(Uncvrtdtxt)
SubName = Mid(Uncvrtdtxt, i, 1) & "Route"

[code]....

View 7 Replies View Related

Pass Variable To A Call Subroutine

Jan 18, 2007

I have a code below which need some input from user. This input will also be serve as the input of the subroutine which i am going to call. However, i do not know how to go assign this input to the subrountine which i will be calling, can anybody help ?

For example, the "input" variable will also be served as an input in subroutine test2 ...

View 6 Replies View Related

Call/Run Macro Within Same Module Using Common Variable

Feb 3, 2009

The attached file contains a simplified version of a more complex macro in which a sub routine will be called a number of times to change the colours of cells in different ranges. However, an error is triggered because the a variable and range are not defined in the subroutine although they are in the main part of the macro.

I know I am missing something obvious here, but I'd appreciate any help in knowing how I can define the ranges in the main procedure and then call the subroutine to change values in the different ranges.

View 6 Replies View Related

Formula To Call Variable File Name In Cell Links

Mar 3, 2007

The master has rows corresponding to numbered files:
4300 | CellValue1 | CellValue2 | etc
4301 | CellValue1 | CellValue2 | etc

In attempting a simple formula (a couple VBA codes I tried did not work and were probably substantially more than I needed anyway), I made the following:

='CMain FolderSub Folder[concatenate(cell w/file number,".xls")]Sheet1'!A1
This is identical to the copy/paste link formula currently in use except that I am trying to have the actual file number/name generated off the master list instead of what is in the file itself.

Purpose: in case I was not clear on this above, I am trying to automatically populate the master worksheet with the data from the workbook. The current procedure is to copy a line of the data from the workbook, then paste as a link in to the master worksheet on the row corresponding to the file number.

View 6 Replies View Related

Call Userform From Variable Number Of ActiveX Command Buttons

Jun 27, 2014

I want to be able to call one user form from multiple ActiveX command buttons. The problem is, the number of command buttons depends on user input on another worksheet, so it's variable.

I've renamed all of the command buttons so they are named "CommandButton" & i, where i is an integer between 1 and, say, 200. I want each of these buttons to direct to the same UserForm where additional information can be entered.

I can't think of a way around the event-handler procedure name.

Sub [Command Button Name]_Click

to call the User Form. I won't know the command button names, because I won't know how many there are (max i) until the user inputs.

Basically, I want to create a For loop through the max i and have the event-handlers call the user form

View 14 Replies View Related

Find Amount In Variable Call Locations That Change On Each Spreadsheet

Feb 15, 2012

I am acquiring multiple spreadsheets that do not always match row number, due to additional information on some sheets. By this I mean that the information may be in row 31 on on sheet and row 39 on another, the column location is the same each time. I have been trying Vlookups, indexing and matching plus combination formulas - with no luck. How to get the information I need with a moving cell reference?

I can provide a small copy of a workbook, if needed.

View 1 Replies View Related

Call Function By Name

Aug 30, 2007

Just wonder how to call the function if I have it's name in the variable String
for example something like that:

View 11 Replies View Related

How To Call The Function

May 10, 2007

I wrote a function procedure in VBA. Pasted it into a 'VBAProject' sheet in my workbook. But when I try to call the function (by entering the name and arguments into a cell), the sheet displays "#NAME?".

What have I forgotten?

example:

Public Function TotDays( _EndDate As Date, _StartDate As Date _) As Integer
TotDays = EndDate - StartDate
End Function

cell
a5: =totdays(b2,A2)
b2: 4/1/2007
a2: 3/1/2007

View 9 Replies View Related

Call An Add-in Function From VB

Jul 12, 2007

I created a file with one sub and one function. Saved it as an add-in. and followed the promts to insert the Add-in.

The button I assigned to the macro works fine, but when I call the function, I am told "Sub or Function not defined".

Is there some special way to call an add-in function from VB?

View 9 Replies View Related

ComboBox To Call Another Function

Sep 24, 2008

How is it possible to take the choice from the Combo Box and send that into a Stored Procedure statement in Excel VBA

I have 3 Combo Boxes, Months, Years, Date.

I'm trying to fillout these into this statement to run my Stored Procedure.

View 10 Replies View Related

How To Call A Procedure / Function

Mar 2, 2012

I have found some code that does what I want- but i do not understand how to use - call it. The programmer says ...

GetNetworkIPAddress()
' can be called from a worksheet cell using the formula:
' =GetNetworkIPAddress()

I have put this in a cell but i ger an error #Name?

what should i do ?

View 9 Replies View Related

Function Call To UserForm (VBA)

Aug 23, 2006

I wanted to have that one appear whenever a certain cell in a certain sheet gets a specific value, and depending on the answer of the user the Form should set the value of some other cell. So maybe there´s a much simpler solution to this task than what I tried - and I´ll be grateful to learn about it.

Anyway I tried the code which follows below - and it works just fine when I call it from the VBE environment - but inside the UserForm it just stops in between (without any error message) when I operate it from the Excel sheet (but still succeeds with the function). I´ll be very happy if someone could solve this "miracle" for me (even IF there´s a better solution altogether). So I just provided a simple Function which does nothing but call the UserForm and is executed whenever the condition is met (some calls to MessageBoxes in between just have been added to see how far the code gets executed): Function:

Public Function Call_my_form()
Call Option_Form.Show
MsgBox "Call executed"
End Function

Call of the function: =If(E11="Go";Call_my_form();"Nothing")

The UserForm just uses two OptionButtons with code as follows (two versions to check whether one of these would be the reason - both do behave the same way):

Private Sub OptionButton1_Click()...........................

View 5 Replies View Related

Call Add-in .xla Function From Workbook

Apr 3, 2008

I have created an Excel add-in (.xla) which contains many custom functions. This add-in is stored as read-only on a network drive. I reference it from various spreadsheets and calls to it work perfectly unless I put a call to it in the Workbook_Open event. When I make a call to a function in the add-in from a spreadsheet's Workbook_Open event, Excel crashes completely and I get the dialog that offers to recover the file and report the problem (i.e. Excel shuts down). I have no doubt that this is being caused by the call to the add-in specifically from the Workbook_Open event as removing this call solves the problem. Also, it is not related to a particular function as I have tried various ones; it is ANY call to the add-in from Workbook_Open that causes it. Is this a bug in Excel or is there something that I need to do before referencing the add-in in Workbook_Open? I have tried Application.CalculateFull and Doevents prior to making the call to the add-in and neither approach worked.

View 2 Replies View Related

Call Combobox_change Function On Exit.

Dec 22, 2009

I'm using an activex combobox in excel. It display 4 posible choices, and one of them is "Other". When someone select "other" there is a textbox named "tb" that should be filled. Otherwise tb should be invisible. Combobox change() function looks like that:

View 14 Replies View Related

Function To Call Another Worksheet Query

Jan 9, 2006

I have a spreadsheet with 5 worksheets representing the working days of the week. Each worksheet contains data pertaining to every occurance of that day for as long as it has been recorded. Each day is broken up into hourly data.

So for example, for Monday's worksheet, you would have

Col A..............Col B........Col C
9/jan/06........5am.........data
9/jan/06........6am.........data
9/jan/06........7am.........data
...etc....then...
2/jan/06........5am.........data
2/jan/06........6am.........data
2/jan/06........7am.........data
...etc....

We are trying to create a function, on another worksheet called Query. The only way I can describe what we want it to do is to do it in psuedocode.

Psuedo........................................

View 10 Replies View Related

Function Call To Set The Password Of The File

Apr 23, 2007

I found a function to set the password a an excel file:

=========================================
Function SetPwd(strNoPwdFile As String, _
strPwdFile As String, _
Optional strOpenPwd As String, _
Optional strModPwd As String) ' As Boolean
' This function requires the following arguments:
' strNoPwdFile - The path to a document without a password.
' strPwdFile - The path and name to save the password-
' protected document.
'
But the compiler said that it is syntax error.

How can i rectify the error?

View 9 Replies View Related

Call A Function With A Macro/button

May 23, 2006

I want to call on a function with a macro/button in the same way as when you choose "insert-function" but in a way that the user himself is able to put in values on the spredsheet.

View 7 Replies View Related

Call Function At Each Iteration Of Solver

Mar 27, 2008

I have a written a small sub to run solver. However, I need to run a secondary sub at each solver iteration. The secondary sub I want to run is called “BypassGoal”.
My code currently looks like:

Sub OperatingPt()
Dim WrkBk As Workbook
Dim WrkSht1 As Worksheet
Set WrkBk = ThisWorkbook
Set WrkSht1 = WrkBk.Worksheets("Engine")
Dim Mode As String
Dim CellTarget As String
Dim CellRange As String
WrkSht1.Activate
WrkSht1. Cells(30, 2).Value = 2
CellTarget = "H14"
CellRange = "B30"
SolverReset
SolverOptions Iterations:=3000, Precision:=0.000001, AssumeNonNeg:=True, Derivatives:=2, StepThru:=True ....................

View 2 Replies View Related

Dir Function - Invalid Procedure Call Or Argument

Jun 25, 2013

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] .......

View 4 Replies View Related

Invalid Procedure Call Or Argument In Function

Aug 30, 2012

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

View 9 Replies View Related

'C:Documents...' Inside Worksheet Function Call

Jan 25, 2008

I have a spreadsheet that uses a function defined in VBA code that is housed in an add-in which is stored in the default location (C:Documents And Settings{user_name}Application DataMicrosoftAdd-Ins). To share this spreadsheet, I first install the add-in on the other person's computer. Then, when the other person opens the spreadsheet, the function doesn't work because the function call on the worksheet contains the whole path of the add-in (and the path contains my {user_name} rather than the other person's). So, the immediate solution is to just delete the path of the add-in from the function call (so that only the name of the function remains, plus the arguments of course). But this is quite inconvenient given the number of times the function appears in the worksheet, and it's not practical as we continue to share files like this.

My question is: is there a way to make sure that the 'C:Documents...' never gets added to the function call in the first place? I mean, why doesn't Excel just try to run the function locally before it adds that path that refers to a separate computer? I do realize that there are other methods of sharing VBA code. For example, I know that I could include the code in the specific spreadsheet that I'm trying to share, and this would work. But, that's not the answer I'm looking for (for one, the function refers to a very large amount of data stored in the add-in, and there are other reasons that make the aforementioned method impractical). I'm wondering if I'm missing something, and if there is a way to stop Excel from tacking on the path string to the function call, so that the function would just work on any computer as long as they have the add-in installed?

View 9 Replies View Related

Call Public Function From Class Module

May 25, 2006

I have written a public function inside a class module. Is there anyway I could call the public function in Excel? What is the exact syntax to do it?

View 5 Replies View Related

Contiguous Vs. Noncontiguous Cells Used In A Function Call From VBA

Aug 2, 2006

I am working with several columns of data, and each column's range is stored in a range array. My program needs to use a specififc combination of the columns as the second argument sent to the regress function. In certain cases, the columns I am referencing are not contiguous, so the function won't work. Here's an example:

Suppose we're working with 3 independent variables (a simple case) whose data is stored in three contiguous columns named x1, x2, and x3. The program I've written will call the regress function 6 times: once using x1, once using x2, once using x3, once using the union of x1 and x2, once using the union of x1 and x3, once using the union of x2 and x3, and once using the union of x1, x2, and x3. Suppose the range for x1 is d5:d18, the range for x2 is e5:e18, and the range for x3 is f5:f18. Also, each union of ranges is stored as one element in an array called Combos, which is sent to the regress function.

The regress function returns an error when prompted to calculate regression stats for columns x1 and x3 because they aren't contiguous. I need to know if there is some sort of way around this. write a procedure that will detect if the ranges being input to the regress function are contiguous, and if they are not, copy them to a new worksheet so they will be contiguous and then send the new range references to the regress function for calculation purposes? Is there an easier way around this issue? If I can't get this to work, my entire program is wasted.

View 3 Replies View Related

Compile Error: = Expected After Function Call

Dec 18, 2006

i'm trying to call a function from another one, i'm getting this error 'Compiler error: = expected' but i don't know the reason, the functions simply take some values an store them in an here is the

Dim productos(19, 3) As String
Sub agregarProducto(ByVal descripcion As String, ByVal modelo As String, _
ByVal precio As String, ByVal unidad As String)
Dim r As Integer
For r = 0 To 19
If productos(r, 0) = "" Then
productos(r, 0) = descripcion
productos(r, 1) = modelo
productos(r, 2) = precio
productos(r, 3) = unidad
End If
Next
End Sub

Sub agregarProductoTelas()
Dim descripcion, modelo, precio, unidad As String
If Selection.Column = 1 Then
descripcion = Selection. Offset(0, 0).Value
modelo = Selection.Offset(0, 0).Value
precio = Selection.Offset(0, 3).Value
unidad = Selection.Offset(0, 2).Value
agregarProducto(descripcion, modelo, precio, unidad) 'error happens right here
MsgBox (descripcion)...

View 3 Replies View Related

Using Two Cell Entries To Call Range For Function Evaluation?

Sep 7, 2013

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.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved