Astuces Excel
  1. Astuces Excel
  3. Excel VBA: Range to Ssortingng Array en 1 étape
Intereting Posts
Si l'instruction elseif Excel VBA Valeurs de sum d'un mois spécifique et user d'un grand file CSV avec 4,5 millions de lignes Node Js Write Excel avec des colonnes verrouillées (non modifiables) Comment atsortingbuer des lignes à une list dans Excel Ma macro Excel 2007 – qu'est-ce que Argument non facultatif signifie? Tri Excel 2013 avec des données mixtes et différents formats diriger un user vers un location de files avec vba excel Correspondance exacte avec application.match Comment afficher un tableau croisé dynamic avec des valeurs de colonne réelles comme l'access dans Excel Supprimez la ligne vide dans Excel vba une fois que la totalité de la donnée de cette ligne a été copiée à une autre image Comment faire des plots de ligne dans r pour la visualisation comme fait en excel? Excel: un file énorme très lent avec Bloomberg Cacher les colonnes et les lignes dans vba, en fonction des critères de sélection d'une cellule spécifiée application.inputbox error après avoir cliqué sur OK avec l'input vide Excel: renvoie plusieurs éléments avec une valeur de search, mais sur une feuille différente

Excel VBA: Range to Ssortingng Array en 1 étape

Je sais que vous pouvez facilement prendre une gamme de cellules et les gifler dans un tableau de variantes, mais je veux travailler avec un réseau de strings (car il est monodimensionnel et prend less de memory qu'une série de variantes).

Existe-t-il un moyen de convertir automatiquement une gamme en une string de caractères?

À l'heure actuelle, j'utilise une fonction qui prendra la gamme et sauvegardera les valeurs dans une masortingce variante, puis convertira le tableau variant en string. Cela fonctionne bien, mais je cherche un moyen d'aller directement de la gamme à la string de caractères. Toute aide serait grandement appréciée. 

Function RangeToArray(ByVal my_range As Range) As Ssortingng() Dim vArray As Variant Dim sArray() As Ssortingng Dim i As Long vArray = my_range.Value ReDim sArray(1 To UBound(vArray)) For i = 1 To UBound(vArray) sArray(i) = vArray(i, 1) Next RangeToArray = sArray() End Function

MISE À JOUR: Il semble qu'il n'y ait aucun moyen de sauter l'étape de lancer datatables dans un tableau variable d'abord avant de la convertir en un tableau de strings à une seule dimension. Une honte si c'est vrai (même si cela ne prend pas beaucoup d'effort, j'aime trop optimiser, alors j'espérais qu'il y avait un moyen de sauter cette étape). Je fermerai la question dans quelques jours si aucune solution ne se présente. Merci pour les commentaires utiles, les gars!

UPDATE2: La réponse va à Simon qui a mis beaucoup d'efforts (tout comme l'ont fait tous les autres) et a souligné clairement qu'il est effectivement impossible de passer de la gamme à la string en un seul coup. Merci tout le monde.

Que diriez-vous… 

 Public Function RangeToSsortingngArray(theRange As Excel.Range) As Ssortingng() ' Get values into a variant array Dim variantValues As Variant variantValues = theRange.Value ' Set up a ssortingng array for them Dim ssortingngValues() As Ssortingng ReDim ssortingngValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2)) ' Put them in there! Dim columnCounter As Long, rowCounter As Long For rowCounter = UBound(variantValues, 1) To 1 Step -1 For columnCounter = UBound(variantValues, 2) To 1 Step -1 ssortingngValues(rowCounter, columnCounter) = CStr(variantValues(rowCounter, columnCounter)) Next columnCounter Next rowCounter ' Return the ssortingng array RangeToSsortingngArray = ssortingngValues End Function Fonction publique RangeToSsortingngArray (theRange As Excel.Range) As Ssortingng () Public Function RangeToSsortingngArray(theRange As Excel.Range) As Ssortingng() ' Get values into a variant array Dim variantValues As Variant variantValues = theRange.Value ' Set up a ssortingng array for them Dim ssortingngValues() As Ssortingng ReDim ssortingngValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2)) ' Put them in there! Dim columnCounter As Long, rowCounter As Long For rowCounter = UBound(variantValues, 1) To 1 Step -1 For columnCounter = UBound(variantValues, 2) To 1 Step -1 ssortingngValues(rowCounter, columnCounter) = CStr(variantValues(rowCounter, columnCounter)) Next columnCounter Next rowCounter ' Return the ssortingng array RangeToSsortingngArray = ssortingngValues End Function 'Obtenir des valeurs dans un set de variantes Public Function RangeToSsortingngArray(theRange As Excel.Range) As Ssortingng() ' Get values into a variant array Dim variantValues As Variant variantValues = theRange.Value ' Set up a ssortingng array for them Dim ssortingngValues() As Ssortingng ReDim ssortingngValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2)) ' Put them in there! Dim columnCounter As Long, rowCounter As Long For rowCounter = UBound(variantValues, 1) To 1 Step -1 For columnCounter = UBound(variantValues, 2) To 1 Step -1 ssortingngValues(rowCounter, columnCounter) = CStr(variantValues(rowCounter, columnCounter)) Next columnCounter Next rowCounter ' Return the ssortingng array RangeToSsortingngArray = ssortingngValues End Function Dim VariantValues ​​As Variant Public Function RangeToSsortingngArray(theRange As Excel.Range) As Ssortingng() ' Get values into a variant array Dim variantValues As Variant variantValues = theRange.Value ' Set up a ssortingng array for them Dim ssortingngValues() As Ssortingng ReDim ssortingngValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2)) ' Put them in there! Dim columnCounter As Long, rowCounter As Long For rowCounter = UBound(variantValues, 1) To 1 Step -1 For columnCounter = UBound(variantValues, 2) To 1 Step -1 ssortingngValues(rowCounter, columnCounter) = CStr(variantValues(rowCounter, columnCounter)) Next columnCounter Next rowCounter ' Return the ssortingng array RangeToSsortingngArray = ssortingngValues End Function VariantValues ​​= theRange.Value Public Function RangeToSsortingngArray(theRange As Excel.Range) As Ssortingng() ' Get values into a variant array Dim variantValues As Variant variantValues = theRange.Value ' Set up a ssortingng array for them Dim ssortingngValues() As Ssortingng ReDim ssortingngValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2)) ' Put them in there! Dim columnCounter As Long, rowCounter As Long For rowCounter = UBound(variantValues, 1) To 1 Step -1 For columnCounter = UBound(variantValues, 2) To 1 Step -1 ssortingngValues(rowCounter, columnCounter) = CStr(variantValues(rowCounter, columnCounter)) Next columnCounter Next rowCounter ' Return the ssortingng array RangeToSsortingngArray = ssortingngValues End Function 'Configurer un set de strings pour eux Public Function RangeToSsortingngArray(theRange As Excel.Range) As Ssortingng() ' Get values into a variant array Dim variantValues As Variant variantValues = theRange.Value ' Set up a ssortingng array for them Dim ssortingngValues() As Ssortingng ReDim ssortingngValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2)) ' Put them in there! Dim columnCounter As Long, rowCounter As Long For rowCounter = UBound(variantValues, 1) To 1 Step -1 For columnCounter = UBound(variantValues, 2) To 1 Step -1 ssortingngValues(rowCounter, columnCounter) = CStr(variantValues(rowCounter, columnCounter)) Next columnCounter Next rowCounter ' Return the ssortingng array RangeToSsortingngArray = ssortingngValues End Function Dim ssortingngValues ​​() As Ssortingng Public Function RangeToSsortingngArray(theRange As Excel.Range) As Ssortingng() ' Get values into a variant array Dim variantValues As Variant variantValues = theRange.Value ' Set up a ssortingng array for them Dim ssortingngValues() As Ssortingng ReDim ssortingngValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2)) ' Put them in there! Dim columnCounter As Long, rowCounter As Long For rowCounter = UBound(variantValues, 1) To 1 Step -1 For columnCounter = UBound(variantValues, 2) To 1 Step -1 ssortingngValues(rowCounter, columnCounter) = CStr(variantValues(rowCounter, columnCounter)) Next columnCounter Next rowCounter ' Return the ssortingng array RangeToSsortingngArray = ssortingngValues End Function ReDim ssortingngValues ​​(1 à UBound (variantValues, 1), 1 à UBound (variantValues, 2)) Public Function RangeToSsortingngArray(theRange As Excel.Range) As Ssortingng() ' Get values into a variant array Dim variantValues As Variant variantValues = theRange.Value ' Set up a ssortingng array for them Dim ssortingngValues() As Ssortingng ReDim ssortingngValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2)) ' Put them in there! Dim columnCounter As Long, rowCounter As Long For rowCounter = UBound(variantValues, 1) To 1 Step -1 For columnCounter = UBound(variantValues, 2) To 1 Step -1 ssortingngValues(rowCounter, columnCounter) = CStr(variantValues(rowCounter, columnCounter)) Next columnCounter Next rowCounter ' Return the ssortingng array RangeToSsortingngArray = ssortingngValues End Function 'Mets-les là-bas! Public Function RangeToSsortingngArray(theRange As Excel.Range) As Ssortingng() ' Get values into a variant array Dim variantValues As Variant variantValues = theRange.Value ' Set up a ssortingng array for them Dim ssortingngValues() As Ssortingng ReDim ssortingngValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2)) ' Put them in there! Dim columnCounter As Long, rowCounter As Long For rowCounter = UBound(variantValues, 1) To 1 Step -1 For columnCounter = UBound(variantValues, 2) To 1 Step -1 ssortingngValues(rowCounter, columnCounter) = CStr(variantValues(rowCounter, columnCounter)) Next columnCounter Next rowCounter ' Return the ssortingng array RangeToSsortingngArray = ssortingngValues End Function Dim columnCounter As Long, rowCounter As Long Public Function RangeToSsortingngArray(theRange As Excel.Range) As Ssortingng() ' Get values into a variant array Dim variantValues As Variant variantValues = theRange.Value ' Set up a ssortingng array for them Dim ssortingngValues() As Ssortingng ReDim ssortingngValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2)) ' Put them in there! Dim columnCounter As Long, rowCounter As Long For rowCounter = UBound(variantValues, 1) To 1 Step -1 For columnCounter = UBound(variantValues, 2) To 1 Step -1 ssortingngValues(rowCounter, columnCounter) = CStr(variantValues(rowCounter, columnCounter)) Next columnCounter Next rowCounter ' Return the ssortingng array RangeToSsortingngArray = ssortingngValues End Function Pour rowCounter = UBound (variantValues, 1) À 1 étape -1 Public Function RangeToSsortingngArray(theRange As Excel.Range) As Ssortingng() ' Get values into a variant array Dim variantValues As Variant variantValues = theRange.Value ' Set up a ssortingng array for them Dim ssortingngValues() As Ssortingng ReDim ssortingngValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2)) ' Put them in there! Dim columnCounter As Long, rowCounter As Long For rowCounter = UBound(variantValues, 1) To 1 Step -1 For columnCounter = UBound(variantValues, 2) To 1 Step -1 ssortingngValues(rowCounter, columnCounter) = CStr(variantValues(rowCounter, columnCounter)) Next columnCounter Next rowCounter ' Return the ssortingng array RangeToSsortingngArray = ssortingngValues End Function Pour columnCounter = UBound (VariantValues, 2) To 1 Step -1 Public Function RangeToSsortingngArray(theRange As Excel.Range) As Ssortingng() ' Get values into a variant array Dim variantValues As Variant variantValues = theRange.Value ' Set up a ssortingng array for them Dim ssortingngValues() As Ssortingng ReDim ssortingngValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2)) ' Put them in there! Dim columnCounter As Long, rowCounter As Long For rowCounter = UBound(variantValues, 1) To 1 Step -1 For columnCounter = UBound(variantValues, 2) To 1 Step -1 ssortingngValues(rowCounter, columnCounter) = CStr(variantValues(rowCounter, columnCounter)) Next columnCounter Next rowCounter ' Return the ssortingng array RangeToSsortingngArray = ssortingngValues End Function ssortingngValues ​​(rowCounter, columnCounter) = CStr (variantValues ​​(rowCounter, columnCounter)) Public Function RangeToSsortingngArray(theRange As Excel.Range) As Ssortingng() ' Get values into a variant array Dim variantValues As Variant variantValues = theRange.Value ' Set up a ssortingng array for them Dim ssortingngValues() As Ssortingng ReDim ssortingngValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2)) ' Put them in there! Dim columnCounter As Long, rowCounter As Long For rowCounter = UBound(variantValues, 1) To 1 Step -1 For columnCounter = UBound(variantValues, 2) To 1 Step -1 ssortingngValues(rowCounter, columnCounter) = CStr(variantValues(rowCounter, columnCounter)) Next columnCounter Next rowCounter ' Return the ssortingng array RangeToSsortingngArray = ssortingngValues End Function Next columnCounter Public Function RangeToSsortingngArray(theRange As Excel.Range) As Ssortingng() ' Get values into a variant array Dim variantValues As Variant variantValues = theRange.Value ' Set up a ssortingng array for them Dim ssortingngValues() As Ssortingng ReDim ssortingngValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2)) ' Put them in there! Dim columnCounter As Long, rowCounter As Long For rowCounter = UBound(variantValues, 1) To 1 Step -1 For columnCounter = UBound(variantValues, 2) To 1 Step -1 ssortingngValues(rowCounter, columnCounter) = CStr(variantValues(rowCounter, columnCounter)) Next columnCounter Next rowCounter ' Return the ssortingng array RangeToSsortingngArray = ssortingngValues End Function Next rowCounter Public Function RangeToSsortingngArray(theRange As Excel.Range) As Ssortingng() ' Get values into a variant array Dim variantValues As Variant variantValues = theRange.Value ' Set up a ssortingng array for them Dim ssortingngValues() As Ssortingng ReDim ssortingngValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2)) ' Put them in there! Dim columnCounter As Long, rowCounter As Long For rowCounter = UBound(variantValues, 1) To 1 Step -1 For columnCounter = UBound(variantValues, 2) To 1 Step -1 ssortingngValues(rowCounter, columnCounter) = CStr(variantValues(rowCounter, columnCounter)) Next columnCounter Next rowCounter ' Return the ssortingng array RangeToSsortingngArray = ssortingngValues End Function 'Retourner la string Public Function RangeToSsortingngArray(theRange As Excel.Range) As Ssortingng() ' Get values into a variant array Dim variantValues As Variant variantValues = theRange.Value ' Set up a ssortingng array for them Dim ssortingngValues() As Ssortingng ReDim ssortingngValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2)) ' Put them in there! Dim columnCounter As Long, rowCounter As Long For rowCounter = UBound(variantValues, 1) To 1 Step -1 For columnCounter = UBound(variantValues, 2) To 1 Step -1 ssortingngValues(rowCounter, columnCounter) = CStr(variantValues(rowCounter, columnCounter)) Next columnCounter Next rowCounter ' Return the ssortingng array RangeToSsortingngArray = ssortingngValues End Function RangeToSsortingngArray = ssortingngValues Public Function RangeToSsortingngArray(theRange As Excel.Range) As Ssortingng() ' Get values into a variant array Dim variantValues As Variant variantValues = theRange.Value ' Set up a ssortingng array for them Dim ssortingngValues() As Ssortingng ReDim ssortingngValues(1 To UBound(variantValues, 1), 1 To UBound(variantValues, 2)) ' Put them in there! Dim columnCounter As Long, rowCounter As Long For rowCounter = UBound(variantValues, 1) To 1 Step -1 For columnCounter = UBound(variantValues, 2) To 1 Step -1 ssortingngValues(rowCounter, columnCounter) = CStr(variantValues(rowCounter, columnCounter)) Next columnCounter Next rowCounter ' Return the ssortingng array RangeToSsortingngArray = ssortingngValues End Function 
 Function RangeToSsortingngArray(myRange as range) as Ssortingng() ReDim strArray(myRange.Cells.Count - 1) As Ssortingng Dim idx As Long Dim c As Range For Each c In myRange Set strArray(idx) = c.Text idx = idx + 1 Next c RangeToSsortingngArray = strArray End sub Fonction RangeToSsortingngArray (myRange as range) en tant que Ssortingng () Function RangeToSsortingngArray(myRange as range) as Ssortingng() ReDim strArray(myRange.Cells.Count - 1) As Ssortingng Dim idx As Long Dim c As Range For Each c In myRange Set strArray(idx) = c.Text idx = idx + 1 Next c RangeToSsortingngArray = strArray End sub ReDim strArray (myRange.Cells.Count - 1) As Ssortingng Function RangeToSsortingngArray(myRange as range) as Ssortingng() ReDim strArray(myRange.Cells.Count - 1) As Ssortingng Dim idx As Long Dim c As Range For Each c In myRange Set strArray(idx) = c.Text idx = idx + 1 Next c RangeToSsortingngArray = strArray End sub Dim idx As Long Function RangeToSsortingngArray(myRange as range) as Ssortingng() ReDim strArray(myRange.Cells.Count - 1) As Ssortingng Dim idx As Long Dim c As Range For Each c In myRange Set strArray(idx) = c.Text idx = idx + 1 Next c RangeToSsortingngArray = strArray End sub Dim c As Range Function RangeToSsortingngArray(myRange as range) as Ssortingng() ReDim strArray(myRange.Cells.Count - 1) As Ssortingng Dim idx As Long Dim c As Range For Each c In myRange Set strArray(idx) = c.Text idx = idx + 1 Next c RangeToSsortingngArray = strArray End sub Pour chaque c Dans myRange Function RangeToSsortingngArray(myRange as range) as Ssortingng() ReDim strArray(myRange.Cells.Count - 1) As Ssortingng Dim idx As Long Dim c As Range For Each c In myRange Set strArray(idx) = c.Text idx = idx + 1 Next c RangeToSsortingngArray = strArray End sub Définir strArray (idx) = c.Texte Function RangeToSsortingngArray(myRange as range) as Ssortingng() ReDim strArray(myRange.Cells.Count - 1) As Ssortingng Dim idx As Long Dim c As Range For Each c In myRange Set strArray(idx) = c.Text idx = idx + 1 Next c RangeToSsortingngArray = strArray End sub idx = idx + 1 Function RangeToSsortingngArray(myRange as range) as Ssortingng() ReDim strArray(myRange.Cells.Count - 1) As Ssortingng Dim idx As Long Dim c As Range For Each c In myRange Set strArray(idx) = c.Text idx = idx + 1 Next c RangeToSsortingngArray = strArray End sub Suivant c Function RangeToSsortingngArray(myRange as range) as Ssortingng() ReDim strArray(myRange.Cells.Count - 1) As Ssortingng Dim idx As Long Dim c As Range For Each c In myRange Set strArray(idx) = c.Text idx = idx + 1 Next c RangeToSsortingngArray = strArray End sub RangeToSsortingngArray = strArray Function RangeToSsortingngArray(myRange as range) as Ssortingng() ReDim strArray(myRange.Cells.Count - 1) As Ssortingng Dim idx As Long Dim c As Range For Each c In myRange Set strArray(idx) = c.Text idx = idx + 1 Next c RangeToSsortingngArray = strArray End sub

Vous pouvez effectivement passer directement d'une gamme à un tableau en utilisant les fonctions Split, Join et un délimiteur non dans le text.

En supposant que vous avez déjà atsortingbué une gamme de valeurs 1D comme SrcRange 

 Dim Array() As Ssortingng: Array = Split(Join(Application.Transpose(SrcRange), "#"), "#")

Si cela ne vous dérange pas de modifier le contenu du presse-papiers, procédez comme suit:

  1. COPIEz la scope dans le presse-papier avec la méthode Copier: 

     MyTargetRange.Copy

  2. Copiez le contenu du presse-papiers vers une variable de string (searchz ce site ou ailleurs pour les fonctions de transfert de strings vers / depuis le presse-papiers).

  3. SPLIT la string dans un set de variantes: 

     MySsortingngArray = Split(MyClipboardText, vbCrLf)

  4. OPTIONNEL: Le tableau aura un élément vierge supplémentaire car il y a toujours un renvoi supplémentaire (vbCrLf) à la fin du text que vous venez de copyr dans le presse-papiers. Pour supprimer simplement resize le tableau: 

     Redim Preserve MySsortingngArray(Ubound(MySsortingngArray) - 1)

Très simple et rapide !!!

Les inconvénients sont que le presse-papiers peut changer lorsque vous vous attendez le less (pendant un nouveau calcul) et qu'il ne produit que des masortingces de strings (pas des doubles ou d'autres types de valeur numérique).

Cela serait EXTRÊMEMENT UTILE si vous travaillez avec beaucoup de fonctions répétitives (milliers) qui utilisent les mêmes données (des milliers de points de données). La première fois que votre fonction est appelée, effectuez tous les calculs intermédiaires sur les plages de données dont vous avez besoin, mais enregistrez les résultats dans des variables statiques. Égalisez également une copy de string de vos plages d'input via le presse-papiers. Avec chaque appel ultérieur à votre fonction, convertissez les plages d'input en text, à nouveau via le Presse-papiers et comparez-en avec la copy enregistrée. Si elles sont identiques, vous pouvez contourner les calculs préliminaires.

Les gammes nommées utilisées dans VBA sont déjà des arrays. Donc, faites d'abord la gamme dans une gamme nommée, puis renvoyez-la et supprimez la gamme nommée. Par exemple: 

 ThisWorkbook.Names.Add Name:="test_array", RefersTo:=Sheet1.Range("A4:B10") a = Sheet1.Range("test_array") ThisWorkbook.Names("test_array").Delete ThisWorkbook.Names.Add Name: = "test_array", RefersTo: = Sheet1.Range ("A4: B10") ThisWorkbook.Names.Add Name:="test_array", RefersTo:=Sheet1.Range("A4:B10") a = Sheet1.Range("test_array") ThisWorkbook.Names("test_array").Delete a = Sheet1.Range ("test_array") ThisWorkbook.Names.Add Name:="test_array", RefersTo:=Sheet1.Range("A4:B10") a = Sheet1.Range("test_array") ThisWorkbook.Names("test_array").Delete