PDA

View Full Version : using pointer in VBA



amrane
09-22-2014, 04:12 AM
Dear Forum,

I am looking how to maintain function result in memory to be re-used by different procedure for 2ed time!!

my case is that I am calling the function (ie import_dt) first time from the USERFORM code page, and for the second time I will the same result in the main program code.


in the attached file I tried to sub-routing gathered in main sub, its working, but what is the way in case of two different code page?

Thx in advance for your support,
Br, Amrane

SamT
09-22-2014, 12:33 PM
Declare a Public Variable in a standard module ("aux", etc.) Set the variable when you call the function in the UserForm

Variable = Function

amrane
09-24-2014, 11:19 AM
Dear Mr SamT,
Actually VBA is not accepting to declare Array as public variable,

let me summarize the case:
my target is to lunch userform windows and make first evaluation of the loaded data (ie the data size is matrix X x Y), and Then using the same loaded data go for further analysis give other result.

my problem is just here, I mus reload the the same data just had been analysis, ref to the lines

Main2()
Set inner = Sheets(1).Cells(1, 1)
inner_dt = import_dt(inner)

Thx for your support,
Amrnae

snb
09-24-2014, 11:29 AM
Actually VBA is not accepting to declare Array as public variable,

That's not correct /the case if you add a macromodule and declare the public variable over there.

SamT
09-24-2014, 02:01 PM
The problem was that you were decalring the two publc variables in every module. Public variables can only be declared once per Project.

I cleaned out the many declarations and added a mod_Globals to declare all global variables and procedures. IMO, Function import_dt is a global Function.

A Global preocedure is one that is called from many modules. An Auxillary Procedure is like what you have in Module Function import_dtM. They are only called from one other module.

Special Procedure Groups are those that may be called from many modules, but all relate to the same issue. For example a Module "mod_AccessIO" would contain all the Public Variable, Subs and Functions that have to do with Access in an Excell or Word Project.

Below is the (my style) code for the new Module "mod_Globals. IMO, this styl of commenting is only needed in Global modules because Module Names like "Function import_dt," and "mod_AccessIO" are self explanatory.

Note: I put the code in Quotes because the auto-formatting of the Code Tags messes up with this many nested comments. For best results look at the attachment.


Option Explicit
Public inner As Range
'Used For: Input parameter to Global Function import_dt
'Set in :
'UserGForm1: _
Sub Caction1_Click
'complement_USERFORMs: _
'Sub Main _
'Sub Mtx_data_exposition
'Sheet1: _
'Sub Mtx_data_exposition
'Used in:
'UserGForm1s: _
Sub Caction1_Click
'complement_USERFORM: _
Sub Main _
Sub Mtx_data_exposition
'Sheet1: _
Sub Mtx_data_exposition

Public inner_dt() As Long
'Filled By: Global Function import_dt (add this comment to Attachment. SamT)
'Used in:
'UserGForm1s: _
'Sub Caction1_Click
'complement_USERFORM: _
'Sub Main _
'Sub Mtx_data_exposition
'Sheet1: _
'Sub Mtx_data_exposition



Function import_dt(dt_in As Range) As Long()
'Purpose: Fills Global Array Variable inner_dt(
'Called by:
'UserGForm1s: _
Sub Caction1_Click
'complement_USERFORM: _
Sub Main _
Sub Mtx_data_exposition
'Sheet1: _
Sub Mtx_data_exposition
Dim data() As Long
Dim i, j, i_max, j_max As Integer
i_max = Range("A200").End(xlUp).Row
j_max = Cells(Range("A1").Row, 255).End(xlToLeft).Column

ReDim data(i_max - 1, j_max - 1)

For i = 1 To i_max
For j = 1 To j_max
data(i - 1, j - 1) = Sheets(1).Cells(i, j)
Next j
Next i

import_dt = data()
End Function