How in the world does this thing run? Never seen variables defined outside the sub they are being used in.
How in the world does this thing run? Never seen variables defined outside the sub they are being used in.
Peace of mind is found in some of the strangest places.
I donno but it does, if someone can tell me how to upload it I will so you guys can see for yourself.
PM me with your emai address and I will give you mine. You can send it to me.
Peace of mind is found in some of the strangest places.
Doing that makes them available to every subroutine in the module.Originally Posted by austenr
Yeah they'd all be global variables then.
No, Global is available across the project, these are module level variable. If you changed DIM to GLOBAL then it would become a global variable.
Inside a sub is only available to that sub unless the variables are passed around like so:
Alternatively you could Dim the MyVar outside the routines then it would not need passing back and forth.Sub MySubHost() Dim MyVar As String MyVar = "Hello World" Call MySubSlave(MyVar) End Sub Sub MySubSlave(MyVar As String) MsgBox MyVar End Sub
Finally if you wish to have MySubSlave on a different module you would need to either pass back and forth (as above) OR Global the variable.
Historically I have been known to have a GLOBALS module in some of my larger VBA apps. Here is an example.
Hope that explains itOption Explicit '*********************************************************************************************** '* * '* MODULE: GLOBALS * '* * '*********************************************************************************************** '*********************************************************************************************** ' This page is simply used to set up the global variables and define functions * '*********************************************************************************************** ' Declare for call to mpr.dll. Declare Function WNetGetUser Lib "mpr.dll" _ Alias "WNetGetUserA" (ByVal lpName As String, _ ByVal lpUserName As String, lpnLength As Long) As Long Global Const NoError = 0 'The Function call was successful Global Const RepVer As String = "1.7" Global DatabaseUserName As String Global DatabasePassword As String Global ReportName As String Global RunTimeReportID As Long Global FuncName As String Global Param1 As String Global Param2 As String Global Param3 As String Global Param1Format As String Global Param2Format As String Global Param3Format As String Global FuncReturnSTR As String Global FuncReturn As String Global CancelRoutine As Boolean Global DirName As String Global DSPListString As String Global FormatListString As String Global ClientModeString As String Global SmartWarningsString As String Global TimeInSeconds As Long Global TimeInTime As String Global Itemlist() As String Global lItem As Long Global Aggregator_Figures As Long Global DBClient() As String Global ConfigListVar() As String Global ConfigListString As String Global Debug_On As Boolean Global DBClientList As String Global NoArtist As Boolean Global NoConfig As Boolean Global CustList As Long Global ProdList As String Global Affiliate As String Global ArtistString As String Global SelectedCountry As String Global Summary(7) As String Global CompleteProdList() As String Global CurrentUser As String Global AdminReportName() As String Global AdminProjectName() As String Global AdminProductName() As String Global EditAllowed As String Global ReportOwner As String Global MaxArtRow As Long Global LogNum As Long Global ProdForProj() As String Global ProdForProjArtist() As String Global ProdForProjTitle() As String Global ProdForProjConfig() As String Global ProjForProj As String Global UnmatchedProjCode As String Global ListBoxList() As String Global ListBoxSQL As String Global SalesMonthValid As Boolean Global NewProjCodeValidate As String Global vch_project_code As String Global vch_name As String Global NewProject As Boolean Global ActiveUserRealName As String Global n_mgr_id As Long Global vch_artist_name As String Global n_division As Long Global vch_format As String Global n_units As Long Global vch_label_type As String Global vch_price_point As String Global vch_marketing_comment As String Global vch_source As String Global vch_signing_country As String Global n_start_month As Long Global ProjCreated As Boolean Global ReturnValue As String Global ProjCode As String Global CurrentRepVer As String Global GetiTunesDaily As Boolean Global SoftChange As Boolean Global RAProdTitle As String Global Const BIF_RETURNONLYFSDIRS As Long = &H1 Global Const BIF_DONTGOBELOWDOMAIN As Long = &H2 Global Const BIF_RETURNFSANCESTORS As Long = &H8 Global Const BIF_BROWSEFORCOMPUTER As Long = &H1000 Global Const BIF_BROWSEFORPRINTER As Long = &H2000 Global Const BIF_BROWSEINCLUDEFILES As Long = &H4000 Global Const MAX_PATH As Long = 260 Type BrowseInfo hOwner As Long pidlRoot As Long pszDisplayName As String lpszINSTRUCTIONS As String ulFlags As Long lpfn As Long lParam As Long iImage As Long End Type Type SHFILEOPSTRUCT hwnd As Long wFunc As Long pFrom As String pTo As String fFlags As Integer fAnyOperationsAborted As Boolean hNameMappings As Long lpszProgressTitle As String End Type Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _ ByVal pidl As Long, _ ByVal pszBuffer As String) As Long Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _ lpBrowseInfo As BrowseInfo) As Long.
Hi Goallie,
What you had orinally was almost working; at least it was producing a result, and I guided you as to how to correct the syntax errors. You have now rewritten the code to do something different, but still doesn't work.
This is your assignment and I have no interest in a right/wrong result. I don't have time to waste though, on your moving goal posts.
Good luck.
MD
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Please don't abandon me, I am trying, but when you don't have much experience doing this it takes a while. And this is what the code is supposed to do, it should have had those functions in the first place but I was just trying to debug it first.
If you can just tell me what type of declaration to use I believe that will solve it, but I've tried making just about everything a "single, double, integer or long" and it doesn't correct it. I've gone back to the original program to try making it work perfectly before putting the functions in.
OK GREAT! I've gotten it to work! Now I just need to make some charts from it.
Thanks so much for all the help guys, I can't tell you how much I appreciate it.
Can you post your finished code?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Sure, basically what I did was go back to the original after reading your post, move the sub to below the declarations, add a "+1" to the cells that read nDips, add the functions in, and change some of the declarations to double.
[VBA]Dim Rs As Double
Dim iRow As Double
Dim iCol As Integer
Dim nDips As Double
Dim Cuph As Double
Dim Rc As Integer
Dim Volc As Double
Dim Vols As Double
Dim Hn As Integer
Dim Pi As Double
Private Sub CommandButton1_Click()
Pi = 3.141592654
'Initial Straw Radius and column number
Rs = 0.2
iCol = 1
'Clear work area
Range("A1:N2000").Clear
'iterate through straw sizes
Do While Rs <= 0.5
'initialize data
nDips = 0
iRow = 2
Cuph = 10
Cells(1, iCol) = "Rs=" & Rs
'Column Reader
Cells(iRow, iCol) = "nDips"
Cells(iRow, iCol + 1) = "Cup Height"
iRow = iRow + 1
'write initial values
Cells(iRow, iCol) = nDips
Cells(iRow, iCol + 1) = Cuph
iRow = iRow + 1
'iterate til final cup height is established
Do While Cuph >= 0.5
'Calculate cup volume using cup radius and water level
CupVolume
'Calculate straw volume using straw radius and water level
StrawVolume
'Get new cup volume after removing the water in the straw
NewCup
'Calculate new water level
NewWater
nDips = nDips + 1
Cells(iRow, iCol) = nDips
Cells(iRow, iCol + 1) = Cuph
iRow = iRow + 1
Loop
iCol = iCol + 2
Rs = Rs + 0.05
Loop
End Sub
Function CupVolume()
Volc = Pi * 16 * Cuph
End Function
Function StrawVolume()
Vols = Rs * Cuph
End Function
Function NewCup()
Volc = Volc - Vols
End Function
Function NewWater()
Cuph = Volc / (Pi * 16)
End Function
[/VBA]
Well it look a sensible solution, unfortunately it's wrong (based on your comments)
Re your variables, it's neater and more checkable to group the types.
There is no advantage in Interger over Long, so always use Long
Single/Double may have accuracy issues. If you don't need it, go with Single. You can test the difference.
Use a Constant declaration for the likes of Pi.
You might also want to make your cup diameter a constant. It's better to get sizes etc. at the head of your code, so if you need a different calculation there is only one change. You don't need to go looking for 16 (in this case) in multiple places, if the problem changes.
[vba]
Option Explicit
Dim iRow As Long
Dim iCol As Long
Dim nDips As Long
Dim Rs As Single
Dim Cuph As Single
Dim Volc As Single
Dim Vols As Single
Const pi = 3.141592654
Private Sub CommandButton1_Click()
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I copied your code and put it in place of mine and it works but it only goes to Rs = 0.45 for me, where it needs to go until Rs = 0.5
I don't think it should do that as there is still the While Rs <=0.5 loop in there. What would cause it to stop there instead of continuing?
Use Double, it fixes the problem. It must be some rounding error
Dim Rs As Double
Trying both produces different results for RS. Interesting.
'Single
0.2
0.25
0.25
0.3
0.3
0.35
0.35
0.4
0.4
0.45
0.45
0.5000001
'Double
0.2
0.25
0.25
0.3
0.3
0.35
0.35
0.4
0.4
0.45
0.45
0.5
0.5
0.55
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Odd... I'll change it to double later today and see how that works.
Thanks again for the help.
Have you found your error?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Yeah just now changed it and it works now, goes all the way to 0.5
Yet again, I really appreciate all the help everyone.
Hi Goalle11,
I realize that this is for school, so do not want to hint too broadly, but I think the mistake(s) Malcom referred to are not at the changing value of rs.
Maybe this will help. What does 16 represent?
Mark
Last edited by GTO; 05-08-2010 at 06:01 PM.
By the way, I am guessing here, but if the workbook doesn't have a bunch of other stuff, I'm betting that the 2.82mb size is after running the code and the sheet is full of values. If that is the case, clear the sheet, Save and close. The wb should be down to an attachable size.
You can also zip the wb and attach a zip.
Mark
There are two overflow possibilities here:
1. Somewhere in the code you have a '+' where you should have a '-'. This is because rather than removing water from the cup you are adding water to the cup. This will inveitably lead to the situation you describe.
2. The vessel which you're emptying the straw into is smaller than the volume you need to remove from the cup.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Posting Permissions
Reply With Quote
