String Names In Different Subs

Jan 12, 2007

How can it be that this works?

Sub AA()
Dim ReportFileName As String
Cells(1, 19).Select
ReportFileName = ActiveCell.Value
ChDir "C:"
Workbooks.Open Filename:=ReportFileName _
End Sub

And this doesn't work?

Sub BB()
Dim ReportFileName As String
Cells(1, 19).Select
ReportFileName = ActiveCell.Value
End Sub
Sub CC()
ChDir "C:"
Workbooks.Open Filename:=ReportFileName _
End Sub

Somehow it seems that the value of the string "ReportFileName" is forgotten when running the subroutine CC. How do I make it not "forget" the file name?

View 4 Replies


Pulling Out Single Names From A String Of Names

Sep 1, 2009

I have a list of names in a single cell. They are all seperated by a comma, then a space. Example would be: John Smith, Steve Wilson, Wallace O Malley, etc. What formula could I use to pull out the names individually, starting from the farthest right?

View 2 Replies View Related

Replace End Of A String Only In Strings Containing Names

Apr 23, 2014

I have a VBA find and replace question

I have a spreadsheet with cognitive tests (approx 300) that have been given names like


What I need is a VBA macro that replaces the _e only at the end of the string to _ss for those strings that begins with w3_, w4_ and DKEFS_ (e.g. from w3_th_e to w3_th_ss)

Likewise I want to replace only the _e at the end of the string on all other names (e.g. pegboard_eh_e) with _t (e.g. from pegboard_eh_e to pegboard_eh_t) Is this possible?

View 4 Replies View Related

Range Names Created From String Variables

Aug 30, 2006

I work with data that varies in row numbers but is consisten in column width. I am trying to write code that will create a named range for the data but be flexible to expand or contract based on the amount of data that is pulled in. Below is the

Const lngLastPossRow As Long = 65536
Dim strDataRng As String
strDataRng = ActiveSheet.Name & "!R4C1:R" & Range("a" & lngLastPossRow).End(xlUp).Row & "C17"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Names.Add Name:=("Data"), RefersToR1C1:= _

While the result creates a named range called "Data" , it does not allow me to reference and data in any formulas ( sumif's, etc). Can someone tell me what I am missing. It appears to be returning the wrong data type (string as opposed to range values).

View 6 Replies View Related

VBA For Inserting Input String Alphabetically Into Column Of Names?

Oct 2, 2013

I have a list of names in the A column of the sheet. I wish to use the input string, a new name, of a vba box to insert into the column of existing names in alphabetical order by inserting an entire row, which I suspect will be the case anyway. Also 'Return' key doesn't seem to be inserting a CR on the page that I typed this request upon.

View 5 Replies View Related

Merging Two VBA Subs?

Mar 21, 2012

I'd like to combine two VBA subs existing on a single sheet. Since they can't duplicate I need some sort of a switch statement...?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range, cell As Range
Dim Msg As String
Dim ValidateCode As Variant
Set VRange = Range("E4:E100")
For Each cell In Target


View 5 Replies View Related

Combining Two Subs Into One

May 20, 2014

I have two subs which I've been trying to combine into one. Essentially all that is changing is the slicer name and range value.

Here's what I have:

Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)
Dim cache As Excel.SlicerCache
Set cache = ActiveWorkbook.SlicerCaches("Slicer_Project_Type3")
Dim sItem As Excel.SlicerItem
Dim myString As String
For Each sItem In cache.SlicerItems
If sItem.Selected = True Then
myString = myString & "," & sItem.Name


But it's erroring out on me.

View 4 Replies View Related

How To Use Same Variables In Different Subs

Jun 4, 2009

I have 2 sub, and I want to use the same variables that reference values on my spreadsheet.

for instance, I declare and set ws as worksheet("sheet1") in the first sub.
in the second one, I also want to reference to the same ws. Instead of having to declare it twice, being redundant, what would be a good way to do this?

View 9 Replies View Related

Repeating The Subs

May 8, 2007

How can I have a sub repeat itself? I have a code and I want it to ask the user at the end if they would like to do it again. If so the whole sub needs to be repeated. I'm sure this has something to do with looping, but I don't know how to manage this.

View 3 Replies View Related

Using Variables And Calling Subs

Aug 19, 2008

I'm having some trouble getting to grips with using/calling variables and other sub routines.

View 9 Replies View Related

Passing UDTs To Subs

Jan 26, 2009

I have created a few User Defined Types in a Main subroutine, but when I pass them into another sub the intelisense for the elements doesn't come up. I can type in the elements manually - and it all seems to work okay - but with a lot of types and elements I really want it to save me the effort.

View 2 Replies View Related

Passing Variables Between Subs?

Nov 3, 2009

I think I am confused as to how to declare a variable in one sub and use it as an input into another sub. I have attached some code below that assigns a value to two variables and then calls a sub that uses those values. I am brand new to programming so I guess I am confused how to implement this.

View 3 Replies View Related

VBA - Exit All Subs Not Just Current

Sep 10, 2012

VBA - Exit all Subs not just current

View 2 Replies View Related

Passing Variables Between Subs

Oct 30, 2013

I have some code I'm running:

Sub Main ()
Dim Value1 as integer
Call firstroutine
Call secondroutine
End Sub


Somehow I need to pass Value1 & Value2 to secondroutine.

View 5 Replies View Related

Call Multiple Subs

Jul 28, 2007

Calling a Sub from another Sub is quite common, how about a Sub that does nothing else but call all the subs to execute the entire job?

Will the subs run sequentially after one completes, or will there be the likley hood they will bump into each other causing all sorts of chaos.

View 9 Replies View Related

Linking Subs To Excute One After The Other

Jan 25, 2007

The below is a selection of some of the code i have written to organise data. The below works, but i'm not sure how/if I can make all the subs work in order automatically once I start the first Sub? I realise what I have written is probably pretty poor code so many need changed?

Sub move_to_all()
End Sub
Sub Omni_Trades().....................

View 2 Replies View Related

Breakpoints Not Hit In Public Subs Of Vba Add-in

May 2, 2007

I have Three questions about debugging VBA code of an excel add-in.

-Breakpoints set in certain Public Subroutines are NOT hit. I have an application that writes data to the add-in. You cannot debug the app + the add-in. The add does things(e.g. writes data), then calls a main public macro in the add-in, then the addin takes over. If I comment out the main macro, the parent app halts, then I try to set breaks in the add-in.

After resumeing, some breaks are never hit. I know this because if I set a stack trace (output ing the func name to a temporary worksheet), I see these functions being hit. All the modules are public.

Usually in C, breakpoints are not hit because that code is NOT part of the address space of the program.

2. Very naive question (I am a C,C++ programmer)...some tiimes when I hit F5, the whole app runs, other times a list of macros comes up (just as if I had gone to the menu and went toolsmacros. Why is this?????.....placement of the cursor?????

3. If in the app I have 5 code modules, with two public subroutines each.

View 3 Replies View Related

Pass Arguments Between Subs

Jul 11, 2007

I am still learning and was wondering what is wrong with this. I have cut some of the code out as it pretty long. This is my main procedure:

Public Sub cal_parameters_Click()
'istat(i) = 0 bonded
' = 1 slip
' = 2 soil yielded

Dim istat(1 To 999) As Single

Call secondary_loop 'calls subprocedure secondary_loop
End Sub

It then calls secondary_loop:................

View 6 Replies View Related

A Variable Which Is Used By Several Subs In A Worksheet But Is Not Global?

Dec 7, 2008

I am working on a workbook which uses a large number of variables. I am trying to keep them as "local" as possible to keep it simple. Some of my variables are local to the subs they're used in. Some are global as they're used by subs in several sheets. A third type of variable is used by several subs all belonging to the same sheet. Is there a way of declaring them so they're known by all subs in that sheet, but not by every sub in the workbook?

View 4 Replies View Related

Combine Subs To Run And Appear As One In Macro List

Jul 13, 2009

I have a Sub that calls 8 other subs, can I hide the 8 subs on a users macro list?

I tried making the 8 subs "private subs", and it did hide them, but then my call sub macro didn't work.

Any thoughts?

View 9 Replies View Related

Calling Subs When Workbook Opens

Mar 7, 2012

I have the following code in a module that I would like to initiate when the workbook opens:

Private Declare Function GetComputerName Lib "kernel32" _
Alias "GetComputerNameA" ( _
ByVal lpBuffer As String, _
ByRef nSize As Long) As Long

Public Property Get ComputerName() As String

[Code] ........

In a worksheet, I have =compname() in cell A1 and =hdserialnumber() in A2. What is the trick to make the values in these cells appear as soon as the worksheet opens?

View 2 Replies View Related

Series Of Subs Called From Function

Apr 15, 2013

I have a series of procedures I am calling from a function that each run fine when executed individually. These procedure are titled: DataValidation, Test1, Test2

The function will not run past Test2- I do not get the msgbox "Stage 2"

Public ArrayD
Public ArrayD2 As Variant
Public RngD3 As Range

Public Function Test(arg2 As Range)

DataValidation arg2

[Code] ............

View 4 Replies View Related

Pass Function Result Between Subs

Oct 11, 2006

I have a simple/dumb question... How do you "capture" a value that is returned by a custom function.

I have tried searching the forums for this & I know that I should by all rights know how to do this by now... but I just can't figure it out.

Here is a Function that I copied from Ktrasler in this thread:
Week Numbers

Public Function MyWeek(DateArg As Date) As Byte

Const BaseDate = "30/12/2001"

DateArg = CDate(DateArg) - (Weekday(DateArg) - 1)
MyWeek = Int(((DateArg - CDate(BaseDate)) / 7) Mod 52)
If MyWeek = 0 Then MyWeek = 52

End Function

I know how to pass variables to Functions, and tried this one out & it worked splendidly for my needs, but how do I take the value of "MyWeek" and use it in the subroutine that I am calling it from?

View 3 Replies View Related

Variables Loosing Values Between Subs

Nov 30, 2006

trying to get it to put the 2 variables into 2 cells in the worksheet for the moment!

The Forms code is:

Private Sub CmdCancel_Click()
Unload frmMsgBox
Exit Sub
End Sub

Private Sub CmdOK_Click()
End Sub

It always fills the cells with False and blank, no matter what buttons are pressed (except cancel), so all I can think is that the information isn't feeding back through properly from the form.

View 4 Replies View Related

Pass Workbook Variable Between Subs

Jul 11, 2007

This is the code after editing to make it more clear

Public Sub 1()
BookA= activeworkbook. name
End Sub

Public Sub 2()

With BookB. sheets(1)
End Sub()

At the end of public sub 1, BookB is the active workbook. What I want to do in public sub 2 is to copy some data from BookA to BookB. Unfortunately, when moving from public sub 1 to public sub 2, BookA needs to be defined again. The code above is the code that I use in my add-ins. I figured out for non add-ins code I can define BookA with when BookB is active as I before work with BookA. This does not apply for add-ins as thisworkbook will refer to my add-ins code. Is there anyway of keeping definition of BookA is constant from one public sub to another public sub? This is simplified code. In fact, I can't merge public sub 2 with public sub 1 due to some reason which I don't say it here.

View 6 Replies View Related

Add Macro To Call Subs Depending On Cell Value

May 16, 2014

I am trying to add a macro to the ACCT DATA sheet, which calls one of two subs (AddCarriertoChecklist() - or - ClrPolChList() located in seperate modules). The macro should call them depending on whether "X" is entered in a cell in column "E". The issue lies in that I have macros doing a few things to this sheet already, and I am unable to tie this one in.

I have the two subs (AddCarriertoChecklist() - or - ClrPolChList()) working. I just can't seem to get them called. This small bit is how I was attempting to call them.

[Code] ......

Below is how I currently have it tied in with the rest of the code for this sheet:

[Code] .....

What is it that I am missing?

View 2 Replies View Related

Toggle 2 Subs (between Either Hidden Or Visible Menus)

Dec 11, 2013

I would like to toggle (button) between either hidden menus or visible menus. I have the following code:

Private Sub ToggleButton1_Click()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual

[Code] .......

Will not work. either one state or the other is visible.

View 2 Replies View Related

Amending VBA Sub / Function To Pass Results To Other Subs

Feb 5, 2014

Basically someone has coded a VBA script to pull a users details from AD when a button is clicked, using the staff id as the search criteria.

I've amended the criteria so it uses Environ$(USERNAME) to find details of the current logged on user, but i am struggling to get the resulting information out of the Sub to be used in other Subs! It currently just fills a cell with the required info.

View 1 Replies View Related


Jun 8, 2007

A recent message prompted me to complete a project to show all modules and subroutines in a workbook.

As use of VBA to manipulate the VB Editor is a recurring issue I thought it a good idea to share this.

Option Base 1
Dim WBname As String
Dim ws As Worksheet
Dim TitleStr As Variant
Dim VBProject As Object
Dim ToRow As Long
Dim ToCol As Integer
Dim ComponentType
Dim MyComponent As Object
Dim ComponentName As String
Dim TypeArray As Variant
Dim StdCol As Integer
Dim LastLine As Long
Dim CurrentLineNumber As Long
Dim CurrentLineText As String
WBname = ActiveWorkbook.FullName
On Error Resume Next
Set ws = ActiveWorkbook.Worksheets("WB Contents")
If Err.Number 0 Then ' sheet not exist....................

View 2 Replies View Related

Prevent Subs From Appearing On Alt+F8 Macro List

Aug 6, 2008

I have an add-in that I'm passing around to my users, and though it has 8 or 9 subs, it only has 2 that they need to see. However, I'd rather not relegate all of the code to a single module, as there is quite a bit of it. I'm aware that I could change all the subs to functions, but they would still appear in the UDF list, and this would also be confusing to the individual responsible for the upkeep of these macros (and I'd have to rework the code a little bit).

I'm just asking if there is any direct way to do this, before I go to the trouble of making a lesser workaround. Google seems to think no, but "don't show macros in list" isn't a very solid search.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved