Public Variables Duration

Jul 21, 2009

I've recently been experiencing some strange phenomena in relation to public variables. It seems that if I run separate routines in quick enough succession (e.g. press a button twice within a couple of seconds), the memory of the public variable "hangs over" into the second routine.

The routines, however, are completely separate events. And I'm not using userforms or anything like that in order to keep VBA running in the background. In which case it seems that Public variables don't always get set to nothing with the end of the final sub in a routine. Has anyone else experienced anything like this? I was under the impression only Static variables could do this.

View 9 Replies


ADVERTISEMENT

Public Variables Not Holding The Value

May 14, 2008

I am trying to use a public variable but it is not holding the value when I call another sub.

Here is basically what I have:

Public AcctName as Long

Sub Statments()
There is much more code in here but it is not relevent
AcctName = an account name lets say "frank"

If X XX then
Call Title
End If

End Sub

It then goes to the title sub and executes the code however it is supposed to write out the value held in acctname but it comes up blank instead of as Frank which is the value it held when it left the statments sub. I am new to this public thing and need any help I can get.
Just a note: the spelling is correct and the variable does hold the correct value before title sub is called.

View 9 Replies View Related

Nullifying Public Variables

Oct 20, 2006

I'm programming with multiple workbooks. So public variables in one workbook often reference ranges in another. Anyhow, I have found that, when closing a workbook, unless I nullify all public variables that reference this workbook, it closes in excel but remains visible in the VB Editor. When I open the workbook again, another instance of its VB project appears in the VB Editor..and so on and so on, which is obviously pretty annoying..

I've worked out a way of nullifying (setting equal to nothing) all public variables in a workbook, but it's quite arduous and I haven't tried it yet.. basically, it involves running a code which runs through the procedure which sets these public variables (they are all set in a single procedure), then having the code write a new subroutine setting all variables to nothing, then running this subroutine..

View 3 Replies View Related

Declaring Public Variables

Apr 19, 2007

how do you declare public variables? In one of my forms I declare the variables but for some reason can not access them in other forms.

Public Rm_new As Integer
Public Angle_new As Integer
Public Pl_new As Integer

Public Sub cal_graph_Click()
'Stating variables
Rm_new = text_rm.Text
Angle_new = text_angle.Text
Pl_new = text_length.Text

View 3 Replies View Related

Reset All Public Variables In A Module

Aug 6, 2009

Is tere a way to reset all public variables in a module. I have a number of sub routines withing the same module and need to carry a variable from one to another so I have decalared tham as public variables.

This works fine except for an instance when I expect the variable to be empty (because nothing has yet been assigned to it) when in fact it contains the value that was assigned to last time another sub was run. So I suppose what I need is a way to reset all the variables in the module when a particular sub ends

View 3 Replies View Related

UserForm Variables In Public Modules

Jan 13, 2008

I make a userform called Option and now i want to call some of the variables of the userform in a module. First i try to copy this variables in a cell with the next

ActiveSheet.Cells(2, 3) = Me.MediaP.Value

and it works. But now, i want to call to MediaP in a module. For do that I try to do this:

With Worksheets("Hoja2")
.Range("B1").Value = Option.MediaP.Value

but it doesn't work. I don't want to use public variables or copy/use the information of the cell(2,3), i only want to know how use the information on the textbox called MediaP in a module.

View 3 Replies View Related

Subscript Out Of Range Using Public Array Variables

Aug 18, 2006

In my VBA project I've declared several public variables as normal (ahead of
all procedures) ... Public simolo() As double etc. They are declared in normal modules, and arent declared twice. I set the values in one procedure and then execute a second procedure but, when the variable is encountered in the second procedure, it appears to be empty and I get a "Subscript out of range" error. Clearly, the public variable isn't public since no data is stored in it.
What is going on?

View 6 Replies View Related

Pass Variables From Userform To Public Procedures

Nov 17, 2007

When working in an userform, and you call another sub routine within the userform, will all variables passed automatically be byref (no way to do byval?)?

View 3 Replies View Related

Excel 2010 :: Public Variables From Event Handling Procedure?

Jan 22, 2014

Excel 2010

I am trying to set public variables from an event handling procedure based in a worksheet so I can use that variable in a userform. Nothing I have tried works no matter where I declare the variable. I am using a msgbox to display the variable (a range) but it shows as blank regardless of whether I place the variable in a module, this workbook object or in the sheet object where the event code is placed.

I am sure there is a simple way to transfer variables from the sheet's code (where it must remain as the variable depends on the target cell's position that triggers the event).

View 2 Replies View Related

Call Public Object From Public Sub?

Aug 14, 2012

I am trying to bring up a form after clicking the "ok" button on another form. Both forms are defined in my public sub, so basically, the module creates both forms, calls the first form, and hides the second form. Then the first form hides itself and shows the second form. However, when second form is defined using "dim", the first form can't find it to show it, and when I make the second form "public" in my public sub, I get the error that it is an invalid attribute or function.

Here is the code from my sub that applies to this error:

Code:
Public Sub PutInEngine()
Dim InputForm As New FrmInputs
InputForm.Show

[Code].....

View 3 Replies View Related

Sum Duration Times In H:mm:ss

Jan 28, 2014


I am trying trying to calculate the amount of time students view videos. I understand I should use [h]:mm:ss but excel continues to convert entries such as 0:14:59 (0 hours, 14 minutes, 59 seconds) to 12:14:49 PM both in the cell and the function bar.

The problem gets complicated because I have entered the data with two different formats; some in h:mm and some in h:mm:ss'

1) Is there an easy way to sum the duration time so it accurately reflects total hours:minutes:seconds?
2) Do I need to reformat or re-enter data to make it work?
3) If so, is there a macro to do it?

This has been a bear, (40 students by 30 videos) all hand coded.

View 5 Replies View Related

Date Duration

Oct 26, 2008

Date problem.

I need to find the duration of time based on 4 columns:

column A : Start date DD/MM/YYYY
Column B : Start time HH:MM:SS [24 hour clock]

column C : End Date DD/MM/YYYY
Column D : End time HH:MM:SS [24 hour clock]

Column E: The duration result in : in Days and Hours

I have several thousand rows and need to complete before tomorrow

View 9 Replies View Related

Duration Formula

Feb 16, 2010

I have the below Date & time in cell a1 and another date and time in cell a3

8/2/2010 08:45:00
8/2/2010 10:15:00


I need to be able to show duration (in hours) between the 2 times, in this instance its the same date, but this wont always be the case. To add a level of complexity in addition to the gross duration in hours, I will also need to know how long (in hours or mins) of the duration fits into "in hours" ( 9 -5 mon- fri) then the duration thats fits "out of hours" (after 5pm weekdays or all day Sat & Sun)

View 9 Replies View Related

Calculate Duration Of Hours

Nov 9, 2008

I'm trying to create a work sheet with start and ending times with a 30 minute meal break, for the end of the week I would like to calculate those hours as a general number.

View 2 Replies View Related

Calculating Duration Of Tasks

Aug 7, 2009

I have a worksheet where I calculate the duration it takes for an operation. the format of the worksheet is as folows:

The worksheet is composed of Start and Finish times in this format: 2009/06/02 5:32:19 , so for example,

- Row1 of the worksheet will have "Start" in column H1 and the start time in column B1
- Row 2 of the worksheet directly below has the "Finish" time, with the Finish time information in column B2

So with the help of excelforums I was able to implement a macro to extract the finish time cell found in row B2 and offset it to C1 and do a simple calculation to get the duration in minutes and display it in column D of the same row as "Start".

View 12 Replies View Related

Get Duration Of MP3 File In Spreadsheet

Oct 9, 2012

I'd like to write a VBA script which writes all data of my mp3s in a spreadsheet. I can read all data like the artist, songname, album, year, this works fine. However I'm not able to get the duration of the mp3 files. Any sample script which reads the duration of the mp3 files?

View 2 Replies View Related

Write Code To Cal Duration

Jan 12, 2008

I have following data, data_in and date_out. I need to auto cal these 2 variable different and give the output as duration in month.

x_INV  EF1date_lndate_out205/10/195005/10/1950325/10/195025/10/1950415/11/195015/11/1950508/01/195029/10/1950618/10/195018/10/1950717/10/195017/10/1950817/10/195017/10/1950915/10/195020/12/19501013/12/195013/12/1950 Excel tables to the web >> Excel Jeanie HTML 4

And give the result in colume X as following :-

x_INV  X1stk_dur20304051060708092 Excel tables to the web >> Excel Jeanie HTML 4

View 9 Replies View Related

Calculating Duration Of ALL My Tasks

Feb 2, 2009

I have 10 columns (1 through 10) called TASKS and 2 rows for each task
Row 1, has a 'start date' in the form of 1/1/2010
Row 2, is duration

the duration of each task may or may not overlap with other durations of other tasks.

how can i find out the total duration of ALL my tasks.....without any overlaping.

any formula that can calculate this? ( i need one number at the end!)

View 9 Replies View Related

Format For Time Duration

Aug 28, 2006

Is there a format for duration of time. The attached spread sheet has column B "Duration". This is the actual time on which I need to do calculation. On "C2" I have the formula "=IF(B2>10,400,B2*40)". The formula is " if value in B2 is more than 10 hours then the value in C2 should be $400 , else C2= B2(duration time) multiplied by 40.

View 5 Replies View Related

Excel 2003 :: Log Out Duration Per Employee

Feb 27, 2014

I have a Log In/Log Out report that I need pulling data from.

Ultimately what I want to do is get the amount of time my employees were logged out.

I am using Excel 2003. Attached is an example of the data I'm working with.

Sheet 1 contains the data, and Sheet 2 will be the summary.

So, looking at Bob's times: If column A contains Bob (A2 in this case), and the same row in column G (G2) contains 79, subtract the contents of the same row in column F (F2) from the next row down in column D (D3) (which would be D3 - F2). That result should populate on Sheet 2 next to Bob's name in h:mm:ss format.

If there is nothing in the next row down in column D, then do nothing.

I would like to leave the data as is, if possible. There are extension numbers next to the names in the data. If possible to ignore those when rolling up to the summary that would be useful. Also, on the summary sheet, if the employee is not on the data sheet (if they had the day off) they can have 0 or NA or something to indicate there is no data for them.

View 1 Replies View Related

Formula For Calculating Duration Between Two Timing

Mar 12, 2014

What is the formula for calculating duration in min/hrs between start time and end time without mentioning any date, and if the end time stretches to next day i.e beyond 12.00 midnight. And what is the logic used in the formula.

View 1 Replies View Related

MOD Function Used For Calculating Duration Between Two Timings?

Mar 14, 2014

How is MOD function used for calculating duration between two timings.

View 3 Replies View Related

Converting Duration To Hours Format

Sep 2, 2009

I have this set of time that is in eg. 0800 to 1630. how do i make it in to a 8.5hrs figure

View 3 Replies View Related

Distribute Duration Evenly Amongst Tasks?

Sep 10, 2013

I'm working on a project that will will have varying tasks & estimating the total duration to complete these tasks.

For example:

If estimating that the min-project will take 90 days & I have 17 tasks, how can I distribute the 90 days evenly amongst the 17 tasks?

View 5 Replies View Related

Find Out Total Duration Logged In

Oct 18, 2009

is it possible to calculate the total duration of logged in from the data below?

TimeDateTimeExtCoDial Number08:02:00 AM11/10/200908:02AM203LOG IN08:03:00 AM11/10/200908:03AM2033448249908:05:00 AM11/10/200908:05AM207LOG IN08:05:00 AM11/10/200908:05AM132102445477708:08:00 AM11/10/200908:08AM207102445477708:11:00 AM11/10/200908:11AM1322908:11:00 AM11/10/200908:11AM1323008:12:00 AM11/10/200908:12AM2073008:12:00 AM11/10/200908:12AM2032908:13:00 AM11/10/200908:13AM132202631121108:14:00 AM11/10/200908:14AM203LOG OUT

Column 'A' has the time and extn# on column 'D' and indication of logged in or logged out on column 'F'

there can be several log in and log out by an extn.

View 9 Replies View Related

Covert Duration Between Start And End Date To Units

Oct 1, 2013

I need to formula to covert duration between start date and end date to units below.

Start Date
End Date
Number of Month
Number of Years
Number of Days

6/1/13
8/31/13

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

View 6 Replies View Related

Change History Duration Error '1004'

May 25, 2006

I encounter an problem when I migrate the excel application from excel 97 to excel 2000. The application basically composed of VB6 and VBA. When the program execute to line "ChangeHistoryDuration", i.e.

'Turn On the Audit
ExlApp. ActiveWorkbook.KeepChangeHistory = True
ExlApp.ActiveWorkbook.ChangeHistoryDuration = gi_duration
ExlApp.ActiveWorkbook. SaveAs w_filename, , gs_open_pwd, gs_write_pwd, , , xlShared

Run-time Error '1004', Method 'ChangeHistoryDuration' of object '_Workbook' faild was found. However, it runs successfully in excel 97. Does anyone know the possible problem that cause such error.

View 4 Replies View Related

Calculate Duration Based On Start & End Times

Jan 21, 2008

I have a spreadsheet with 4 columns - Start Date, Start Time, End Date and End Time. In the 5th column, I need to fill in the "Duration" which is calculated as follows :- Duration = (End Date,End Time) - (Start Date,Start Time)

View 2 Replies View Related

Time Format- Spread Sheet To Calculate Call Duration

Mar 27, 2007

I am setting up a spread sheet to calculate call duration it would be a great help if data could be entered using one hand IE use decimal point on the number pad instead of the colon is this possible. I will need to subtact start time from end time to get the duration

View 9 Replies View Related

Unzip Code - Works Without Variables, Breaks With Variables...

Feb 5, 2009

Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

View 2 Replies View Related







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