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 Repliessorting data I use in a workbook for athletics. I've really chopped down my workbook for upload, In the worksheet "Leaderboard" I can call up stats for different lifts, and it finds (in this case) the top 5 lifts and the names for the kids that have those corresponding lifts.
My problem is that when two or more kids have the same lift, it will only call up the name of the first instance of that lift. You can see this in the "Leaderboard" worksheet, and the name "Adams, Andrew" appears for both lifts of 75. I would appreciate any help on how to correct this, as I've searched the Internet for weeks now looking for a solution. I've tried experimenting with different things as well, all to no avail.
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
[Code]....
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:
Code:
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
[code].....
But it's erroring out on me.
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?
I'm having some trouble getting to grips with using/calling variables and other sub routines.
View 9 Replies View RelatedI 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 RelatedI 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 RelatedVBA - Exit all Subs not just current
View 2 Replies View RelatedI have some code I'm running:
Code:
Sub Main ()
Dim Value1 as integer
Value1=5
Call firstroutine
Call secondroutine
End Sub
[code].....
Somehow I need to pass Value1 & Value2 to secondroutine.
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.
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
CC
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?
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()
Sheets("All").Select
Cells.Select
Range("A10").Activate
Selection.Copy
Sheets("Rest").Select
Range("A1").Select
ActiveSheet.Paste
End Sub
Sub Omni_Trades().....................
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.
HOW COME, IF I GO TOOLSMACROSRUN (same as what I get sometimes when I go F5), I ONLY GET A PARTIAL LISTING OF THE SUBROUTINES, ie NOT ALL 10?
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:................
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 RelatedI 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?
I have the following code in a module that I would like to initiate when the workbook opens:
Code:
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?
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"
Code:
Public ArrayD
Public ArrayD2 As Variant
Public RngD3 As Range
Public Function Test(arg2 As Range)
DataValidation arg2
[Code] ............
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?
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()
Me.Hide
Unload frmMsgBox
Exit Sub
End Sub
Private Sub CmdOK_Click()
Me.Hide
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.
This is the code after editing to make it more clear
Public Sub 1()
BookA= activeworkbook. name
BookB=Application.Workbooks.Add
Workbook(BookB).activate
End Sub
Public Sub 2()
BookB=Activeworkbook.name
With BookB. sheets(1)
.range("A1")=BookA.sheets(1).range("B1")
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 thisworkbook.name 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.
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?
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.
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.
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
Sub SHOW_ALL_MODULES()
WBname = ActiveWorkbook.FullName
On Error Resume Next
Set ws = ActiveWorkbook.Worksheets("WB Contents")
If Err.Number 0 Then ' sheet not exist....................
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.
I am trying to get a module wide variable to provide the current filename to the various subs.
View 2 Replies View RelatedI had a matrix like this
0 4 0 0
1 0 0 0
0 0 0 2
2 3 0 0
then I used vba to put number between 1 to 4 (since this is an 4x4 matrix) when there is a 0 in the matrix, but the numbers in row need to be no repeat??
so i need something like this for my output. Is there any code for search the number in the row?? How can I do this??
1 4 2 3
1 2 3 4
1 3 4 2
2 3 4 1
I have several columns of data representing a shop operation along different years. Now I try to see how will it look like if I have 10 shops like this, all summed up. The problem is that each shop will have the same numbers as the other 9, just that it's first year of operation will be different.
Now, I don't want to have different sheet for each and every shop since their data is exactly the same. I only want to have 1 type of shop which I can add to a consolidated sheet but 10 times, and each time the first year shoud be different.
Just an example: If I have this data for my shop:
YR1 YR2 YR3 YR4
Sales: 10 12 14 16
Costs: 5 6 7 8
Operation: 2 2 3 3
Tax: 1 1 1 1
profit: 2 3 3 4
And lets say I have 4 shops opening in 2000,2001 and 2 in 2002 than my consolidated should look like this:
2000 2001 2002 2003
Sales: 10 12+10 14+12+10*2 16+14+12*2
Costs: 5 6+5 7+6+5*2 8+7+6*2
Operation: 2 2+2 3+2+2*2 3+3+2*2
Tax: 1 1+1 1+1+1*2 1+1+1*2
profit: 2 3+2 3+3+2*2 4+3+3*2
But again, I don't want to open a different table for each shop and than sum them up - I want to have only my one shop, and have a possibility to say when will I open my next shop and the consolidation will be automatically.