Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-changeable paths into Excel - avoid hard coding
Hello all, I want to enable my non-VBA friendly users to be able to change the paths that various files are pulled from / saved to via Excel. At the moment the paths are hard coded - they are of the non-geeky variety (unlike me ;) ) and do NOT want to go into the VBA to change them. So I have made a table in Excel for them to enter the paths. My project has many, many modules and subroutines. I can't declare and set the variables to the path names once, as I'd have to do it in each sub/module. SO my question is - is there a way of making global constants which are set up from stuff in the excel sheet (e.g. range("mypath").value )? Or maybe there is a better way...? Thanks for your help. Brgds, Graham -- gearoi ------------------------------------------------------------------------ gearoi's Profile: http://www.excelforum.com/member.php...o&userid=26576 View this thread: http://www.excelforum.com/showthread...hreadid=400711 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-changeable paths into Excel - avoid hard coding
My suggestion would be to create publis constants.
Eg... -------------------------------------------------------- Option Explicit Public Const cstMyPath as String = Range("MY_Path").Value --------------------------------------------------------- This would be how I'd do it, or else create a message box for them to enter it in. Either way works. HTH. "gearoi" wrote: Hello all, I want to enable my non-VBA friendly users to be able to change the paths that various files are pulled from / saved to via Excel. At the moment the paths are hard coded - they are of the non-geeky variety (unlike me ;) ) and do NOT want to go into the VBA to change them. So I have made a table in Excel for them to enter the paths. My project has many, many modules and subroutines. I can't declare and set the variables to the path names once, as I'd have to do it in each sub/module. SO my question is - is there a way of making global constants which are set up from stuff in the excel sheet (e.g. range("mypath").value )? Or maybe there is a better way...? Thanks for your help. Brgds, Graham -- gearoi ------------------------------------------------------------------------ gearoi's Profile: http://www.excelforum.com/member.php...o&userid=26576 View this thread: http://www.excelforum.com/showthread...hreadid=400711 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-changeable paths into Excel - avoid hard coding
But constants have to be constants.
DaveO wrote: My suggestion would be to create publis constants. Eg... -------------------------------------------------------- Option Explicit Public Const cstMyPath as String = Range("MY_Path").Value --------------------------------------------------------- This would be how I'd do it, or else create a message box for them to enter it in. Either way works. HTH. "gearoi" wrote: Hello all, I want to enable my non-VBA friendly users to be able to change the paths that various files are pulled from / saved to via Excel. At the moment the paths are hard coded - they are of the non-geeky variety (unlike me ;) ) and do NOT want to go into the VBA to change them. So I have made a table in Excel for them to enter the paths. My project has many, many modules and subroutines. I can't declare and set the variables to the path names once, as I'd have to do it in each sub/module. SO my question is - is there a way of making global constants which are set up from stuff in the excel sheet (e.g. range("mypath").value )? Or maybe there is a better way...? Thanks for your help. Brgds, Graham -- gearoi ------------------------------------------------------------------------ gearoi's Profile: http://www.excelforum.com/member.php...o&userid=26576 View this thread: http://www.excelforum.com/showthread...hreadid=400711 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-changeable paths into Excel - avoid hard coding
True, but a constant can be defined as a range value. It's the holder's
contents that have to remain static, not the value itself. For the purposes of the routines running, the data should remain the same. I've never tried doing it, but it works in other code I've written from other apps. Othwise, just declare a Public variable and set it on initialise. "Dave Peterson" wrote: But constants have to be constants. DaveO wrote: My suggestion would be to create publis constants. Eg... -------------------------------------------------------- Option Explicit Public Const cstMyPath as String = Range("MY_Path").Value --------------------------------------------------------- This would be how I'd do it, or else create a message box for them to enter it in. Either way works. HTH. "gearoi" wrote: Hello all, I want to enable my non-VBA friendly users to be able to change the paths that various files are pulled from / saved to via Excel. At the moment the paths are hard coded - they are of the non-geeky variety (unlike me ;) ) and do NOT want to go into the VBA to change them. So I have made a table in Excel for them to enter the paths. My project has many, many modules and subroutines. I can't declare and set the variables to the path names once, as I'd have to do it in each sub/module. SO my question is - is there a way of making global constants which are set up from stuff in the excel sheet (e.g. range("mypath").value )? Or maybe there is a better way...? Thanks for your help. Brgds, Graham -- gearoi ------------------------------------------------------------------------ gearoi's Profile: http://www.excelforum.com/member.php...o&userid=26576 View this thread: http://www.excelforum.com/showthread...hreadid=400711 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-changeable paths into Excel - avoid hard coding
Did that line work for you?
It didn't for me. DaveO wrote: True, but a constant can be defined as a range value. It's the holder's contents that have to remain static, not the value itself. For the purposes of the routines running, the data should remain the same. I've never tried doing it, but it works in other code I've written from other apps. Othwise, just declare a Public variable and set it on initialise. "Dave Peterson" wrote: But constants have to be constants. DaveO wrote: My suggestion would be to create publis constants. Eg... -------------------------------------------------------- Option Explicit Public Const cstMyPath as String = Range("MY_Path").Value --------------------------------------------------------- This would be how I'd do it, or else create a message box for them to enter it in. Either way works. HTH. "gearoi" wrote: Hello all, I want to enable my non-VBA friendly users to be able to change the paths that various files are pulled from / saved to via Excel. At the moment the paths are hard coded - they are of the non-geeky variety (unlike me ;) ) and do NOT want to go into the VBA to change them. So I have made a table in Excel for them to enter the paths. My project has many, many modules and subroutines. I can't declare and set the variables to the path names once, as I'd have to do it in each sub/module. SO my question is - is there a way of making global constants which are set up from stuff in the excel sheet (e.g. range("mypath").value )? Or maybe there is a better way...? Thanks for your help. Brgds, Graham -- gearoi ------------------------------------------------------------------------ gearoi's Profile: http://www.excelforum.com/member.php...o&userid=26576 View this thread: http://www.excelforum.com/showthread...hreadid=400711 -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-changeable paths into Excel - avoid hard coding
No, you're right you can't define a constant as the results of a range. What
a pain. Oh well, looks like the Public variable and not constant would have to be the way to go. Hate it when I'm wrong. Good spot Dave P!! "Dave Peterson" wrote: Did that line work for you? It didn't for me. DaveO wrote: True, but a constant can be defined as a range value. It's the holder's contents that have to remain static, not the value itself. For the purposes of the routines running, the data should remain the same. I've never tried doing it, but it works in other code I've written from other apps. Othwise, just declare a Public variable and set it on initialise. "Dave Peterson" wrote: But constants have to be constants. DaveO wrote: My suggestion would be to create publis constants. Eg... -------------------------------------------------------- Option Explicit Public Const cstMyPath as String = Range("MY_Path").Value --------------------------------------------------------- This would be how I'd do it, or else create a message box for them to enter it in. Either way works. HTH. "gearoi" wrote: Hello all, I want to enable my non-VBA friendly users to be able to change the paths that various files are pulled from / saved to via Excel. At the moment the paths are hard coded - they are of the non-geeky variety (unlike me ;) ) and do NOT want to go into the VBA to change them. So I have made a table in Excel for them to enter the paths. My project has many, many modules and subroutines. I can't declare and set the variables to the path names once, as I'd have to do it in each sub/module. SO my question is - is there a way of making global constants which are set up from stuff in the excel sheet (e.g. range("mypath").value )? Or maybe there is a better way...? Thanks for your help. Brgds, Graham -- gearoi ------------------------------------------------------------------------ gearoi's Profile: http://www.excelforum.com/member.php...o&userid=26576 View this thread: http://www.excelforum.com/showthread...hreadid=400711 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-changeable paths into Excel - avoid hard coding
Hi Gearoi,
My project has many, many modules and subroutines. I can't declare and set the variables to the path names once, as I'd have to do it in each sub/module. SO my question is - is there a way of making global constants which are set up from stuff in the excel sheet (e.g. range("mypath").value )? Declare the variables that are to hold the path as Public, at the top of a normal module. This makes them global in scope, for all modules to use: Public gsPath as String Public gbVarsOK as Boolean Then use an initialise routine: Sub Initialise() gsPath=Thisworkbook.Worksheets("Paths").Range("A1" ) gbVarsOK=True End Sub In your entry subs (fired by the user), it is a good idea to check if gbVarsOK=True so you know the paths have been read. Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
User-changeable paths into Excel - avoid hard coding
i dont understand your question? if you looking at getting the file structure of specific files use Sub getdirector() i = Application.GetOpenFilename ' brings up the open dialog box x = Application.GetSaveAsFilename ' brings up the save as dialog box ActiveCell.Value = i ' returns the open dialog box value ActiveCell.Offset(0, 1) = x ' returns the save dialog box value End Sub Hope this help -- funkymonkU ----------------------------------------------------------------------- funkymonkUK's Profile: http://www.excelforum.com/member.php...fo&userid=1813 View this thread: http://www.excelforum.com/showthread.php?threadid=40071 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
user-changeable drop-down lists | Excel Discussion (Misc queries) | |||
Protection password iin VBA, how to avoid user seeing it? | Excel Discussion (Misc queries) | |||
hard coding a cell | Excel Discussion (Misc queries) | |||
Avoid user having to enter 00 hours when using [mm]:ss format | Excel Discussion (Misc queries) | |||
Hard-Coding a File Location | Excel Programming |